using sqlalchemy to_sql when dataframe has different column names to sql server table column names

Question

I have a dataframe that I upload to a SQL server table. I am using sqlalchemy & the to_sql method.

The data uploads into the table perfectly. Currently I have designed it so that my column names in my dataframe and sql table are the same. However I was wondering if this needs to be the case? Is there a way that when your dataframe has a different column name to the sql table that you can specify some mapping? Or do you just simply rename the column name in your dataframe?

from sqlalchemy import create_engine
engine = create_engine(engine_str)
conn = engine.connect()
df.to_sql(tbl_name, conn, if_exists='append', index=False)

Answer 1

I've had this situation when transferring data between tables, I've used pandas.DataFrame.rename to map one set of columns to another before pushing the dataframe back to SQL.

So, for example let's say that one table has the columns: Name, IPAddress, Folder

And your second table has the columns: name, ip, folder

You could read the first table with sqlalchemy into a dataframe:

source_data = pd.read_sql_table(source_table, con=engine)

Then create a conversion dictionary to convert the columns:

conv_dict = {
'Name': 'name',
'IPAddress': 'ip',
'Folder': 'folder'
}

# convert the columns into a new datframe
new_df = source_data.rename(columns=conv_dict)

Now you can put that new dataframe with the converted columns into your second table:

new_df.to_sql(dest_table, con=engine, if_exists='append', index=False)

Source: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename.html