polars.DataFrame.write_database#

DataFrame.write_database(
table_name: str,
connection: ConnectionOrCursor | str,
*,
if_table_exists: DbWriteMode = 'fail',
engine: DbWriteEngine | None = None,
engine_options: dict[str, Any] | None = None,
) → int[source]#

Write the data in a Polars DataFrame to a database.

New in version 0.20.26: Support for instantiated connection objects in addition to URI strings, and a new engine_options parameter.

Parameters:
table_name

Schema-qualified name of the table to create or append to in the target SQL database. If your table name contains special characters, it should be quoted.

connection

An existing SQLAlchemy or ADBC connection against the target database, or a URI string that will be used to instantiate such a connection, such as:

  • “postgresql://user:pass@server:port/database”

  • “sqlite:////path/to/database.db”

if_table_exists{‘append’, ‘replace’, ‘fail’}

The insert mode:

  • ‘replace’ will create a new database table, overwriting an existing one.

  • ‘append’ will append to an existing table.

  • ‘fail’ will fail if table already exists.

engine{‘sqlalchemy’, ‘adbc’}

Select the engine to use for writing frame data; only necessary when supplying a URI string (defaults to ‘sqlalchemy’ if unset)

engine_options

Additional options to pass to the engine’s associated insert method:

  • “sqlalchemy” - currently inserts using Pandas’ to_sql method, though this will eventually be phased out in favour of a native solution.

  • “adbc” - inserts using the ADBC cursor’s adbc_ingest method.

Returns:
int

The number of rows affected, if the driver provides this information. Otherwise, returns -1.

Examples

Insert into a temporary table using a PostgreSQL URI and the ADBC engine:

>>> df.write_database(
...     table_name="target_table",
...     connection="postgresql://user:pass@server:port/database",
...     engine="adbc",
...     engine_options={"temporary": True},
... )

Insert into a table using a pyodbc SQLAlchemy connection to SQL Server that was instantiated with “fast_executemany=True” to improve performance:

>>> pyodbc_uri = (
...     "mssql+pyodbc://user:pass@server:1433/test?"
...     "driver=ODBC+Driver+18+for+SQL+Server"
... )
>>> engine = create_engine(pyodbc_uri, fast_executemany=True)  
>>> df.write_database(
...     table_name="target_table",
...     connection=engine,
... )