Pandas read_sql(): SQLite Queries, Parameters and Chunked Imports

Use read_sql() to load database query results into a DataFrame. Select useful records, bind values, and check types before analysis.

Create the sample student database 🔝

import pandas as pd
import sqlite3
import tempfile
from pathlib import Path
from io import StringIO
student_csv = 'id,name,class,mark,gender\n1,John Deo,Four,75,female\n2,Max Ruin,Three,85,male\n3,Arnold,Three,55,male\n4,Krish Star,Four,60,female\n5,John Mike,Four,60,female\n6,Alex John,Four,55,male\n7,My John Rob,Fifth,78,male\n8,Asruid,Five,85,male\n9,Tes Qry,Six,78,male\n10,Big John,Four,55,female\n11,Ronald,Six,89,female\n12,Recky,Six,94,female\n13,Kty,Seven,88,female\n14,Bigy,Seven,88,female\n15,Tade Row,Four,88,male\n16,Gimmy,Four,88,male\n17,Tumyu,Six,54,male\n18,Honny,Five,75,male\n19,Tinny,Nine,18,male\n20,Jackly,Nine,65,female\n21,Babby John,Four,69,female\n22,Reggid,Seven,55,female\n23,Herod,Eight,79,male\n24,Tiddy Now,Seven,78,male\n25,Giff Tow,Seven,88,male\n26,Crelea,Seven,79,male\n27,Big Nose,Three,81,female\n28,Rojj Base,Seven,86,female\n29,Tess Played,Seven,55,male\n30,Reppy Red,Six,79,female\n31,Marry Toeey,Four,88,male\n32,Binn Rott,Seven,90,female\n33,Kenn Rein,Six,96,female\n34,Gain Toe,Seven,69,male\n35,Rows Noump,Six,88,female\n'
students = pd.read_csv(StringIO(student_csv))
def student_connection():
    connection = sqlite3.connect(":memory:")
    students.to_sql("student", connection, index=False, if_exists="replace")
    return connection
print(students.head())
print("Student rows:", len(students))
assert len(students) == 35

Expected output

   id        name  class  mark  gender
0   1    John Deo   Four    75  female
1   2    Max Ruin  Three    85    male
2   3      Arnold  Three    55    male
3   4  Krish Star   Four    60  female
4   5   John Mike   Four    60  female
Student rows: 35

Use an uploaded Excel file in Colab 🔝

To follow the original video, upload student.xlsx through the Files panel of Colab. Session files can disappear when the runtime ends; keep your source elsewhere. These original screenshots illustrate the process. Read it with pd.read_excel("student.xlsx", index_col="id"), then use reset_index() before the index=False database write below. Reading Excel requires a suitable engine such as openpyxl. The embedded CSV provides the same student records for portable practice. See read_excel().

List tables and inspect database records 🔝

connection = student_connection()
try:
    tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name", connection)
    print(tables)
    records = connection.execute("SELECT id, name, mark FROM student ORDER BY id LIMIT 3").fetchall()
    print(records)
    assert tables["name"].tolist() == ["student"]
finally:
    connection.close()

Expected output

      name
0  student
[(1, 'John Deo', 75), (2, 'Max Ruin', 85), (3, 'Arnold', 55)]

Read all student rows with an ID index 🔝

connection = student_connection()
try:
    result = pd.read_sql("SELECT * FROM student ORDER BY id", connection, index_col="id")
    print(result.head())
    print("Rows:", len(result))
    assert result.equals(students.set_index("id"))
finally:
    connection.close()

Expected output

          name  class  mark  gender
id                                 
1     John Deo   Four    75  female
2     Max Ruin  Three    85    male
3       Arnold  Three    55    male
4   Krish Star   Four    60  female
5    John Mike   Four    60  female
Rows: 35

Filter the Five class with a query 🔝

connection = student_connection()
try:
    query = "SELECT * FROM student WHERE class='Five' ORDER BY id"
    result = pd.read_sql(query, connection, index_col="id")
    print(result)
    assert result.index.tolist() == [8, 18]
finally:
    connection.close()

Expected output

      name class  mark gender
id                           
8   Asruid  Five    85   male
18   Honny  Five    75   male

Bind one parameter 🔝

connection = student_connection()
try:
    query = "SELECT * FROM student WHERE class=:my_class ORDER BY id"
    result = pd.read_sql(query, connection, index_col="id", params={"my_class": "Five"})
    print(result)
    assert result.index.tolist() == [8, 18]
    suspicious = "Five' OR 1=1 --"
    empty = pd.read_sql(query, connection, params={"my_class": suspicious})
    assert empty.empty
    print("Literal unmatched input rows:", len(empty))
finally:
    connection.close()

Expected output

      name class  mark gender
id                           
8   Asruid  Five    85   male
18   Honny  Five    75   male
Literal unmatched input rows: 0

Bind multiple parameters 🔝

