Using SQL in Pandas

1. Big Picture

pandas is excellent for data analysis in Python. SQL is excellent for asking structured questions from tables. When we combine them, we get a powerful workflow.

What you will learn

• How to create a pandas DataFrame.
• How to create a SQLite database using Python.
• How to save a DataFrame as a SQL table.
• How to run SQL queries from pandas.
• How to use SELECT, WHERE, ORDER BY, GROUP BY and JOIN.
• How to compare SQL queries with pandas commands.
• How to use SQL result data for model training.
• How to save query results and trained models.

2. Why Use SQL with Pandas?

pandas and SQL both work with tabular data, but they are useful in different ways.

Simple comparison

SQL asks questions like this:

SELECT name, marks
FROM students
WHERE marks >= 70;

pandas asks similar questions like this:

result = df[df["marks"] >= 70][["name", "marks"]]

3. Setup

For this lesson, we will use sqlite3, which is included with Python. We do not need to install a separate database server.

Import libraries

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import joblib

from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression

Explanation of each library

Install needed libraries

If these libraries are not installed on your local computer, use:

pip install pandas matplotlib scikit-learn joblib

You do not need to install sqlite3 separately because it usually comes with Python.

4. Creating Data and Saving It into SQL

First, we will create a pandas DataFrame. Then we will save that DataFrame into a SQLite database table.

4.1 Create a student DataFrame

import pandas as pd

students_data = {
    "student_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "name": ["Amit", "Ravi", "Sita", "Meena", "Kabir", "Anu", "Farhan", "Pooja"],
    "city": ["Varanasi", "Lucknow", "Varanasi", "Delhi", "Lucknow", "Delhi", "Varanasi", "Lucknow"],
    "hours": [1, 2, 3, 4, 5, 6, 7, 8],
    "attendance": [55, 60, 65, 70, 78, 85, 90, 95],
    "marks": [35, 42, 50, 58, 70, 80, 88, 96]
}

students_df = pd.DataFrame(students_data)

print(students_df)

4.2 Create a database connection

import sqlite3

connection = sqlite3.connect("school.db")

This creates a file named school.db. If the file already exists, Python connects to it. If it does not exist, Python creates it.

4.3 Save DataFrame into SQL table

students_df.to_sql(
    "students",
    connection,
    if_exists="replace",
    index=False
)

Explanation of to_sql()

Other values of if_exists

5. Reading SQL Data into Pandas

The most important pandas command for SQL is:

pd.read_sql_query()

It runs a SQL query and returns the result as a pandas DataFrame.

5.1 Read full table

query = "SELECT * FROM students"

result_df = pd.read_sql_query(query, connection)

print(result_df)

Explanation

5.2 Check the returned DataFrame

print(result_df.head())
print(result_df.info())
print(result_df.describe())

Once the SQL result comes into pandas, it behaves like any normal DataFrame.

6. SQL SELECT in Pandas

SELECT is used to choose columns from a SQL table.

6.1 Select all columns

query = """
SELECT *
FROM students
"""

df = pd.read_sql_query(query, connection)

print(df)

6.2 Select specific columns

query = """
SELECT name, city, marks
FROM students
"""

df = pd.read_sql_query(query, connection)

print(df)

6.3 Rename output columns using alias

query = """
SELECT
    name AS student_name,
    marks AS final_marks
FROM students
"""

df = pd.read_sql_query(query, connection)

print(df)

AS gives a temporary name to a column in the output.

6.4 Create calculated columns

query = """
SELECT
    name,
    marks,
    marks + 5 AS marks_after_bonus
FROM students
"""

df = pd.read_sql_query(query, connection)

print(df)

SQL can calculate new columns during query time.

7. SQL WHERE in Pandas

WHERE is used to filter rows.

7.1 Students with marks 70 or above

query = """
SELECT name, city, marks
FROM students
WHERE marks >= 70
"""

df = pd.read_sql_query(query, connection)

print(df)

7.2 Students from Lucknow

query = """
SELECT *
FROM students
WHERE city = 'Lucknow'
"""

df = pd.read_sql_query(query, connection)

