[PYTHON] 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;
'''
)

Recommended Posts

Update multiple tables at once with pandas to_sql
Convert multiple proto files at once with python
Rsync multiple files at once
Create multiple users with serial numbers at once with Ansible Playbook
[Python] Join two tables with pandas
Extract specific multiple columns with pandas
Replace all at once with sed
[Laravel] Aliase to create migration file of multiple tables at once
Convert memo at once with Python 2to3
Create multiple users with serial numbers at once in Ansible Playbook: Part 2
Send newsletters all at once with Gmail
Tips for plotting multiple lines with pandas
Get out of multiple loops at once
Type conversion of multiple columns of pandas DataFrame with astype at the same time
Erase image files at once with one liner
Command to automatically update pip library at once
Upgrade all at once including dependencies with pip
Register multiple self-made styles in Word at once
Limits that can be analyzed at once with MeCab
Generate multiple HTML files at once by pouring JSON data into an HTML template with Python
Acquire multiple Excels at once with glob (Borace machine learning prediction result, confirmation for June)