connection = student_connection()
try:
    query = "SELECT * FROM student WHERE class=:my_class AND mark>=:my_mark ORDER BY id"
    result = pd.read_sql(query, connection, index_col="id", params={"my_class": "Five", "my_mark": 80})
    print(result)
    positional = pd.read_sql("SELECT id FROM student WHERE class=? AND mark>=? ORDER BY id",
        connection, params=("Five", 80))
    assert result.index.tolist() == positional["id"].tolist() == [8]
finally:
    connection.close()

Expected output

      name class  mark gender
id                           
8   Asruid  Five    85   male

Read from a persistent SQLite file 🔝

with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
    database = Path(folder) / "my_data.db"
    connection = sqlite3.connect(database)
    try:
        students.to_sql("student", connection, index=False, if_exists="replace")
    finally:
        connection.close()
    connection = sqlite3.connect(database)
    try:
        result = pd.read_sql("SELECT COUNT(*) AS row_count FROM student", connection)
        print(result)
        assert result.loc[0, "row_count"] == 35
    finally:
        connection.close()

Expected output

   row_count
0         35

Parse dates and preserve nullable values 🔝

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE orders (id TEXT, ordered TEXT, units INTEGER)")
    connection.executemany("INSERT INTO orders VALUES (?, ?, ?)",
        [("001", "2026-01-02", 2), ("002", "2026-01-03", None)])
    result = pd.read_sql("SELECT * FROM orders ORDER BY id", connection,
        parse_dates={"ordered": {"format": "%Y-%m-%d"}},
        dtype={"id": "string", "units": "Int64"})
    print(result)
    print(result.dtypes)
    assert result["id"].tolist() == ["001", "002"]
    assert result["units"].isna().sum() == 1
    assert result.loc[0, "ordered"] == pd.Timestamp("2026-01-02")
finally:
    connection.close()

Expected output

    id    ordered  units
0  001 2026-01-02      2
1  002 2026-01-03   <NA>
id                 string
ordered    datetime64[us]
units               Int64
dtype: object

Process chunks while the connection stays open 🔝

connection = student_connection()
try:
    chunks = pd.read_sql("SELECT id, mark FROM student ORDER BY id", connection, chunksize=10)
    row_count = 0
    mark_total = 0
    sizes = []
    for chunk in chunks:
        sizes.append(len(chunk))
        row_count += len(chunk)
        mark_total += int(chunk["mark"].sum())
    print("Chunk sizes:", sizes)
    print("Rows:", row_count, "Total marks:", mark_total)
    assert sizes == [10, 10, 10, 5]
    assert row_count == 35 and mark_total == students["mark"].sum()
finally:
    connection.close()

Expected output

Chunk sizes: [10, 10, 10, 5]
Rows: 35 Total marks: 2613

Read a grouped report rather than every row 🔝

connection = student_connection()
try:
    query = """SELECT class, COUNT(*) AS students, SUM(mark) AS total_mark,
        AVG(mark) AS average_mark FROM student GROUP BY class ORDER BY class"""
    report = pd.read_sql(query, connection)
    print(report.round({"average_mark": 2}))
    assert report["students"].sum() == 35
    assert report["total_mark"].sum() == students["mark"].sum()
finally:
    connection.close()

Expected output

   class  students  total_mark  average_mark
0  Eight         1          79         79.00
1  Fifth         1          78         78.00
2   Five         2         160         80.00
3   Four         9         638         70.89
4   Nine         2          83         41.50
5  Seven        10         776         77.60
6    Six         7         578         82.57
7  Three         3         221         73.67

SQLAlchemy, connection strings and other databases 🔝

try:
    from sqlalchemy import create_engine, text
except ImportError:
    print("Optional SQLAlchemy example skipped: install sqlalchemy to run it.")
else:
    engine = create_engine("sqlite:///:memory:")
    try:
        with engine.begin() as connection:
            students.to_sql("student", connection, index=False, if_exists="replace")
        with engine.connect() as connection:
            query = text("SELECT id, name, mark FROM student WHERE class=:my_class ORDER BY id")
            result = pd.read_sql(query, connection, params={"my_class": "Five"})
            print(result)
            assert result["id"].tolist() == [8, 18]
    finally:
        engine.dispose()

Expected output

Optional SQLAlchemy example skipped: install sqlalchemy to run it.

Questions and common pitfalls 🔝

Exercise: high-scoring Four-class students 🔝

Exercise solution 🔝

connection = student_connection()
try:
    query = "SELECT id, name, mark FROM student WHERE class=:class AND mark>=:mark ORDER BY id"
    answer = pd.read_sql(query, connection, params={"class": "Four", "mark": 80})
    print(answer)
    print("Selected rows:", len(answer))
    assert answer["id"].tolist() == [15, 16, 31]
finally:
    connection.close()

Expected output

   id         name  mark
0  15     Tade Row    88
1  16        Gimmy    88
2  31  Marry Toeey    88
Selected rows: 3

Practice in Google Colab 🔝

Open in Google Colab View on GitHub

Run examples in order, inspect queries and records, then change bound values. Save a copy to keep edits.

Reference: Pandas read_sql documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: