Reading & Writing SQL (read_sql, to_sql)

Reviewed & published by Brayan K

pandas can talk directly to databases — read_sql pulls the result of a query into a DataFrame, and to_sql pushes a DataFrame back out as a database table.

Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll connect to a SQLite database, run queries with pd.read_sql, write with df.to_sql(if_exists=), and stream large results with chunksize.

What You'll Learn in This Lesson

• Open a sqlite3 connection
• Write a DataFrame with df.to_sql()
• Control rewrites with if_exists=
• Query into a DataFrame with read_sql
• Push filtering/joins down into SQL
• Stream big results with chunksize

1 Connect, Write, and Read Back

Every database operation needs a connection. Python ships with sqlite3, and sqlite3.connect(":memory:") spins up a throwaway in-memory database — perfect for learning. You write a DataFrame with df.to_sql("table", conn) and read it back with pd.read_sql("SELECT ...", conn).

import pandas as pd, sqlite3

conn = sqlite3.connect(":memory:")          # in-memory DB
df.to_sql("people", conn, index=False)      # write the table
pd.read_sql("SELECT * FROM people", conn)   # read it back

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")

df = pd.DataFrame({
    "name": ["Alice", "Bob", "Carol"],
    "city": ["NYC", "LA", "NYC"],
    "age": [25, 30, 35],
})

# Write the DataFrame to a table called "people"
df.to_sql("people", conn, index=False, if_exists="replace")

# Read the whole table back
print(pd.read_sql("SELECT * FROM people", conn))

conn.close()

# ✅ Expected output:
#     name city  age
# 0  Alice  NYC   25
# 1    Bob   LA   30
# 2  Carol  NYC   35

2 Query in SQL and Append with if_exists

A big advantage of read_sql is that you can do the filtering, joining, and aggregation in SQL and only bring back the rows you need — far cheaper than loading everything and filtering in pandas. On the write side, if_exists decides what happens when the table already exists.

# Only pull the rows you want
pd.read_sql_query("SELECT name, age FROM people WHERE city = 'NYC'", conn)

# if_exists options when writing:
df.to_sql("t", conn, if_exists="fail")     # default: error if exists
df.to_sql("t", conn, if_exists="replace")  # drop + recreate
df.to_sql("t", conn, if_exists="append")   # add rows

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")
df = pd.DataFrame({"name": ["Alice", "Bob"], "score": [10, 20]})

df.to_sql("t", conn, index=False, if_exists="replace")

# Append a second batch onto the same table
more = pd.DataFrame({"name": ["Carol"], "score": [30]})
more.to_sql("t", conn, index=False, if_exists="append")

print(pd.read_sql("SELECT * FROM t", conn))

# Filter inside SQL, not pandas
print(pd.read_sql_query("SELECT name FROM t WHERE score >= 20", conn))

conn.close()

# ✅ Expected output:
#     name  score
# 0  Alice     10
# 1    Bob     20
# 2  Carol     30
#     name
# 0    Bob
# 1  Carol

3 Streaming Large Tables with chunksize

When a result set is too large to hold in memory at once, pass chunksize=n to read_sql. Instead of one giant DataFrame you get an iterator that yields DataFrames of n rows, so you can process the data batch by batch and keep memory flat.

for chunk in pd.read_sql("SELECT * FROM nums", conn, chunksize=4):
    process(chunk)   # each chunk is a small DataFrame

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")
pd.DataFrame({"n": range(1, 11)}).to_sql(
    "nums", conn, index=False, if_exists="replace")

# Process 10 rows in batches of 4
total = 0
for chunk in pd.read_sql("SELECT * FROM nums", conn, chunksize=4):
    print("chunk rows:", len(chunk))
    total += int(chunk["n"].sum())

print("total:", total)

conn.close()

# ✅ Expected output:
# chunk rows: 4
# chunk rows: 4
# chunk rows: 2
# total: 55

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")
products = pd.DataFrame({
    "product": ["Pen", "Notebook", "Marker"],
    "price": [1.5, 4.0, 2.0],
})

# 🎯 YOUR TURN:
# 1) Write this DataFrame to a table named "products" (no index column).
# 2) Read back ONLY the products priced under 3, using SQL.
# Fill in the three blanks.

products.____("products", conn, index=____, if_exists="replace")
print(pd.____("SELECT * FROM products WHERE price < 3", conn))

# Expected output:
#   product  price
# 0     Pen    1.5
# 1  Marker    2.0

conn.close()

! Common Errors (And How to Fix Them)

❌ "Table 'x' already exists"

Writing twice without saying what to do on conflict:

df.to_sql("people", conn)   # ❌ second run -> ValueError, table exists

✅ Fix: choose a behavior:

df.to_sql("people", conn, if_exists="replace")  # ✅ rebuild
# or if_exists="append" to add rows

❌ Treating a chunked read as one DataFrame

df = pd.read_sql("SELECT * FROM t", conn, chunksize=100)
df.head()   # ❌ AttributeError: it's a generator, not a DataFrame

✅ Fix: iterate the chunks:

for chunk in pd.read_sql("SELECT * FROM t", conn, chunksize=100):
    handle(chunk)   # ✅

🎯 Mini Challenge: Join and Aggregate in SQL

Write two related tables, then let SQL do the heavy lifting.

• Write orders and customers to SQLite
• Join them and total each customer's spend in one query

import pandas as pd
import sqlite3

conn = sqlite3.connect(":memory:")

orders = pd.DataFrame({
    "order_id": [1, 2, 3],
    "cust_id":  [10, 20, 10],
    "amount":   [100, 50, 75],
})
customers = pd.DataFrame({"cust_id": [10, 20], "name": ["Alice", "Bob"]})

orders.to_sql("orders", conn, index=False, if_exists="replace")
customers.to_sql("customers", conn, index=False, if_exists="replace")

query = """
SELECT c.name, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON o.cust_id = c.cust_id
GROUP BY c.name
ORDER BY total DESC
"""
print(pd.read_sql_query(query, conn))

conn.close()

# ✅ Expected output:
#     name  total
# 0  Alice    175
# 1    Bob     50

❓ Frequently Asked Questions

Lesson complete — pandas and databases speak the same language!

You can open a connection, write with to_sql(if_exists=), query with read_sql, push filters and joins into SQL, and stream big tables with chunksize.

Practice quiz

Continue this course

• Previous: Cross-Tabulation (crosstab)
• Next: Efficient Formats: Parquet, Feather & Pickle — Store data faster and smaller than CSV with columnar formats
• Quick reference: Pandas cheat sheet

Frequently asked questions

What is the difference between read_sql, read_sql_query, and read_sql_table?

read_sql_query runs a SQL statement and returns the result as a DataFrame. read_sql_table reads an entire named table (it needs SQLAlchemy). read_sql is a convenience dispatcher that figures out which of the two you meant based on whether you passed a query string or a table name, so it is the one most people reach for.

Why does to_sql write an extra unnamed column?

By default to_sql writes the DataFrame index as its own column. If your index is just a 0..n RangeIndex you usually don't want it in the database — pass index=False to drop it.

What does if_exists control?

It decides what to_sql does when the target table already exists: 'fail' (the default) raises an error, 'replace' drops and recreates the table, and 'append' adds the new rows to the existing table. Use 'replace' for a clean rebuild and 'append' to keep accumulating data.

When should I use chunksize?

Use chunksize when a table is too big to fit comfortably in memory. On read_sql it makes pandas return an iterator of smaller DataFrames so you can process the result one batch at a time; on to_sql it inserts rows in batches of that size, which can also be gentler on the database.