print(df)

7.3 Multiple conditions with AND

query = """
SELECT name, hours, attendance, marks
FROM students
WHERE hours >= 5 AND attendance >= 80
"""

df = pd.read_sql_query(query, connection)

print(df)

7.4 Multiple conditions with OR

query = """
SELECT name, city, marks
FROM students
WHERE city = 'Delhi' OR marks >= 90
"""

df = pd.read_sql_query(query, connection)

print(df)

7.5 Sorting with ORDER BY

query = """
SELECT name, city, marks
FROM students
ORDER BY marks DESC
"""

df = pd.read_sql_query(query, connection)

print(df)

8. SQL GROUP BY in Pandas

GROUP BY is used when we want summary results. For example: average marks by city.

8.1 Average marks by city

query = """
SELECT
    city,
    AVG(marks) AS average_marks
FROM students
GROUP BY city
"""

df = pd.read_sql_query(query, connection)

print(df)

8.2 Count students city by city

query = """
SELECT
    city,
    COUNT(*) AS total_students
FROM students
GROUP BY city
"""

df = pd.read_sql_query(query, connection)

print(df)

8.3 Minimum and maximum marks by city

query = """
SELECT
    city,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students
GROUP BY city
"""

df = pd.read_sql_query(query, connection)

print(df)

8.4 Filtering groups with HAVING

query = """
SELECT
    city,
    AVG(marks) AS average_marks
FROM students
GROUP BY city
HAVING AVG(marks) >= 70
"""

df = pd.read_sql_query(query, connection)

print(df)

9. SQL JOIN in Pandas

A JOIN combines data from two tables. This is one of the most powerful parts of SQL.

9.1 Create another DataFrame

fees_data = {
    "student_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "fees_paid": [5000, 4500, 5000, 4000, 3000, 5000, 4500, 5000],
    "scholarship": [0, 500, 0, 1000, 1500, 0, 500, 0]
}

fees_df = pd.DataFrame(fees_data)

fees_df.to_sql(
    "fees",
    connection,
    if_exists="replace",
    index=False
)

Now we have two tables:

• students
• fees

9.2 INNER JOIN

query = """
SELECT
    students.student_id,
    students.name,
    students.city,
    students.marks,
    fees.fees_paid,
    fees.scholarship
FROM students
INNER JOIN fees
ON students.student_id = fees.student_id
"""

df = pd.read_sql_query(query, connection)

print(df)

Explanation

9.3 JOIN with condition

query = """
SELECT
    students.name,
    students.city,
    students.marks,
    fees.fees_paid,
    fees.scholarship
FROM students
INNER JOIN fees
ON students.student_id = fees.student_id
WHERE students.marks >= 70
"""

df = pd.read_sql_query(query, connection)

print(df)

10. Same Task: Pandas vs SQL

Let us compare common operations in pandas and SQL.

When should we use SQL?

• When data is already inside a database.
• When we want to fetch only selected rows and columns.
• When tables need to be joined.
• When the dataset is too large to load fully into pandas.

When should we use pandas?

• When data is already in a DataFrame.
• When we need cleaning and transformation.
• When we need charts.
• When we need to prepare data for machine learning.

11. SQL Command to Pandas Statement Reference

This is the practical dictionary of SQL commands and their pandas equivalents. The SQL version is useful when data is inside a database. The pandas version is useful when data is already inside a DataFrame.

11.1 Reading and selecting data

11.2 Filtering rows

11.3 Sorting and calculated columns

11.4 Aggregation and grouping

11.5 Joins and set operations

11.6 Insert, update, delete and table commands

11.7 Subqueries, Common Table Expressions and window ideas

11.8 Running the same idea directly through SQL from pandas

Sometimes the best pandas statement is not a DataFrame operation. It is simply pd.read_sql_query() with the SQL command inside it.

query = """
SELECT city, AVG(marks) AS average_marks
FROM students
GROUP BY city
HAVING AVG(marks) >= 70
ORDER BY average_marks DESC
"""

result_df = pd.read_sql_query(query, connection)
print(result_df)

12. Using SQL Query Result for Model Training

