Pandas DataFrame: to_sql() function

Last update on August 19 2022 21:50:51 (UTC/GMT +8 hours)

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