Python Pandas to_sql() Method

Theto_sql() method in Python's Pandas library provides a convenient way to write data stored in a PandasDataFrame orSeries object to a SQL database. It supports creating new tables, appending to existing ones, or overwriting existing data. This functionality works with databases supported bySQLAlchemy.

Syntax

Following is the syntax of the Python Pandas to_sql() method −

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

When using theto_sql() method on a Series object, you should call it asSeries.to_sql().

Parameters

The Python Pandas to_sql() method accepts the below parameters −

• name: A string representing the name of the SQL table.

• con: Database connection object. Can be an instance ofsqlalchemy.engine.(Engine or Connection) orsqlite3.Connection.

• schema: Specifies the schema to use. Defaults to the database's default schema ifNone.

• if_exists: This parameter determines the action if the table already exists. Available options arefail (raises a ValueError),replace (drops the table before inserting new values), andappend (appends new values to the existing table).

• index: Determines whether to write the DataFrame index as a column. IfTrue, the index is written. IfFalse, it is not included.

• index_label: Column labels for the index. Defaults to index names ifNone.

• chunksize: Specifies the number of rows to write at a time. Writes all rows at once ifNone.

• dtype: Specifies the data type for columns. You can useSQLAlchemy types or strings forSQLite.

• method: Determines the SQL insertion method. It supportsNone,multi, andcallable.

Return Value

The Pandasto_sql() method returnsNone or an integer indicating the number of rows affected. If the callable passed into method doesn't return a row count,None is returned.

Example: Creating SQLite Database from Pandas DataFrame

Here is a basic example demonstrating creating SQLite Database from Pandas DataFrame using the Pandasto_sql() method. We will then read the created SQL database using theread_sql() method.

import pandas as pdfrom sqlite3 import connect# Create a database connectionconn = connect(':memory:')# Create a sample DataFramedf = pd.DataFrame({"Col_1": list("abc"),"Col_2": pd.date_range("20250101", periods=3)})# Save the DataFrame in a SQL table with 'test_data' namedf.to_sql(name='test_data', con=conn)# Read SQL table into a DataFrameresult = pd.read_sql('SELECT * FROM test_data', conn)# Display the retrieved dataprint("DataFrame from SQL Table:")print(result.head())

Following is an output of the above code −

DataFrame from SQL Table:

Example: Creating an In-Memory SQLite Database

Here is another example of creating an in-memory SQLite database using the Pandasto_sql() method.

from sqlalchemy import create_engineimport pandas as pd# Create an SQLite enginedf = pd.DataFrame({'name': ['Ravi', 'Priya', 'Kiran'], 'salary': [50000, 60000, 65000]})engine = create_engine('sqlite://', echo=False)# Write the DataFrame to a new SQL tabledf.to_sql(name='employees', con=engine)# Fetch the written data from the SQL table# Query the tablefrom sqlalchemy import textprint('Fetched Data from SQL table:')with engine.connect() as conn:    print(conn.execute(text("SELECT * FROM employees")).fetchall())

When we run above program, it produces following result −

Fetched Data from SQL table:[(0, 'Ravi', 50000), (1, 'Priya', 60000), (2, 'Kiran', 65000)]

Example: Appending to an Existing SQL Table

This example demonstrates appending values to an existing table using theif_exists parameter of theDataFrame.to_sql() method.

from sqlalchemy import create_engineimport pandas as pd# Create an SQLite enginedf = pd.DataFrame({'name': ['Ravi', 'Priya', 'Kiran'], 'salary': [50000, 60000, 65000]})engine = create_engine('sqlite://', echo=False)# Write the DataFrame to a new SQL tabledf.to_sql(name='employees', con=engine)# Appending values to an Existing Tablewith engine.begin() as connection:    new_data = pd.DataFrame({'name': ['Sahitya'], 'salary':[55000]})    new_data.to_sql(name='employees', con=connection, if_exists='append') # Query the table to fetch the data from the SQL tablefrom sqlalchemy import textprint('Fetched Data from SQL table:')with engine.connect() as conn:    print(conn.execute(text("SELECT * FROM employees")).fetchall())

While executing the above code we obtain the following output −

Fetched Data from SQL table:[(0, 'Ravi', 50000), (1, 'Priya', 60000), (2, 'Kiran', 65000), (0, 'Sahitya', 55000)]

Example: Overwriting SQL Table Using the to_sql() Method

The following example demonstrates using theto_sql() method for over writing an existing SQL table by settingif_exists='replace' parameter.

from sqlalchemy import create_engineimport pandas as pd# Create an SQLite enginedf = pd.DataFrame({'name': ['Ravi', 'Priya', 'Kiran'], 'salary': [50000, 60000, 65000]})engine = create_engine('sqlite://', echo=False)# Write the DataFrame to a new SQL tabledf.to_sql(name='employees', con=engine)# Overwriting SQL Tablereplacement_data = pd.DataFrame({'name': ['Anil', 'Nitya'], 'salary': [10000, 40000]})replacement_data.to_sql(name='employees', con=engine, if_exists='replace', index_label='id')# Query the table to fetch the data from the SQL tablefrom sqlalchemy import textprint('Fetched Data from SQL table:')with engine.connect() as conn:    print(conn.execute(text("SELECT * FROM employees")).fetchall())

Following is an output of the above code −

Fetched Data from SQL table:[(0, 'Anil', 10000), (1, 'Nitya', 40000)]