Now we will use SQL to pull the columns needed for training. Then pandas will prepare the data for the model.

11.1 Pull training data using SQL

query = """
SELECT
    hours,
    attendance,
    marks
FROM students
"""

training_df = pd.read_sql_query(query, connection)

print(training_df)

11.2 Prepare input and output

X = training_df[["hours", "attendance"]]
y = training_df["marks"]

11.3 Train-test split

X_train, X_test, y_train, y_test = train_test_split(
    X,
    y,
    test_size=0.25,
    random_state=42
)

11.4 Train Linear Regression model

model = LinearRegression()

model.fit(X_train, y_train)

11.5 Check score

score = model.score(X_test, y_test)

print("Model score:", score)

11.6 Make prediction

new_student = [[7, 92]]

prediction = model.predict(new_student)

print("Predicted marks:", prediction[0])

13. Saving SQL Results, DataFrames and Models

After querying and training, we may want to save the result in different forms.

12.1 Save SQL query result as CSV

query = """
SELECT name, city, marks
FROM students
WHERE marks >= 70
ORDER BY marks DESC
"""

top_students_df = pd.read_sql_query(query, connection)

top_students_df.to_csv("top_students.csv", index=False)

12.2 Save SQL query result back into another SQL table

top_students_df.to_sql(
    "top_students",
    connection,
    if_exists="replace",
    index=False
)

12.3 Save trained model

joblib.dump(model, "marks_model.pkl")

12.4 Load trained model

loaded_model = joblib.load("marks_model.pkl")

prediction = loaded_model.predict([[8, 95]])

print(prediction[0])

12.5 Close database connection

connection.close()

Closing the connection is a good habit. It tells Python that we are done using the database.

14. Complete Mini Project

This full project creates data, saves it to SQL, queries it, joins tables, trains a model, creates predictions and saves everything.

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import joblib

from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression

# -------------------------------------------------
# 1. CREATE DATAFRAMES
# -------------------------------------------------

students_data = {
    "student_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "name": ["Amit", "Ravi", "Sita", "Meena", "Kabir", "Anu", "Farhan", "Pooja"],
    "city": ["Varanasi", "Lucknow", "Varanasi", "Delhi", "Lucknow", "Delhi", "Varanasi", "Lucknow"],
    "hours": [1, 2, 3, 4, 5, 6, 7, 8],
    "attendance": [55, 60, 65, 70, 78, 85, 90, 95],
    "marks": [35, 42, 50, 58, 70, 80, 88, 96]
}

fees_data = {
    "student_id": [1, 2, 3, 4, 5, 6, 7, 8],
    "fees_paid": [5000, 4500, 5000, 4000, 3000, 5000, 4500, 5000],
    "scholarship": [0, 500, 0, 1000, 1500, 0, 500, 0]
}

students_df = pd.DataFrame(students_data)
fees_df = pd.DataFrame(fees_data)

print("Students DataFrame")
print(students_df)

print("Fees DataFrame")
print(fees_df)

# -------------------------------------------------
# 2. CREATE SQLITE DATABASE
# -------------------------------------------------

connection = sqlite3.connect("school.db")

students_df.to_sql(
    "students",
    connection,
    if_exists="replace",
    index=False
)

fees_df.to_sql(
    "fees",
    connection,
    if_exists="replace",
    index=False
)

print("Tables saved into SQLite database.")

# -------------------------------------------------
# 3. BASIC SQL QUERY
# -------------------------------------------------

query = """
SELECT *
FROM students
"""

all_students = pd.read_sql_query(query, connection)

print("All students from SQL")
print(all_students)

# -------------------------------------------------
# 4. FILTERING WITH SQL
# -------------------------------------------------

query = """
SELECT name, city, hours, attendance, marks
FROM students
WHERE marks >= 70
ORDER BY marks DESC
"""

top_students = pd.read_sql_query(query, connection)

print("Top students")
print(top_students)

# -------------------------------------------------
# 5. GROUPING WITH SQL
# -------------------------------------------------

