Pandas MySQL read_sql(): Queries, Parameters and Reports
Read MySQL records into Pandas with SQLAlchemy. Practise bound filters, LIKE, indexes, columns, chunks and aggregate reports with embedded student data.
read_sql to create Pandas DataFrame by using query from MySQL database table with options.
Show Table of Contents ↓Create the portable student database 🔝
The notebook embeds the original 35-student sample. SQLite provides runnable practice for SQLAlchemy query operations; it does not reproduce every MySQL dialect or collation rule. Install Pandas and SQLAlchemy before Run All. The live connection example is separate.
import pandas as pd
from io import StringIO
from sqlalchemy import create_engine, text, inspect
from sqlalchemy.engine import URL
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)
return engine
print(students.head())
print("Student rows:", len(students))
assert len(students) == 35Expected 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: 35Connect to MySQL using SQLAlchemy 🔝
Configure a MySQL driver and private credentials. SQLAlchemy supports mysql+pymysql and mysql+mysqlconnector with the corresponding installed driver. Pandas officially supports SQLAlchemy connectables for this workflow; passing a raw non-SQLite DBAPI connection is not its general supported route. See MySQL connections and SQLAlchemy. This example constructs a masked URL without connecting.
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"Expected output
mysql+pymysql://practice_user:***@localhost/practice_db?charset=utf8mb4Optional live MySQL read 🔝
This read-only branch stays disabled in Run All. Install PyMySQL, supply your private environment settings and provision your own student table before enabling it. The query selects class Four; use the exact stored labels and account for your database collation. No credentials are embedded in the notebook.
import os
run_mysql = False
if run_mysql:
private_url = URL.create("mysql+pymysql", username=os.environ["MYSQL_USER"],
password=os.environ["MYSQL_PASSWORD"], host=os.environ["MYSQL_HOST"],
database=os.environ["MYSQL_DATABASE"], query={"charset": "utf8mb4"})
engine = create_engine(private_url)
try:
with engine.connect() as connection:
result = pd.read_sql(text("SELECT * FROM student WHERE class=:class ORDER BY id"),
connection, params={"class": "Four"})
print(result)
finally:
engine.dispose()
else:
print("Live MySQL read disabled; practice examples use SQLite.")Expected output
Live MySQL read disabled; practice examples use SQLite.Read class Four and select fields 🔝
Retain the class Four example and name/class selection. Project needed columns in SQL for larger tables; selecting DataFrame fields afterwards also works.
engine = student_engine()
try:
with engine.connect() as connection:
result = pd.read_sql(text("SELECT * FROM student WHERE class=:class ORDER BY id"),
connection, params={"class": "Four"})
print(result)
print(result[["name", "class"]].head())
assert result["id"].tolist() == [1, 4, 5, 6, 10, 15, 16, 21, 31]
finally:
engine.dispose()Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 4 Krish Star Four 60 female
2 5 John Mike Four 60 female
3 6 Alex John Four 55 male
4 10 Big John Four 55 female
5 15 Tade Row Four 88 male
6 16 Gimmy Four 88 male
7 21 Babby John Four 69 female
8 31 Marry Toeey Four 88 male
name class
0 John Deo Four
1 Krish Star Four
2 John Mike Four
3 Alex John Four
4 Big John FourBind a single parameter with a dictionary 🔝
SQLAlchemy text() uses named binds with a dictionary. A set such as {"Four"} is not a parameter mapping. Driver-native placeholders differ: MySQL commonly uses %s with a positional tuple, while SQLite uses ?. A one-item tuple needs a trailing comma. Prefer named SQLAlchemy binds for these portable examples.
engine = student_engine()
try:
with engine.connect() as connection:
query = text("SELECT id, name FROM student WHERE class=:class ORDER BY id")
result = pd.read_sql(query, connection, params={"class": "Five"})
print(result)
assert result["id"].tolist() == [8, 18]
unmatched = pd.read_sql(query, connection, params={"class": "Five' OR 1=1 --"})
assert unmatched.empty
print("Unmatched literal input rows:", len(unmatched))
finally:
engine.dispose()Expected output
id name
0 8 Asruid
1 18 Honny
Unmatched literal input rows: 0Bind several values and an IN selection 🔝
Preserve the original Four-class IDs 6 and 10 selection. Bind each value independently. For variable-length IN lists, SQLAlchemy expanding bind parameters are another option; do not construct SQL by joining arbitrary user input.
engine = student_engine()
try:
with engine.connect() as connection:
query = text("SELECT id, name, class FROM student WHERE class=:class AND id IN (:first,:second) ORDER BY id")
result = pd.read_sql(query, connection, params={"class": "Four", "first": 6, "second": 10})
print(result)
assert result["id"].tolist() == [6, 10]
finally:
engine.dispose()Expected output
id name class
0 6 Alex John Four
1 10 Big John FourBind a LIKE pattern 🔝
Pass wildcard text as a bound value, such as %John%, rather than embedding escaped driver-specific percent characters into the SQL. LIKE case behavior depends on the database and collation. For literal percent or underscore searches, choose an explicit escape policy.
engine = student_engine()
try:
with engine.connect() as connection:
result = pd.read_sql(text("SELECT id, name FROM student WHERE name LIKE :pattern ORDER BY id"),
connection, params={"pattern": "%John%"})
print(result)
assert result["id"].tolist() == [1, 5, 6, 7, 10, 21]
finally:
engine.dispose()Expected output
id name
0 1 John Deo
1 5 John Mike
2 6 Alex John
3 7 My John Rob
4 10 Big John
5 21 Babby JohnUse id as the DataFrame index 🔝
index_col controls DataFrame row labels, not a database key. The current sample labels My John Rob as Fifth, so the exact Five filter selects Asruid and Honny.
engine = student_engine()
try:
with engine.connect() as connection:
result = pd.read_sql(text("SELECT * FROM student WHERE class=:class ORDER BY id"),
connection, params={"class": "Five"}, index_col="id")
print(result)
assert result.index.tolist() == [8, 18]
finally:
engine.dispose()Expected output
name class mark gender
id
8 Asruid Five 85 male
18 Honny Five 75 maleRead a table with selected columns 🔝
read_sql() accepts a table name through SQLAlchemy and routes it to table reading. columns applies to this table-name mode; for a query specify fields in SELECT. SQL table reads do not guarantee row order, so sort for predictable display. See read_sql_table().
engine = student_engine()
try:
with engine.connect() as connection:
selected = pd.read_sql("student", connection, columns=["id", "name"])
print(selected.sort_values("id").head())
all_fields = pd.read_sql("student", connection)
print("Fields:", all_fields.columns.tolist())
for field in all_fields.columns:
print(field)
assert selected.columns.tolist() == ["id", "name"] and len(selected) == 35
finally:
engine.dispose()Expected output
id name
0 1 John Deo
1 2 Max Ruin
2 3 Arnold
3 4 Krish Star
4 5 John Mike
Fields: ['id', 'name', 'class', 'mark', 'gender']
id
name
class
mark
genderRead the first two chunks and consume the rest 🔝
Keep the connection open until the iterator is consumed. ORDER BY gives a defined display sequence. chunksize limits rows per DataFrame; driver buffering still affects total memory usage.
engine = student_engine()
try:
with engine.connect() as connection:
iterator = pd.read_sql(text("SELECT * FROM student ORDER BY id"), connection, chunksize=3)
first = next(iterator)
second = next(iterator)
print(first)
print("--End of first set of records--")
print(second)
total = len(first) + len(second) + sum(len(chunk) for chunk in iterator)
assert first["id"].tolist() == [1, 2, 3]
assert second["id"].tolist() == [4, 5, 6]
assert total == 35
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
--End of first set of records--
id name class mark gender
0 4 Krish Star Four 60 female
1 5 John Mike Four 60 female
2 6 Alex John Four 55 maleRead maximum, average and grouped marks 🔝
Retain the original aggregate query concepts with explicit mark columns. COUNT counts records; MAX, MIN and AVG measure marks. SQL aggregates commonly ignore NULL inputs, so report observation counts when data may be incomplete.
engine = student_engine()
try:
with engine.connect() as connection:
overall = pd.read_sql(text("SELECT MAX(mark) AS highest, AVG(mark) AS average FROM student"), connection)
grouped = pd.read_sql(text("SELECT class, COUNT(*) AS students, MAX(mark) AS highest, AVG(mark) AS average, MIN(mark) AS lowest FROM student GROUP BY class ORDER BY class"), connection)
print(overall.round(2))
print(grouped.round(2))
assert overall.loc[0, "highest"] == students["mark"].max()
assert abs(overall.loc[0, "average"] - students["mark"].mean()) < 1e-10
assert grouped["students"].sum() == 35
finally:
engine.dispose()Expected output
highest average
0 96 74.66
class students highest average lowest
0 Eight 1 79 79.00 79
1 Fifth 1 78 78.00 78
2 Five 2 85 80.00 75
3 Four 9 88 70.89 55
4 Nine 2 65 41.50 18
5 Seven 10 90 77.60 55
6 Six 7 96 82.57 54
7 Three 3 85 73.67 55Read the first and second ranked records 🔝
Preserve the highest and second-record queries for class Five. This is row ranking rather than second distinct mark. Resolve ties with id as a secondary ordering field.
engine = student_engine()
try:
with engine.connect() as connection:
query = text("SELECT id, name, mark FROM student WHERE class=:class ORDER BY mark DESC, id ASC LIMIT 1 OFFSET :offset")
highest = pd.read_sql(query, connection, params={"class": "Five", "offset": 0})
second = pd.read_sql(query, connection, params={"class": "Five", "offset": 1})
print(highest)
print(second)
assert highest.loc[0, "id"] == 8 and second.loc[0, "id"] == 18
finally:
engine.dispose()Expected output
id name mark
0 8 Asruid 85
id name mark
0 18 Honny 75Execute table creation separately from result queries 🔝
Use connection.execute() for CREATE/INSERT/UPDATE rather than a DataFrame reader. Execute distinct statements separately instead of concatenating them. Retain the primary-key and uniqueness concept: a primary key is already unique. This SQLite example creates student5 with a primary key; MySQL-specific engine and charset clauses belong only in a MySQL schema. See inserting records.
engine = create_engine("sqlite:///:memory:")
try:
with engine.begin() as connection:
connection.execute(text("CREATE TABLE student5 (id INTEGER PRIMARY KEY, name TEXT, class TEXT, mark INTEGER, gender TEXT)"))
connection.execute(text("INSERT INTO student5 VALUES (:id,:name,:class,:mark,:gender)"),
{"id": 1, "name": "John Deo", "class": "Four", "mark": 75, "gender": "female"})
result = pd.read_sql(text("SELECT * FROM student5 ORDER BY id"), connection)
print(result)
assert inspect(engine).get_pk_constraint("student5")["constrained_columns"] == ["id"]
finally:
engine.dispose()Expected output
id name class mark gender
0 1 John Deo Four 75 femaleRead dates, nullable integers and text identifiers 🔝
This synthetic order example checks types explicitly. parse_dates accepts column lists or parsing mappings. dtype applies to query reads; table mode has different dtype support. The database must store identifiers as text to keep leading zeros.
engine = create_engine("sqlite:///:memory:")
try:
with engine.begin() as connection:
connection.execute(text("CREATE TABLE orders (id TEXT, date TEXT, units INTEGER)"))
connection.execute(text("INSERT INTO orders VALUES (:id,:date,:units)"),
[{"id": "001", "date": "2026-01-02", "units": 2},
{"id": "002", "date": "2026-01-03", "units": None}])
with engine.connect() as connection:
result = pd.read_sql(text("SELECT * FROM orders ORDER BY id"), connection,
parse_dates={"date": {"format": "%Y-%m-%d"}}, dtype={"id": "string", "units": "Int64"})
print(result)
assert result["id"].tolist() == ["001", "002"]
assert result["units"].isna().sum() == 1
finally:
engine.dispose()Expected output
id date units
0 001 2026-01-02 2
1 002 2026-01-03 <NA>Parameters and connection pitfalls 🔝
| Parameter | Use |
|---|---|
sql / con | Query or table name, and a supported connection. |
params | Bind query values; placeholder syntax depends on the interface/driver. |
index_col | Columns to use as DataFrame row labels. |
coerce_float | May convert Decimal-like objects to float; assess precision requirements. |
parse_dates | Date parsing options; plural name. |
columns | Selected fields in table-name mode, not query filtering. |
chunksize | Return a DataFrame iterator; retain its connection while reading. |
dtype / dtype_backend | Query dtype mapping or supported nullable backend choices. |
Bind data values and keep identifiers controlled; read_sql() does not sanitize an entire supplied SQL statement. For large reports push field selection and aggregation into SQL. Close connections and dispose engines. Check the actual sample labels and column names rather than assuming sex and gender are interchangeable. Keep DDL separate from SELECT readers and do not assume MySQL DDL transaction behavior matches SQLite. For graphing, see charts from database data.
Exercise: read a high-mark class selection 🔝
Read id, name and mark for class Four and a minimum mark of 80. Bind both values, order by ID and verify the selected records.
Exercise solution 🔝
Use bound predicates and select only the fields required for the report.
engine = student_engine()
try:
with engine.connect() as connection:
answer = pd.read_sql(text("SELECT id, name, mark FROM student WHERE class=:class AND mark>=:mark ORDER BY id"),
connection, params={"class": "Four", "mark": 80})
print(answer)
assert answer["id"].tolist() == [15, 16, 31]
finally:
engine.dispose()Expected output
id name mark
0 15 Tade Row 88
1 16 Gimmy 88
2 31 Marry Toeey 88Practice 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 MySQL 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.
Connect:
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
Python Video Tutorials