Running SQL Queries from Pandas

pandas read_sql and running SQL queries on pandas dataframe actually cover two genuinely different things that get blurred together constantly: pulling data from a real database into a DataFrame, and running literal SQL syntax against a DataFrame that's already sitting in memory. This article draws that line clearly, following directly from the previous sqlite3/SQLAlchemy article.

Two Different Meanings of "SQL from Pandas"

Two genuinely distinct things

The previous article covered connecting Python to a database. This one covers two related, but genuinely distinct things: pulling data from a real database straight into a DataFrame (read_sql, to_sql), and running SQL-syntax queries directly against a DataFrame that already lives in memory, with no actual database involved at all (pandasql, and its modern successor, DuckDB).

Why both matter, for different reasons

The first is about data movement — getting a query's results out of a real database and into pandas for analysis, exactly the workflow covered in the previous article. The second is about readability — some people, especially those who learned SQL before pandas, find SQL syntax more direct than chained pandas methods for certain filters, joins, and aggregations, even when no actual database is involved anywhere in the picture.

What this article covers

read_sql()/read_sql_query() for real databases, to_sql() for writing back, and pandasql's sqldf() (plus a note on DuckDB, its modern, considerably faster successor) for running literal SQL against DataFrames without any database connection whatsoever.

Reading a Database Table or Query with read_sql()

A convenience wrapper
import pandas as pd
import sqlite3

c sqlite3.connect("company.db")
df = pd.read_sql("SELECT * FROM employees WHERE salary > 60000", conn)

pd.read_sql() is a convenience wrapper that reads either a SQL query or an entire database table directly into a DataFrame, delegating internally to read_sql_table() for a plain table name, or read_sql_query() for actual SQL text, depending on exactly what you pass in.

A practical example
from sqlalchemy import create_engine

engine = create_engine("sqlite:///company.db")

query = "SELECT name, department, salary FROM employees WHERE department = 'Engineering'"
df = pd.read_sql(query, engine)
print(df.head())

This works identically whether you connect through a SQLAlchemy engine or a plain sqlite3 connection object, exactly as covered in the previous article — pd.read_sql() accepts either, running the given SELECT query with its WHERE clause and returning the result as a fully-formed DataFrame, with zero extra parsing needed on your part.

parse_dates: a genuinely useful convenience
df = pd.read_sql(
    "SELECT * FROM orders",
    engine,
    parse_dates=["order_date", "ship_date"]
)
print(df.dtypes)

parse_dates calls pd.to_datetime() automatically on the specified columns during the read itself — tying directly back to the earlier datetime article — genuinely saving a separate conversion step you'd otherwise need to remember to run manually every single time, exactly the kind of small, easy-to-forget step covered as a common pitfall in the earlier datetime and data-cleaning articles.

Writing Parameterized Queries Safely

The security habit, repeated deliberately

This is worth repeating directly from the previous SQLAlchemy article, since it applies identically here: for parameterized queries, use the params argument, rather than building the query string with an f-string or other manual string interpolation.

from sqlalchemy import text

query = text("SELECT * FROM orders WHERE customer_id = :cust_id")
df = pd.read_sql(query, engine, params={"cust_id": 42})

Passing a SQLAlchemy text() object with a named placeholder (:cust_id), alongside a params dictionary supplying the actual value, is the recommended, safe pattern — genuinely equivalent to the ?-placeholder pattern covered for plain sqlite3 in the previous article, just using SQLAlchemy's named-parameter syntax instead.

An explicit caveat worth flagging clearly

This is genuinely important to understand precisely: pandas does not attempt to sanitize SQL statements itself — it simply forwards whatever query you give it directly to the underlying database driver. The responsibility for safe, injection-resistant query construction rests entirely on how you build the query string yourself, not on any safety net pandas provides on your behalf.

# DANGEROUS — never do this, even inside pandas
customer_id = "42"
df = pd.read_sql(f"SELECT * FROM orders WHERE customer_id = {customer_id}", engine)

# SAFE — parameterized, exactly as shown above
query = text("SELECT * FROM orders WHERE customer_id = :cust_id")
df = pd.read_sql(query, engine, params={"cust_id": customer_id})
A practical example
def get_customer_orders(customer_id, engine):
    query = text("SELECT * FROM orders WHERE customer_id = :cust_id")
    return pd.read_sql(query, engine, params={"cust_id": customer_id})

orders = get_customer_orders(42, engine)

Filtering a large orders table by a customer ID passed in as a genuine parameter, rather than concatenated directly into the query text, is exactly the pattern to reach for reflexively any time a value going into a query originates from outside your own hardcoded source code — user input, an API request, anything not fully under your own direct control.

