write_database() - Insert many rows with sql server using fast_executemany

[gregory-kral-shell]

Description

I would like to be able to insert many rows quickly in an sql server database table with df.write_database()

I can currently achieve this with the fast_executemany option in pandas but I can’t find a way to do this with polars.write_database() which only accepts a connection string and not an sql alchemy engine. The pandas code is much faster than the polars equivalent without fast_executemany.

Example code with pandas:
engine = create_engine(
"mssql+pyodbc://{user}:{password}@{server}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server",
fast_executemany=True,
)
ret = curve.to_pandas().to_sql(
name="curve",
con=engine,
if_exists="append",
index=False,
)

Example code with polars:
curve.write_database(
table_name="curve",
connection="mssql+pyodbc://{user}:{password}@{server}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server",
if_table_exists="append",
engine="sqlalchemy",
)

[alexander-beedie]

Yup; allowing write_database to take a connection object is on my near-term "todo" list... I'll try to get it done this week. (Longer-term I have a total rewrite of this entire set of functionality in mind so we can drop the internal adbc/pandas redirects completely 😅)

[alexander-beedie]

Done; in the upcoming 0.20.26 you'll be able to pass instantiated connections to write_database, so you can use the same approach (create one with fast_executemany) with Polars too 👌

[gregory-kral-shell]

Great thanks for that!!!