How to Use MySQL with Jupyter Notebooks

I spend a lot of my analysis time bouncing between raw SQL and Python, and Jupyter Notebooks turned out to be the most natural place to do both at once. Instead of exporting query results to CSV and reloading them somewhere else, I can query MySQL directly inside a notebook cell, get a pandas DataFrame back, and immediately plot, clean, or model it. In this article, I’ll walk through everything from setting up the connection to advanced query optimization, so you can build a smooth, reproducible data analysis workflow on top of MySQL.

Table of Contents

1. MySQL Architecture Refresher
2. Why Use MySQL with Jupyter Notebooks
3. Setting Up the Environment
4. Connecting to MySQL from a Notebook
5. Running Queries and Loading Data into pandas
6. Using SQL Magic Commands
7. Exploratory Data Analysis Workflows
8. Writing Data Back to MySQL
9. Indexing and Query Optimization from the Notebook
10. Visualizing MySQL Data
11. Handling Large Result Sets
12. Security Best Practices
13. Troubleshooting Common Issues
14. Interview Questions
15. FAQs
16. Summary and Key Takeaways
17. References

1. MySQL Architecture Refresher

Even in a data science context, it helps to remember what’s happening server-side. MySQL parses your SQL, the optimizer picks an execution plan, and the InnoDB storage engine reads or writes rows, using its buffer pool to cache frequently accessed pages in memory.

graph TD
    A[Jupyter Notebook] --> B[Python MySQL Connector]
    B --> C[MySQL Server]
    C --> D[Query Optimizer]
    D --> E[InnoDB Storage Engine]
    E --> F[Buffer Pool]
    E --> G[Disk Tablespace]

Understanding this matters because a notebook that runs SELECT * FROM huge_table without a LIMIT isn’t just slow in Python — it forces the server to scan potentially millions of rows, which is a load issue on the database itself, not just your local kernel.

2. Why Use MySQL with Jupyter Notebooks

I reach for this combination when I need to:

• Explore and clean data pulled directly from a production or reporting database.
• Prototype SQL queries interactively before embedding them in an application.
• Build ad-hoc dashboards and charts from live data.
• Train machine learning models on tabular data stored in MySQL.
• Document an analysis with narrative text, code, and query output side by side.

3. Setting Up the Environment

I typically set up a virtual environment first, to keep dependencies isolated:

python -m venv mysql-notebook-env
source mysql-notebook-env/bin/activate
pip install jupyterlab pandas sqlalchemy mysql-connector-python pymysql matplotlib seaborn

Then I launch JupyterLab:

jupyter lab

For SQL magic commands later in this article, I also install:

pip install ipython-sql

4. Connecting to MySQL from a Notebook

There are two connection styles I use depending on the task: a raw connector for simple queries, and SQLAlchemy when I want pandas integration or ORM-style access.

Using mysql-connector-python directly:

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="analyst",
    password="AnalystPass123!",
    database="salesdb"
)

cursor = conn.cursor()
cursor.execute("SELECT VERSION();")
print(cursor.fetchone())

Output:

('8.0.36',)

Using SQLAlchemy (my preferred method for pandas workflows):

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("mysql+pymysql://analyst:AnalystPass123!@localhost:3306/salesdb")

df = pd.read_sql("SELECT * FROM orders LIMIT 10;", engine)
df.head()

I keep credentials out of notebook cells entirely by loading them from environment variables:

import os
from sqlalchemy import create_engine

user = os.environ["MYSQL_USER"]
password = os.environ["MYSQL_PASSWORD"]
engine = create_engine(f"mysql+pymysql://{user}:{password}@localhost:3306/salesdb")

5. Running Queries and Loading Data into pandas

Once the engine is set up, pulling any query result into a DataFrame is one line:

query = """
SELECT customer_id, SUM(total) AS total_spent, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 20;
"""

top_customers = pd.read_sql(query, engine)
top_customers

Sample output:

   customer_id  total_spent  order_count
0          1042      4820.50           37
1          2091      4210.75           29
2          3087      3990.10           25

From here, everything is standard pandas — filtering, grouping, merging with other DataFrames, or feeding into scikit-learn.

6. Using SQL Magic Commands