Writing DataFrames Back to a Database with to_sql()

Basic usage
df.to_sql("employees_cleaned", engine, if_exists="fail")

df.to_sql() writes every row of a DataFrame to a database table. The name parameter (the first positional argument, "employees_cleaned" here) specifies the destination table, and if_exists controls what happens if that table already exists:

  • "fail" — the safe default; raises an error rather than risking accidental data loss or corruption.

  • "replace" — drops and recreates the table, fully overwriting whatever was there.

  • "append" — adds the new rows to the existing table, leaving what's already there untouched.

A practical example: loading cleaned data
cleaned_df = clean_employee_data(raw_df)   # tying back to the earlier data-cleaning article
cleaned_df.to_sql("employees_cleaned", engine, if_exists="replace", index=False)

verification = pd.read_sql_table("employees_cleaned", engine)
print(verification.head())

Loading a cleaned DataFrame — tying directly back to the earlier data-cleaning article's clean_employee_data() pipeline — into a fresh database table, then confirming the write actually succeeded by reading the same table straight back with read_sql_table(), is a genuinely good habit: don't just assume a write succeeded silently; verify it directly, especially the first time you run a new pipeline against a table that matters.

A common beginner trap
df.to_sql("employees", engine)   # writes the DataFrame's index as its own column too!

This genuinely catches people off guard: to_sql() defaults to also writing the DataFrame's index as its own separate column in the destination table — usually not what you actually want, since a DataFrame's default index is typically just a meaningless 0, 1, 2, ... sequence, carrying no real information worth persisting into the database.

df.to_sql("employees", engine, index=False)   # the usual, correct choice

Passing index=False is usually the right call, unless the index genuinely represents meaningful data actually worth preserving as its own column — say, if you'd previously set a genuinely meaningful ID as the DataFrame's index via .set_index(), covered in the earlier pandas data selection article.

Querying a DataFrame Directly with pandasql

A fundamentally different tool

Worth distinguishing clearly from everything covered so far: the pandasql library allows querying pandas DataFrames by running literal SQL commands, without connecting to any actual SQL server at all. It uses SQLite syntax internally, and automatically detects any DataFrame already sitting in your current Python session's memory, treating it as though it were a regular SQL table.

Basic usage
from pandasql import sqldf

df = pd.DataFrame({
    "name": ["Alex", "Sam", "Jordan"],
    "score": [85, 72, 91],
})

result = sqldf("SELECT * FROM df WHERE score > 80", globals())
print(result)

Passing globals() is what lets sqldf() find df — since it needs access to your current namespace to locate the DataFrame referenced by name in the query string. This is genuinely useful specifically for people who initially learned SQL before pandas, and who find certain filters, joins, or aggregations more directly readable as SQL text than as the equivalent chained pandas methods — a legitimate readability preference, not a lesser or "wrong" way to work.

Honest limitations worth closing on
pandasql only supports the Data Query Language subset of SQL — essentially just SELECT statements. It genuinely can't modify, insert, or delete data the way a real SQL database can, since there's no actual persistent database underneath it at all, just an in-memory translation layer over your existing DataFrame.

More significantly: pandasql can be several orders of magnitude slower than native pandas operations, since every query involves converting the DataFrame into a temporary SQLite table behind the scenes, running the query there, and converting the result back — real, non-trivial overhead compared to pandas' own native, in-memory vectorized operations covered throughout this series. This makes pandasql a genuinely good tool for learning or quick readability checks, but a poor choice for performance-sensitive production pipelines.

A modern alternative worth knowing about: DuckDB

If you genuinely want SQL-syntax querying against DataFrames with real performance, DuckDB has emerged as the considerably faster, more actively maintained modern alternative to pandasql:

import duckdb

result = duckdb.sql("SELECT * FROM df WHERE score > 80").df()
print(result)

DuckDB is an embedded, in-process SQL analytics engine, and it can query a pandas DataFrame directly by name, without any explicit registration step, running considerably faster than pandasql — often by a large margin — since it's a genuine, purpose-built columnar SQL engine rather than a thin translation layer bouncing data in and out of a temporary SQLite table on every call. For anything beyond quick, informal learning or readability purposes, DuckDB is generally the better choice today.

The practical recommendation

Use pandasql (or, for anything with real performance stakes, DuckDB) while you're genuinely still ramping up on pandas syntax, or when SQL text is demonstrably more readable for one specific, occasional query. Transition to native pandas methods — .loc[], .groupby(), .merge(), all covered in depth throughout the earlier pandas articles in this series — once you're comfortable with them, since native pandas operations remain both faster and more idiomatically integrated with the rest of a typical pandas-based workflow.

PREVIOUS