Update multiple tables at once with pandas to_sql

Method

Transactions in pandas can be implemented using SQLAlchemy's ʻengine.begin ()` as follows:

By doing this, even if it takes time to update each table, the update can be reflected at the same time without any time difference, and if it fails, you can roll back. By using with, the commit process is automatically called. (Rollback on failure)

from sqlalchemy import create_engine
engine = create_engine(url)

with engine.begin() as conn:
    df1.to_sql('table1', conn, if_exists='append')
    df2.to_sql('table2', conn, if_exists='append')

important point

If you run a query that causes a commit to run inside the with block, it will be committed there. Reference: Statements That Cause an Implicit Commit

For example, if you set the option ʻif_exists of to_sql` to'replace', DROP TABLE will run and updates will occur for each table.

with engine.begin() as conn:
    df1.to_sql('table1', conn, if_exists='replace')
    df2.to_sql('table2', conn, if_exists='replace')  #DROP TABLE is called and the update of table1 is committed.

If you want to update all the contents of the table, it seems better to create a table with another name once and replace it with RENAME TABLE.

from sqlalchemy import create_engine
engine = create_engine(url)

df1.to_sql('table1_new', engine, if_exists='replace')
df2.to_sql('table2_new', engine, if_exists='replace') 

engine.execute('''
RENAME TABLE table1 to table1_old, 
             table1_new to table1
             table2 to table2_old,
             table2_tmp to table2;
'''
)