How to Use SQL with Python and Pandas

In practice, SQL and Python split the work. SQL is best at filtering, joining and summarizing large amounts of data right where it lives. Python and pandas take over after that, for modeling, statistics and plotting. The bridge between them is a single function, pd.read_sql.

Setting Up the Connection

You need three libraries. Install them once with pip install pandas sqlalchemy psycopg2-binary. SQLAlchemy creates the connection, and psycopg2 is the driver that talks to PostgreSQL.

Running a Query and Getting a DataFrame

Here is a rides table from a cab service, with one row per trip and columns city and fare. We want the number of rides and the average fare in each city.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://postgres:your_password@localhost:5432/cabs")

query = """
SELECT city, COUNT(*) AS rides, ROUND(AVG(fare), 1) AS avg_fare
FROM rides
GROUP BY city
ORDER BY avg_fare DESC;
"""

df = pd.read_sql(query, engine)
print(df)

Output:

     city  rides  avg_fare
0   Delhi      3     210.0
1  Mumbai      4     150.0
2    Pune      3     120.0

Code Explanation:

• create_engine builds a connection from a single address, made up of the user, password, host, port, and database name.
• The query is a normal SQL string. The grouping and averaging happen inside the database.
• pd.read_sql runs the query and returns the result as a DataFrame, using the query's column names.
• The numbers on the left are pandas’ row index, not part of the SQL result.

Writing Results Back and Staying Safe

To save a DataFrame as a new table, use df.to_sql("city_summary", engine, index=False). This is handy for storing cleaned or aggregated data.

When a query includes a value that comes from a user or a variable, pass it as a parameter instead of pasting it into the string. Parameters keep your code safe from SQL injection and avoid quoting mistakes.

One habit saves a lot of time. Let SQL do the heavy lifting, and bring only the rows and columns you need into pandas. Pulling a 10-million-row table into memory just to filter it there is slow and often crashes the notebook.