0Pricing
Learn AI with Python · Lesson

Pandas and SQL Integration

pd.read_sql(), df.to_sql(), SQLAlchemy engine, querying database results directly into DataFrames.

Pandas and SQL Integration is a free Learn AI with Python lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Learn AI with Python learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Pandas Meets SQL

You do not have to choose between SQL and pandas — they work together. Query a database into a DataFrame, transform with pandas, and write results back.

The glue is SQLAlchemy plus the pandas functions read_sql and to_sql.

Why SQLAlchemy?

pandas talks to databases through a connection or a SQLAlchemy engine. The engine abstracts the database type, so the same code works for SQLite, PostgreSQL, MySQL, and more — just change the URL.

Creating an Engine

create_engine takes a connection URL. The URL scheme identifies the database driver.

from sqlalchemy import create_engine

engine = create_engine("sqlite:///ml.db")
# Postgres: create_engine("postgresql://user:pass@host:5432/db")

Reading a Table with pd.read_sql

pd.read_sql runs a query (or reads a table) and returns a DataFrame. Pass a SQL string and the engine.

import pandas as pd

df = pd.read_sql("SELECT * FROM experiments", engine)
print(df.head())

Reading with a Filtered Query

Let the database do filtering and aggregation, then hand a smaller result to pandas. This is far more efficient than loading everything.

df = pd.read_sql(
    "SELECT name, accuracy FROM experiments WHERE accuracy > 0.85 ORDER BY accuracy DESC",
    engine,
)
print(df)

Parameterized Queries

Pass query parameters safely with the params argument instead of formatting strings.

from sqlalchemy import text

df = pd.read_sql(
    text("SELECT * FROM experiments WHERE name = :n"),
    engine,
    params={"n": "xgb"},
)

Writing a DataFrame with df.to_sql

df.to_sql(table, engine) writes a DataFrame to a database table, creating it from the DataFrame dtypes if needed.

df.to_sql("results", engine, index=False)

The index Parameter

By default to_sql writes the DataFrame index as a column. Usually you do not want that — pass index=False to skip it.

df.to_sql("results", engine, index=False)   # no extra index column

The if_exists Parameter

if_exists controls behavior when the table already exists:

  • "fail" — raise an error (default)
  • "replace" — drop and recreate the table
  • "append" — add rows to the existing table
df.to_sql("results", engine, if_exists="append", index=False)
# Use "replace" to overwrite, "fail" to be safe

A Round-Trip Workflow

A typical pipeline: read raw data from SQL, transform in pandas, write the cleaned result back to a new table.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///ml.db")
raw = pd.read_sql("SELECT * FROM experiments", engine)
raw["accuracy_pct"] = (raw["accuracy"] * 100).round(1)
raw.to_sql("experiments_clean", engine, if_exists="replace", index=False)

Chunked Reads for Big Tables

For tables too large to fit in memory, pass chunksize to read_sql to iterate over the result in batches.

for chunk in pd.read_sql("SELECT * FROM big_table", engine, chunksize=10000):
    process(chunk)   # handle 10k rows at a time

Quick Check: Appending Rows

You want to_sql to add new rows to an existing table without dropping it.

Recap: pandas and SQL

You can now move data between pandas and databases:

  • create_engine(url) for a database-agnostic connection
  • pd.read_sql(query, engine) to load query results into a DataFrame
  • df.to_sql(table, engine, index=False, if_exists=...) to write back
  • Parameterized queries and chunksize for safety and scale

Next: storing and querying ML experiment results.

Frequently asked questions

Is the “Pandas and SQL Integration” lesson free?

Yes — the full text of “Pandas and SQL Integration” is free to read here on the web, and the Learn AI with Python course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Learn AI with Python course, upgrade to CoddyKit PRO.

What will I learn in “Pandas and SQL Integration”?

pd.read_sql(), df.to_sql(), SQLAlchemy engine, querying database results directly into DataFrames. You practise Learn AI with Python with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Learn AI with Python?

No prior experience is required. Learn AI with Python on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Pandas and SQL Integration” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Learn AI with Python lesson?

Yes. Every Learn AI with Python lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. SQLite with Python's sqlite3 Module
  2. Pandas and SQL Integration
  3. Storing and Querying ML Results
  4. Introduction to Vector Databases
← Back to Learn AI with Python