Pandas read_sql_table(): MySQL Connections and Portable Table Imports

Read SQL tables into Pandas using SQLAlchemy, selected columns, indexes, dates and chunks. Practise with embedded student data and configure MySQL separately.

Show Table of Contents ↓

Create a portable student table for practice 🔝

read_sql_table() reads a table through SQLAlchemy; it does not accept a raw sqlite3 connection. The same API works with supported MySQL engines. The notebook uses the original 35-student sample and an in-memory SQLite engine so no server or credentials are needed.

import pandas as pd
import tempfile
from pathlib import Path
from io import StringIO
from sqlalchemy import create_engine, text, inspect
from sqlalchemy.types import Integer, String, DateTime
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_engine():
    engine = create_engine("sqlite:///:memory:")
    with engine.begin() as connection:
        students.to_sql("student", connection, index=False, if_exists="fail")
    return engine
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

Read all records from a table 🔝

Pass the table name and managed connection. Table reads have no ORDER BY parameter; sort when you need predictable display order. The example reads all 35 records, then displays the first five.

engine = student_engine()
try:
    with engine.connect() as connection:
        result = pd.read_sql_table("student", connection)
    result = result.sort_values("id").reset_index(drop=True)
    print(result.head())
    print("Rows:", len(result))
    pd.testing.assert_frame_equal(result, students)
finally:
    engine.dispose()

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
Rows: 35

Build a MySQL URL without exposing credentials 🔝

To use your MySQL student table, install SQLAlchemy and a compatible driver, provision the table and configure private connection settings. MySQL with SQLAlchemy explains connections; the student sample provides practice data. URL.create handles password characters. This example creates a URL only; it does not connect. Replace the local settings with your private configuration when running against MySQL.

from sqlalchemy.engine import URL
url = URL.create("mysql+pymysql", username="practice_user", password="practice_password",
    host="localhost", database="practice_db", query={"charset": "utf8mb4"})
print(url.render_as_string(hide_password=True))
assert url.drivername == "mysql+pymysql"
# Live MySQL (requires pymysql and your own private settings):
# engine = create_engine(url)
# try:
#     with engine.connect() as connection:
#         result = pd.read_sql_table("student", connection)
# finally:
#     engine.dispose()

Expected output

mysql+pymysql://practice_user:***@localhost/practice_db?charset=utf8mb4

Use id as the DataFrame index 🔝

index_col selects labels after the table is read. It does not add a database index or primary key.

engine = student_engine()
try:
    with engine.connect() as connection:
        result = pd.read_sql_table("student", connection, index_col="id")
    result = result.sort_index()
    print(result.head())
    pd.testing.assert_frame_equal(result, students.set_index("id"))
finally:
    engine.dispose()

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

Read selected columns and an index 🔝

Preserve the original name/class selection with id as the index. columns selects table fields rather than filtering records. Use read_sql_query() or read_sql() with SELECT for WHERE, ORDER BY or LIMIT.

engine = student_engine()
try:
    with engine.connect() as connection:
        result = pd.read_sql_table("student", connection, columns=["name", "class"], index_col="id")
    result = result.sort_index()
    print(result.head())
    assert result.columns.tolist() == ["name", "class"] and len(result) == 35
finally:
    engine.dispose()

Expected output

          name  class
id                   
1     John Deo   Four
2     Max Ruin  Three
3       Arnold  Three
4   Krish Star   Four
5    John Mike   Four

Read the first two three-row chunks 🔝

chunksize returns an iterator. Keep the connection open while consuming it. The first two displayed chunks retain the original exercise layout; this practice table inserts rows in ID order, but SQL table reads do not guarantee order in general. For ordered chunking, use an explicit SELECT ORDER BY.

engine = student_engine()
try:
    with engine.connect() as connection:
        iterator = pd.read_sql_table("student", connection, index_col="id", chunksize=3)
        first = next(iterator)
        second = next(iterator)
        print(first)
        print(second)
        remaining = sum(len(chunk) for chunk in iterator)
        assert len(first) == len(second) == 3
        assert len(first) + len(second) + remaining == 35
finally:
    engine.dispose()

Expected output

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

Aggregate all chunks without combining them 🔝

Accumulate totals instead of concatenating every chunk. This bounds the DataFrame work per batch, though driver buffering can still affect memory use.

engine = student_engine()
try:
    with engine.connect() as connection:
        chunks = pd.read_sql_table("student", connection, columns=["id", "mark"], chunksize=10)
        count = total = 0
        sizes = []
        for chunk in chunks:
            sizes.append(len(chunk))
            count += len(chunk)
            total += int(chunk["mark"].sum())
    print("Chunk sizes:", sizes)
    print("Rows:", count, "Total marks:", total)
    assert count == 35 and total == students["mark"].sum()
finally:
    engine.dispose()

Expected output

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

Read dates and nullable values 🔝

This synthetic order table illustrates parse_dates and dtype_backend="numpy_nullable". The database stores identifiers as text to keep their zeros. inspect the resulting types instead of assuming every SQL type maps identically across drivers.

engine = create_engine("sqlite:///:memory:")
try:
    with engine.begin() as connection:
        connection.execute(text("CREATE TABLE orders (id TEXT, ordered TEXT, units INTEGER)"))
        connection.execute(text("INSERT INTO orders VALUES (:id,:ordered,:units)"),
            [{"id": "001", "ordered": "2026-01-02", "units": 2},
             {"id": "002", "ordered": "2026-01-03", "units": None}])
    with engine.connect() as connection:
        result = pd.read_sql_table("orders", connection,
            parse_dates={"ordered": {"format": "%Y-%m-%d"}}, dtype_backend="numpy_nullable")
    result = result.sort_values("id").reset_index(drop=True)
    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:
    engine.dispose()

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

Parameter choices and table-reading limits 🔝

Parameter	Purpose
`table_name / con`	Existing table and SQLAlchemy connectable or database URL.
`schema`	Supported schema name; default None.
`index_col`	One or more columns for DataFrame labels.
`coerce_float`	May convert Decimal-like values to float; consider precision needs.
`parse_dates`	Column list or date-parsing mapping; plural parameter name.
`columns`	Selected table columns; all by default.
`chunksize`	Return a DataFrame iterator instead of a single DataFrame.
`dtype_backend`	Select nullable NumPy or optional PyArrow backend.

There is no params, WHERE or ORDER BY argument for read_sql_table(). Use a query reader for filtered or ordered requests. Keep a chunk iterator's connection open. Close connections and dispose engines. A missing table or schema is a database error rather than an empty result. Select only needed columns and avoid converting precise Decimal values to float without an explicit policy. Continue with MySQL read_sql().

Exercise: read and filter selected fields 🔝

Read id, name and mark from student with id as the index. Select marks at least 90, sort by ID and verify the selected count.

Exercise solution 🔝

Read only the needed fields, then filter in Pandas. For very large tables, filter directly with a SQL query instead.

engine = student_engine()
try:
    with engine.connect() as connection:
        result = pd.read_sql_table("student", connection, index_col="id", columns=["name", "mark"])
    answer = result.loc[result["mark"].ge(90)].sort_index()
    print(answer)
    print("Selected rows:", len(answer))
    assert answer.index.tolist() == [12, 32, 33]
finally:
    engine.dispose()

Expected output

         name  mark
id                 
12      Recky    94
32  Binn Rott    90
33  Kenn Rein    96
Selected rows: 3

Practice in Google Colab 🔝

Open in Google Colab View on GitHub

Run examples in order, inspect database records and try the exercise. Save a copy to keep edits.

Reference: Pandas read_sql_table() 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:

Subscribe to our YouTube Channel here