w3resource

Pandas DataFrame: to_sql() function

DataFrame - to_sql() function

The to_sql() function is used to write records stored in a DataFrame to a SQL database.

Syntax:

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

Parameters:

Name Description Type / Default Value Required / Optional
name  Name of SQL table. string Required
con   Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. sqlalchemy.engine.Engine or sqlite3.Connection Required
schema

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

string Optional
if_exists

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.
{'fail', 'replace', 'append'}
Default Value: 'fail'
Required
index Write DataFrame index as a column. Uses index_label as the column name in the table. bool
Default Value: True
Required
index_label 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. string or sequence
Default Value: None
Required
chunksize Rows will be written in batches of this size at a time. By default, all rows will be written at once. int Optional
dtype Specifying the datatype for columns. The keys should be the column names and the values should be the SQLAlchemy types or strings for the sqlite3 legacy mode. dict Optional
method 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

{None, 'multi', callable}
Default Value: None
Required

Raises: ValueError
When the table already exists and if_exists is 'fail' (the default).

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.

Example:


Download the Pandas DataFrame Notebooks from here.

Previous: DataFrame - to_hdf() function
Next: DataFrame - to_dict() function



Share this Tutorial / Exercise on : Facebook and Twitter