to_sql() alternative to fast_executemany for databases other than SQL Server

user_15269052 0
Viewed 1242

I'm trying to speed-up the way to write my DataFrame in a SQLite database. To do so, I'm trying to use Method 4 on this website. I also found a more elegant way to change the variable cursor.fast_executemany = True thanks to a post on Stack Overflow.

The problem is that this error occurs: TypeError: Invalid argument(s) 'fast_executemany' sent to create_engine(), using configuration SQLiteDialect_pysqlite/NullPool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

I'm using sqlalchemy version 1.3.23 and pandas version 1.14.0.

import pandas as pd
from sqlalchemy import create_engine # database connection
from sqlalchemy import event
from IPython.display import display

disk_engine = create_engine('sqlite:///2021_database.db',fast_executemany=True)
data.to_sql('data', disk_engine, if_exists='replace')

Here, data is my DataFrame. When I run this code without fast_executemany=True, it works.

Someone has any idea?

pandas sqlalchemy sqlite python
1 Answers

fast_executemany=True is specific to the mssql+pyodbc:// dialect. It will not work with other dialects like sqlite://. For other databases you would normally use method="multi" (or a custom function for PostgreSQL as described in this answer).

However, SQLite appears to have a limit of 999 parameter values in a single SQL statement so you would also need to use the chunksize= argument:

# value to pass to index= argument
use_index = False

# calculate chunksize= , taking into account whether we'll be
# uploading the index
chunk_size = 999 // (len(df.columns) + (1 if use_index else 0))

df.to_sql(
    "table1",
    engine,
    index=use_index,
    if_exists="replace",
    method="multi",
    chunksize=chunk_size,
)

Edit:

Based on comments below, I tested df.to_sql() inserting 1_000_000 rows into a SQLite database on my local hard drive.

method=None (the default): 1_000_000 rows written in 53 seconds

method="multi" (as above): 1_000_000 rows written in 505 seconds

Apparently Python's sqlite3 module has some sort of optimization similar to fast_executemany=True already built-in, so an .executemany() with 1_000_000 rows (pd.to_sql(…, method=None)) is about an order of magnitude faster than ~5 thousand .execute() calls inserting 199 rows each (pd.to_sql(…, method="multi")).

user_21443900
Related