Loop to streamline pandas dataframe to_sql
As far as I can see the only thing changing is the URL to the DB file.

You can just define a dictionary for this:

DB_URL = {"df1": "sqlite:///abc_Fares.db",
          "df2": "sqlite:///defg_Fares.db",
          ...}


I would also define the titles of the dataframes as a list:

titles = ["Truck Services", ...]


Which you can the easily use:

engine = create_engine(DB_URL[df_id])
connection = engine.connect()
for df, title in zip(dataframe_source, titles):
    df.to_sql(title, engine, if_exists="append")
connection.close()


Note that this calls to_sql directly on the dataframes, so no need for pandas.DataFrame(df, ...).

Also note that zip will stop after the shorter iterable is exhausted. So if the list of titles only contains four titles, the fifth dataframe will not be written to the DB.

Final code:

DB_URL = {"df1": "sqlite:///abc_Fares.db",
          "df2": "sqlite:///defg_Fares.db",
          ...}

def _format_data_frame(dataframe_source, df_id):
    """Format the dataframe source that is retrieved from 'my_function()' and put into SQL"""
    column_rename = {"Fare Per Ride (cent)": "Card",
                     "Unnamed: 2": "Cash",
                     "Card Fare Per Ride (cent)": "Card",
                     "Card Fare (cent)": "Card",
                     "Description": "Distance"}

    titles = ["Truck Services", "Feeder Services", "Express Services", "Other Services"]
    engine = create_engine(DB_URL[df_id])
    connection = engine.connect()
    # Since zip stops after the shorter iterable is exhausted, this
    # leaves the fifth df out
    for df, title in zip(dataframe_source, titles):
        if "Unnamed: 2" in df.columns:
            df.drop("Unnamed: 2", axis=1, inplace=True)
        df.rename(columns=column_rename, inplace=True)
        df.to_sql(title, engine, if_exists="append")
    connection.close()


Note that I also made the if "xxx" in each_dataframe.columns faster by storing the columns once per dataframe in a set, for which membership testing is \$\mathcal{O}(1)\$.

I also made the column renaming a lot easier. The dataframe will ignore all keys in the translation dictionary for which no columns exist, so we can use one common dictionary.

You should also include your imports, right now it is not clear where create_engine comes from. You should have a look if they implemented contextmanagers, so you could do:

with engine.connect() as connection:
    for df, title in zip(dataframe_source, titles):
            df.to_sql(title, engine, if_exists="append")


Where the connection.close() is done automatically.