ipython-sql lets me write SQL directly in a cell without wrapping it in Python strings, which I find much more readable during exploratory work.

%load_ext sql
%sql mysql+pymysql://analyst:AnalystPass123!@localhost:3306/salesdb

%%sql
SELECT product_category, AVG(total) AS avg_order_value
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY product_category
ORDER BY avg_order_value DESC;

The result renders directly as a table below the cell, and I can convert it to a DataFrame with .DataFrame() if I need further manipulation:

result = _
df = result.DataFrame()

7. Exploratory Data Analysis Workflows

A typical EDA session for me looks like this:

df = pd.read_sql("SELECT * FROM orders;", engine)

df.info()
df.describe()
df['total'].hist(bins=30)
df.isnull().sum()

I also profile join cardinality directly with SQL before pulling anything into Python, since it’s faster to catch a fan-out join at the database level than after loading a bloated DataFrame:

SELECT COUNT(*) FROM orders o JOIN order_items oi ON o.id = oi.order_id;

8. Writing Data Back to MySQL

After cleaning or transforming data, I write it back using to_sql:

cleaned_df.to_sql(
    name="orders_cleaned",
    con=engine,
    if_exists="replace",
    index=False,
    chunksize=1000
)

I always set chunksize for anything beyond a few thousand rows — inserting one giant statement can lock the table longer than necessary and increases memory pressure on both ends.

For inserting a small number of new rows manually:

with engine.connect() as conn:
    conn.execute(
        "INSERT INTO customer_notes (customer_id, note) VALUES (%s, %s)",
        (1042, "Flagged for follow-up")
    )

9. Indexing and Query Optimization from the Notebook

Since I’m running exploratory queries constantly, I check execution plans right inside the notebook before trusting a query on a large table:

plan = pd.read_sql("EXPLAIN SELECT * FROM orders WHERE customer_id = 1042;", engine)
plan

   id  select_type  table   type  possible_keys      key             rows  Extra
0   1  SIMPLE       orders  ref   idx_customer_id    idx_customer_id  12   NULL

If type shows ALL instead of ref or range, that’s my signal an index is missing:

CREATE INDEX idx_customer_id ON orders(customer_id);

10. Visualizing MySQL Data

Once data is in a DataFrame, I usually visualize with matplotlib or seaborn directly in the notebook:

import seaborn as sns
import matplotlib.pyplot as plt

monthly_sales = pd.read_sql("""
    SELECT DATE_FORMAT(created_at, '%%Y-%%m') AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY month
    ORDER BY month;
""", engine)

plt.figure(figsize=(10,5))
sns.lineplot(data=monthly_sales, x="month", y="revenue")
plt.xticks(rotation=45)
plt.title("Monthly Revenue")
plt.show()

Note the escaped %%Y-%%m — pandas’ read_sql passes strings through Python’s string formatting in some drivers, so a literal % in a DATE_FORMAT string needs escaping to avoid a TypeError. This tripped me up the first time I hit it.

11. Handling Large Result Sets

For tables with millions of rows, I never load everything into memory at once. Instead, I use chunked reads:

chunks = pd.read_sql("SELECT * FROM orders;", engine, chunksize=50000)

total_revenue = 0
for chunk in chunks:
    total_revenue += chunk['total'].sum()

print(total_revenue)

This keeps memory usage predictable regardless of table size, since only one chunk is held in memory at a time.

12. Security Best Practices

• Never hardcode credentials in a notebook cell — use environment variables or a .env file excluded from version control.
• Create a read-only analyst user for exploratory notebooks; only grant write access when a notebook explicitly needs to persist results.
• Be careful sharing notebooks that have already executed cells — cached output can leak sensitive data even if the code itself looks clean.
• Use SSL connections when querying a remote MySQL server over an untrusted network:

engine = create_engine(
    "mysql+pymysql://analyst:pass@remotehost:3306/salesdb?ssl_ca=/path/to/ca.pem"
)

CREATE USER 'analyst'@'%' IDENTIFIED BY 'AnalystPass123!';
GRANT SELECT ON salesdb.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

13. Troubleshooting Common Issues

13.5 Building Reusable Query Functions in a Notebook

As a notebook grows, I stop repeating connection and query boilerplate in every cell and instead wrap common patterns in small helper functions near the top of the notebook:

