ZetCode

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.

sql_basic.py
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.

sql_table.py
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.

sql_exists.py
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.

sql_params.py
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.

sql_alchemy.py
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

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Pandas tutorials.