pandas.to_sql with new columns to existing table, add automatically new columns?

Question

I want to write a dataframe to an existing sqlite (or mysql) table and sometimes the dataframe will contain a new column that is not yet present in the database. What do I need to do to avoid this throwing an error? Is there a way to tell pandas or sqlalchemy to automatically expand the database table with potential new columns?

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table match_exact_both has no column named ....

Answer 1

Here is my solution using mySQL and sqlalchemy. The basic idea is that if possible I would like to append to the SQL database instead of re-writing the whole thing, but if there is a new column then I can combine the data in Pandas and then overwrite the existing database.

import pymysql
from sqlalchemy import create_engine
import pandas as pd
cnx = create_engine('mysql+pymysql://username:password@hostname/database_name')
try:
    #this will fail if there is a new column
    df.to_sql(name='sql_table', con=cnx, if_exists = 'append', index=False)
except:
    data = pd.read_sql('SELECT * FROM sql_table', cnx)
    df2 = pd.concat([data,df])
    df2.to_sql(name='sql_table', con=cnx, if_exists = 'replace', index=False)