Pandas SQL
last modified November 17, 2026 Python 3.15
SQL (Structured Query Language) is the standard language for asking questions of
a relational database. Pandas bridges the gap between tables in a database and
DataFrames in Python: read_sql loads query results into a
DataFrame, and to_sql writes a DataFrame into a database table.
All examples below create a tiny SQLite database in memory with
sqlite3.connect(':memory:'), so nothing is saved to disk and every
program runs on its own. They were verified with pandas 3.1.1.
Reading with read_sql and read_sql_query
We first create an in-memory SQLite connection, an object that represents the
database, and write a small DataFrame to a table called people.
Then read_sql runs an SQL SELECT statement and returns
its rows as a DataFrame. read_sql_query does the same job but
accepts only a query string, which makes the intent clearer.
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [25, 32]})
df.to_sql('people', conn, if_exists='replace', index=False)
print(pd.read_sql('SELECT * FROM people', conn))
print()
print(pd.read_sql_query('SELECT name FROM people', conn))
conn.close()
The first query selects every column and returns both rows. The second selects
only the name column. In both cases the column names of the result
come from the SQL query, and the DataFrame index is rebuilt starting at zero.
$ python sql_basic.py
name age
0 Alice 25
1 Bob 32
name
0 Alice
1 Bob
Reading a whole table with read_sql_table
When you want every row of a table and already know its name,
read_sql_table saves you from writing a query. It needs an SQLAlchemy
connectable rather than a plain DBAPI connection; SQLAlchemy is a separate
library that provides a common interface to many databases. We create the same
in-memory SQLite database through a SQLAlchemy engine, a factory that opens
connections for us.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [25, 32]})
df.to_sql('people', engine, if_exists='replace', index=False)
print(pd.read_sql_table('people', engine))
Passing just the table name is enough, and the whole table is returned. On
pandas 3.1.1, a bare sqlite3 connection raises
NotImplementedError for this function, so use
read_sql_query as shown above, or an SQLAlchemy engine as here.
$ python sql_table.py
name age
0 Alice 25
1 Bob 32
Writing with to_sql and if_exists
The if_exists argument of to_sql decides what happens
when the table is already there: 'fail' raises an error,
'append' adds the new rows, and 'replace' drops the
old table and creates a new one. Setting index=False keeps the
DataFrame index out of the table, so only real columns are written.
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
df = pd.DataFrame({'name': ['Alice'], 'age': [25]})
df.to_sql('people', conn, if_exists='fail', index=False)
try:
df.to_sql('people', conn, if_exists='fail', index=False)
except ValueError as e:
print('fail raised ValueError:', e)
df.to_sql('people', conn, if_exists='append', index=False)
print('\nafter append:')
print(pd.read_sql('SELECT * FROM people', conn))
df.to_sql('people', conn, if_exists='replace', index=False)
print('\nafter replace:')
print(pd.read_sql('SELECT * FROM people', conn))
conn.close()
The second 'fail' write raises a ValueError because
the table exists, which we catch and print. Appending adds a second copy of
Alice, leaving two rows; replacing starts fresh, leaving one. Without
index=False the table would also contain an extra column named
index.
$ python sql_exists.py
fail raised ValueError: Table 'people' already exists.
after append:
name age
0 Alice 25
1 Alice 25
after replace:
name age
0 Alice 25
Parameterised queries
A parameterised query leaves values out of the SQL string and passes
them separately through params. The database driver then inserts
them safely, which avoids SQL injection, a security problem where untrusted text
is interpreted as part of the query. In SQLite the placeholder is a question
mark.
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
df = pd.DataFrame({'name': ['Alice', 'Bob', 'Cindy'], 'age': [25, 32, 28]})
df.to_sql('people', conn, if_exists='replace', index=False)
query = 'SELECT * FROM people WHERE age > ?'
print(pd.read_sql_query(query, conn, params=(27,)))
conn.close()
The tuple (27,) supplies the value for the single placeholder, so
the database returns only Bob and Cindy. Re-running the same query with a
different value needs no changes to the SQL text.
$ python sql_params.py
name age
0 Bob 32
1 Cindy 28
Using an SQLAlchemy engine
For anything beyond a small local database, the recommended approach is to pass
an SQLAlchemy engine to read_sql and to_sql. The engine
holds the connection details and can be reused for many operations. Here we
use one pointing at an in-memory SQLite database; the same code shape works for
other database systems once their driver and URL are supplied.
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [25, 32]})
df.to_sql('people', engine, if_exists='replace', index=False)
print(pd.read_sql('SELECT * FROM people', engine))
The result is the same DataFrame as with a direct SQLite connection, but the engine path also gives us connection pooling and database-independent code.
$ python sql_alchemy.py
name age
0 Alice 25
1 Bob 32
Source
Pandas read_sql Documentation, Pandas DataFrame.to_sql Documentation, and Pandas read_sql_table Documentation
In this article we have read from and written to an in-memory SQLite database,
compared the three if_exists modes, used parameterised queries and
tried the optional SQLAlchemy engine. For another way of loading data see the
Pandas read CSV tutorial, and for JSON see the
Pandas JSON tutorial.
Author
List all Pandas tutorials.