query = """
SELECT
    city,
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks,
    MAX(marks) AS highest_marks
FROM students
GROUP BY city
ORDER BY average_marks DESC
"""

city_summary = pd.read_sql_query(query, connection)

print("City summary")
print(city_summary)

# -------------------------------------------------
# 6. JOINING TWO TABLES
# -------------------------------------------------

query = """
SELECT
    students.student_id,
    students.name,
    students.city,
    students.hours,
    students.attendance,
    students.marks,
    fees.fees_paid,
    fees.scholarship
FROM students
INNER JOIN fees
ON students.student_id = fees.student_id
"""

joined_df = pd.read_sql_query(query, connection)

print("Joined data")
print(joined_df)

# -------------------------------------------------
# 7. TRAINING DATA FROM SQL
# -------------------------------------------------

query = """
SELECT
    hours,
    attendance,
    marks
FROM students
"""

training_df = pd.read_sql_query(query, connection)

X = training_df[["hours", "attendance"]]
y = training_df["marks"]

X_train, X_test, y_train, y_test = train_test_split(
    X,
    y,
    test_size=0.25,
    random_state=42
)

model = LinearRegression()

model.fit(X_train, y_train)

score = model.score(X_test, y_test)

print("Model score:", score)

# -------------------------------------------------
# 8. PRESENTATION
# -------------------------------------------------

training_df["predicted_marks"] = model.predict(X)

print("Training data with predictions")
print(training_df)

plt.scatter(training_df["hours"], training_df["marks"], label="Actual Marks")
plt.plot(training_df["hours"], training_df["predicted_marks"], label="Predicted Marks")

plt.xlabel("Study Hours")
plt.ylabel("Marks")
plt.title("Actual vs Predicted Marks")
plt.legend()
plt.show()

# -------------------------------------------------
# 9. SAVE RESULTS
# -------------------------------------------------

top_students.to_csv("top_students.csv", index=False)
city_summary.to_csv("city_summary.csv", index=False)
joined_df.to_csv("joined_student_fees.csv", index=False)
training_df.to_csv("training_with_predictions.csv", index=False)

training_df.to_sql(
    "training_with_predictions",
    connection,
    if_exists="replace",
    index=False
)

joblib.dump(model, "marks_model.pkl")

print("CSV files, SQL table and model saved successfully.")

# -------------------------------------------------
# 10. CLOSE DATABASE
# -------------------------------------------------

connection.close()

print("Database connection closed.")

15. Important Features Explained

sqlite3.connect()

connection = sqlite3.connect("school.db")

Creates a database file or connects to an existing database file.

df.to_sql()

df.to_sql("table_name", connection, if_exists="replace", index=False)

Saves a pandas DataFrame as a SQL table.

pd.read_sql_query()

df = pd.read_sql_query(query, connection)

Runs a SQL query and returns the result as a pandas DataFrame.

Triple-quoted SQL string

query = """
SELECT name, marks
FROM students
WHERE marks >= 70
"""

Triple quotes allow us to write multi-line SQL queries clearly.

connection.close()

connection.close()

Closes the database connection after work is finished.

index=False

Prevents pandas from saving its automatic index as a separate column.

if_exists="replace"

Replaces old SQL table with a new one. Useful during practice.

if_exists="append"

Adds new rows to an existing SQL table.

16. Practice in Our Python Editor

Use the embedded Python editor below to test pandas and SQL examples from this lesson.

17. Practice Tasks

1. Create a DataFrame named products_df.
2. Add columns: product_id, product_name, category, price, units_sold.
3. Create a SQLite database named shop.db.
4. Save the DataFrame as a SQL table named products.
5. Write a SQL query to select all products.
6. Write a SQL query to find products where price is greater than 500.
7. Write a SQL query to find total units sold by category.
8. Write a SQL query to find average price by category.
9. Bring the result into pandas using pd.read_sql_query().
10. Save the final result as product_summary.csv.

Self Test

18. Final Summary

DataFrame
   ↓
SQLite database
   ↓
to_sql()
   ↓
SQL query
   ↓
read_sql_query()
   ↓
pandas DataFrame
   ↓
analysis, chart, training, saving