Skip to main content
`Ctrl`+`K`

# pandas.DataFrame.to_sql

DataFrame.to_sql(name, con, *, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

  Write records stored in a DataFrame to a SQL database.

  Databases supported by SQLAlchemy [1] are supported. Tables can be newly created, appended to, or overwritten.

  Warning

  The pandas library does not attempt to sanitize inputs provided via a to_sql call. Please refer to the documentation for the underlying database driver to see if it will properly prevent injection, or alternatively be advised of a security risk when executing arbitrary commands in a to_sql call.

  Parameters:
    name str

      Name of SQL table.

    con ADBC connection, sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection

      ADBC provides high performance I/O with native type support, where available. Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable. See here. If passing a sqlalchemy.engine.Connection which is already in a transaction, the transaction will not be committed. If passing a sqlite3.Connection, it will not be possible to roll back the record insertion.

    schema str, optional

      Specify the schema (if database flavor supports this). If None, use default schema.

    if_exists{‘fail’, ‘replace’, ‘append’, ‘delete_rows’}, default ‘fail’

      How to behave if the table already exists.

      - fail: Raise a ValueError.

      - replace: Drop the table before inserting new values.

      - append: Insert new values to the existing table.

      - delete_rows: If a table exists, delete all records and insert data.

    index bool, default True

      Write DataFrame index as a column. Uses index_label as the column name in the table. Creates a table index for this column.

    index_label str or sequence, default None

      Column label for index column(s). If None is given (default) and index is True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.

    chunksize int, optional

      Specify the number of rows in each batch to be written to the database connection at a time. By default, all rows will be written at once. Also see the method keyword.

    dtype dict or scalar, optional

      Specifying the datatype for columns. If a dictionary is used, the keys should be the column names and the values should be the SQLAlchemy types or strings for the sqlite3 legacy mode. If a scalar is provided, it will be applied to all columns.

    method{None, ‘multi’, callable}, optional

      Controls the SQL insertion clause used:

      - None : Uses standard SQL `INSERT` clause (one per row).

      - ‘multi’: Pass multiple values in a single `INSERT` clause.

      - callable with signature `(pd_table, conn, keys, data_iter)`.

      Details and a sample callable implementation can be found in the section insert method.

  Returns:
    None or int

      Number of rows affected by to_sql. None is returned if the callable passed into `method` does not return an integer number of rows.

      The number of returned rows affected is the sum of the `rowcount` attribute of `sqlite3.Cursor` or SQLAlchemy connectable which may not reflect the exact number of written rows as stipulated in the sqlite3 or SQLAlchemy.

  Raises:
    ValueError

      When the table already exists and if_exists is ‘fail’ (the default).

  See also

  `read_sql`

    Read a DataFrame from a table.

  Notes

  Timezone aware datetime columns will be written as `Timestamp with timezone` type with SQLAlchemy if supported by the database. Otherwise, the datetimes will be stored as timezone unaware timestamps local to the original timezone.

  Not all datastores support `method="multi"`. Oracle, for example, does not support multi-value insert.

  References

  [1]

  https://docs.sqlalchemy.org

  [2]

  https://www.python.org/dev/peps/pep-0249/

  Examples

  Create an in-memory SQLite database.

      >>> from sqlalchemy import create_engine
      >>> engine = create_engine('sqlite://', echo=False)

  Create a table from scratch with 3 rows.

      >>> df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
      >>> df
           name
      0  User 1
      1  User 2
      2  User 3

      >>> df.to_sql(name='users', con=engine)
      3
      >>> from sqlalchemy import text
      >>> with engine.connect() as conn:
      ...     conn.execute(text("SELECT * FROM users")).fetchall()
      [(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]

  An sqlalchemy.engine.Connection can also be passed to con:

      >>> with engine.begin() as connection:
      ...     df1 = pd.DataFrame({'name' : ['User 4', 'User 5']})
      ...     df1.to_sql(name='users', con=connection, if_exists='append')
      2

  This is allowed to support operations that require that the same DBAPI connection is used for the entire operation.

      >>> df2 = pd.DataFrame({'name' : ['User 6', 'User 7']})
      >>> df2.to_sql(name='users', con=engine, if_exists='append')
      2
      >>> with engine.connect() as conn:
      ...     conn.execute(text("SELECT * FROM users")).fetchall()
      [(0, 'User 1'), (1, 'User 2'), (2, 'User 3'),
       (0, 'User 4'), (1, 'User 5'), (0, 'User 6'),
       (1, 'User 7')]

  Overwrite the table with just `df2`.

      >>> df2.to_sql(name='users', con=engine, if_exists='replace',
      ...            index_label='id')
      2
      >>> with engine.connect() as conn:
      ...     conn.execute(text("SELECT * FROM users")).fetchall()
      [(0, 'User 6'), (1, 'User 7')]

  Delete all rows before inserting new records with `df3`

      >>> df3 = pd.DataFrame({"name": ['User 8', 'User 9']})
      >>> df3.to_sql(name='users', con=engine, if_exists='delete_rows',
      ...            index_label='id')
      2
      >>> with engine.connect() as conn:
      ...     conn.execute(text("SELECT * FROM users")).fetchall()
      [(0, 'User 8'), (1, 'User 9')]

  Use `method` to define a callable insertion method to do nothing if there’s a primary key conflict on a table in a PostgreSQL database.

      >>> from sqlalchemy.dialects.postgresql import insert
      >>> def insert_on_conflict_nothing(table, conn, keys, data_iter):
      ...     # "a" is the primary key in "conflict_table"
      ...     data = [dict(zip(keys, row)) for row in data_iter]
      ...     stmt = insert(table.table).values(data).on_conflict_do_nothing(index_elements=["a"])
      ...     result = conn.execute(stmt)
      ...     return result.rowcount
      >>> df_conflict.to_sql(name="conflict_table", con=conn, if_exists="append",  # noqa: F821
      ...                    method=insert_on_conflict_nothing)
      0

  For MySQL, a callable to update columns `b` and `c` if there’s a conflict on a primary key.

      >>> from sqlalchemy.dialects.mysql import insert   # noqa: F811
      >>> def insert_on_conflict_update(table, conn, keys, data_iter):
      ...     # update columns "b" and "c" on primary key conflict
      ...     data = [dict(zip(keys, row)) for row in data_iter]
      ...     stmt = (
      ...         insert(table.table)
      ...         .values(data)
      ...     )
      ...     stmt = stmt.on_duplicate_key_update(b=stmt.inserted.b, c=stmt.inserted.c)
      ...     result = conn.execute(stmt)
      ...     return result.rowcount
      >>> df_conflict.to_sql(name="conflict_table", con=conn, if_exists="append",  # noqa: F821
      ...                    method=insert_on_conflict_update)
      2

  Specify the dtype (especially useful for integers with missing values). Notice that while pandas is forced to store the data as floating point, the database supports nullable integers. When fetching the data with Python, we get back integer scalars.

      >>> df = pd.DataFrame({"A": [1, None, 2]})
      >>> df
           A
      0  1.0
      1  NaN
      2  2.0

      >>> from sqlalchemy.types import Integer
      >>> df.to_sql(name='integers', con=engine, index=False,
      ...           dtype={"A": Integer()})
      3

      >>> with engine.connect() as conn:
      ...     conn.execute(text("SELECT * FROM integers")).fetchall()
      [(1,), (None,), (2,)]

  Added in version 2.2.0: pandas now supports writing via ADBC drivers

      >>> df = pd.DataFrame({'name' : ['User 10', 'User 11', 'User 12']})
      >>> df
            name
      0  User 10
      1  User 11
      2  User 12

      >>> from adbc_driver_sqlite import dbapi
      >>> with dbapi.connect("sqlite://") as conn:
      ...     df.to_sql(name="users", con=conn)
      3