sqlalchemy supports multiple rows insert into database.
This example here uses pandas to insert the whole dataset into a MS SQL Server table.
Note if a data frame is too big, it can specify chunksize as well
Note, the default insert method is one row per insert, which is very slow for large data.
use the method 'multi' for multiple rows insertion
from sqlalchemy import create_engine
import pandas as pd
df = pd.DataFrame([['A', 'B', '2022-01-01', 1],['C', 'D', '2022-01-01',2]], columns=['Cat', 'Name', 'Date', 'Value'])
engine = create_engine(
'mssql+pyodbc://user_name:password@sql_server_address/database_name?driver=SQL Server')
df.to_sql(schema='dbo', name='TableName', con=engine, if_exists='append', index=None, chunksize=100, method='multi')
The sqlalchemy is actually running a massive insert statement:
INSERT INTO table (col1, col2, ... colk) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), ....
when there are too many rows, it tends to fail, maybe the string is too long.
Limit the chuncksize to a small enough value, e.g. 200
THe native SQLALchemy uses bulk_insert_objects for inserting but requires managing your own session, table definition, etc.