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

Write the data in a Polars DataFrame to a database.

Added 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 insert method associated with the engine specified by the option engine.

• Setting engine to “sqlalchemy” currently inserts using Pandas’ to_sql method (though this will eventually be phased out in favor of a native solution).
• Setting engine to “adbc” inserts using the ADBC cursor’s adbc_ingest method. Note that when passing an instantiated connection object, PyArrow is required for SQLite and Snowflake drivers.

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,
... )