def run_query(sql, params=None):
    """Run a parameterized query and return a DataFrame."""
    with engine.connect() as conn:
        return pd.read_sql(sql, conn, params=params)

def run_statement(sql, params=None):
    """Run an INSERT/UPDATE/DELETE and return affected row count."""
    with engine.begin() as conn:
        result = conn.execute(sql, params or {})
        return result.rowcount

Usage becomes much cleaner across the rest of the notebook:

top_orders = run_query(
    "SELECT * FROM orders WHERE customer_id = %(cust_id)s ORDER BY created_at DESC LIMIT 5;",
    {"cust_id": 1042}
)
top_orders

Using parameterized queries this way isn’t just cleaner — it also protects against SQL injection if any part of the query ever comes from user-supplied input rather than a hardcoded value, which matters even in an internal analysis notebook that might later get turned into a scheduled job.

13.6 Scheduling Notebooks as Recurring Reports

Once an exploratory notebook turns into something I want to run daily or weekly, I convert it into a parameterized script using papermill rather than manually re-running cells:

pip install papermill

papermill sales_report.ipynb output/sales_report_$(date +%F).ipynb \
  -p report_date "2026-07-30" \
  -p region "APAC"

Inside the notebook, I mark a cell with the parameters tag so papermill knows where to inject values:

# This cell is tagged "parameters"
report_date = "2026-07-01"
region = "US"

query = """
SELECT * FROM orders
WHERE region = %(region)s AND DATE(created_at) = %(report_date)s;
"""
df = run_query(query, {"region": region, "report_date": report_date})

I schedule the papermill command with cron or a Jenkins job, so the same notebook produces a fresh, dated output file automatically without me touching it.

13.7 Combining Multiple MySQL Sources in One Notebook

Occasionally I need to join data from two separate MySQL instances — for example, a production replica and a separate analytics database. Since a single SQL query can’t span two servers, I pull each into its own DataFrame and join them in pandas:

prod_engine = create_engine("mysql+pymysql://analyst:pass@prod-replica:3306/salesdb")
analytics_engine = create_engine("mysql+pymysql://analyst:pass@analytics-db:3306/metricsdb")

orders_df = pd.read_sql("SELECT id, customer_id, total FROM orders;", prod_engine)
churn_df = pd.read_sql("SELECT customer_id, churn_score FROM customer_scores;", analytics_engine)

merged = orders_df.merge(churn_df, on="customer_id", how="left")
merged.head()

This pattern — pull separately, join in pandas — is one I rely on constantly, since real organizations rarely keep every relevant table in a single schema.

14. Interview Questions

1. What’s the difference between using mysql-connector-python directly versus SQLAlchemy with pandas?
2. Why is it important to check EXPLAIN output before running a query on a large table in a notebook?
3. How would you handle a MySQL table with 50 million rows in a memory-constrained Jupyter environment?
4. What are the risks of committing an executed notebook to version control when it queries a production database?
5. How does to_sql‘s chunksize parameter affect performance and locking behavior?

15. FAQs

Can Jupyter Notebooks connect directly to a remote MySQL server? Yes, as long as network access and credentials are configured correctly; I recommend SSL for any connection over the public internet.

Is it safe to use root credentials in a notebook? No — I always create a scoped user with only the privileges the analysis actually needs, typically read-only.

What’s the best library for MySQL and pandas integration? I prefer SQLAlchemy with the pymysql driver because pd.read_sql and to_sql both work smoothly with it.

How do I avoid loading an entire huge table into memory? Use chunksize in pd.read_sql, or filter and aggregate as much as possible in SQL before pulling data into Python.

16. Summary and Key Takeaways

MySQL and Jupyter Notebooks pair well because they let me stay in one environment for the entire journey from raw SQL to a finished chart or model. The keys I keep coming back to are: use SQLAlchemy for clean pandas integration, check EXPLAIN before trusting a query against a large table, keep credentials out of notebook cells, and chunk large reads instead of loading everything into memory. Once those habits are in place, the notebook becomes a genuinely fast way to go from a question to an answer.

17. References

• MySQL 8.0 Reference Manual
• pandas read_sql Documentation
• SQLAlchemy Documentation
• Project Jupyter Documentation