read_sql() takes either a raw SQL query string or a table name, plus a database connection object, commonly created with SQLAlchemy, or a DB-API connection like sqlite3, and returns the query's result set as a fully-formed DataFrame, with column names taken directly from the query's result columns. It's a convenient bridge between a relational database and pandas' data-analysis tools, letting you filter and join data at the database level with SQL before pulling only the data you actually need into memory.

1Understanding pd.read_sql()

💡

Always use parameterized queries when a read_sql() query includes variable, user-provided data — passing that data as separate params, not by formatting it directly into the SQL string, avoids SQL injection, the same rule that applies to raw database code.

editor.html

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
df = pd.read_sql("SELECT * FROM orders WHERE total > 100", conn)
print(df.shape)

localhost:3000

2Practical Example

Here is a real-world application of pd.read_sql() showing how it is used in production Pandas code.

editor.html

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
min_total = 100
df = pd.read_sql("SELECT * FROM orders WHERE total > ?", conn, params=(min_total,))
print(len(df))

localhost:3000

3Best Practices

Follow these guidelines when working with pd.read_sql():

1. Push filtering and joining logic into the SQL query itself when possible, rather than loading a whole table and filtering afterward in pandas, to reduce the amount of data transferred

2. Use parameterized queries, passing params separately, for any query built with variable data, to avoid SQL injection

3. Use a context-managed connection, or explicitly close it afterward, rather than leaving database connections open indefinitely

⚠️

Tip: Always use parameterized queries when a read_sql() query includes variable, user-provided data — passing that data as separate params, not by formatting it directly into the SQL string, avoids SQL injection, the same rule that applies to raw database code.

editor.html

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
df = pd.read_sql("SELECT * FROM orders WHERE total > 100", conn)
print(df.shape)

localhost:3000

Examples

Example 01Basic Usage

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
df = pd.read_sql("SELECT * FROM orders WHERE total > 100", conn)
print(df.shape)

Example 02Advanced Example

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
min_total = 100
df = pd.read_sql("SELECT * FROM orders WHERE total > ?", conn, params=(min_total,))
print(len(df))

Best Practices

Push filtering and joining logic into the SQL query itself when possible, rather than loading a whole table and filtering afterward in pandas, to reduce the amount of data transferred
Use parameterized queries, passing params separately, for any query built with variable data, to avoid SQL injection
Use a context-managed connection, or explicitly close it afterward, rather than leaving database connections open indefinitely

Interview Question

Why is it better to filter data with a WHERE clause in the SQL query passed to read_sql(), rather than loading the whole table and filtering the DataFrame afterward?▼

Hint: Think about where the filtering work happens and how much data has to move.

Filtering in the SQL query happens inside the database engine, which is optimized for exactly this kind of operation and can use indexes to avoid scanning every row, and it means only the rows that actually matter are ever transferred over the network and loaded into memory. Loading the entire table first and filtering it afterward in pandas wastes time and memory pulling rows across that will just be discarded immediately, which becomes especially costly as the table grows much larger than the filtered result you actually need.

Exercises

MediumPractice using pd.read_sql() in a real scenario.

View Solution

import pandas as pd
import sqlite3

conn = sqlite3.connect("shop.db")
df = pd.read_sql("SELECT * FROM orders WHERE total > 100", conn)
print(df.shape)

Frequently Asked Questions

Why is it better to filter data with a WHERE clause in the SQL query passed to read_sql(), rather than loading the whole table and filtering the DataFrame afterward?▼

Related Functions

pd-read-csvdf-to-sqlsqlite3-module