Pandas with SQL databases - Mini Project: Build & Apply

Pandas with SQL databases

📖 Scenario: You work as a data analyst for a small bookstore. The store keeps its sales data in a SQL database. You want to use Python and pandas to analyze the sales data easily.First, you will create a small SQL database table with sales data. Then, you will use pandas to read the data from the database and analyze it.

🎯 Goal: Build a simple Python program that creates a SQL database table with sales data, reads the data into a pandas DataFrame, filters the sales for a specific book, and prints the filtered data.

📋 What You'll Learn

Create a SQLite database connection using pandas and sqlite3

Create a sales table with specific data

Read the sales data into a pandas DataFrame

Filter the DataFrame for sales of a specific book

Print the filtered DataFrame

💡 Why This Matters

🌍 Real World

Many companies store data in SQL databases. Using pandas with SQL lets you analyze data easily without manual exports.

💼 Career

Data analysts and data scientists often combine SQL and pandas to prepare and analyze data efficiently.

1

Create the sales data table in SQLite

Import the sqlite3 module and create a connection to an in-memory SQLite database called conn. Then create a cursor called cur. Use cur.execute() to create a table called sales with columns id (integer primary key), book (text), and quantity (integer). Insert these exact rows into the sales table: (1, 'Python Basics', 5), (2, 'Data Science 101', 3), (3, 'Python Basics', 2), (4, 'Machine Learning', 4). Commit the changes using conn.commit().

Pandas

import sqlite3

# Create connection and cursor
# Your code here

Need a hint?

Use sqlite3.connect(':memory:') to create an in-memory database. Use cur.execute() to run SQL commands.

2

Set up pandas and read the sales data

Import the pandas library as pd. Use pd.read_sql_query() with the SQL query "SELECT * FROM sales" and the connection conn to read the sales data into a DataFrame called df.

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''CREATE TABLE sales (id INTEGER PRIMARY KEY, book TEXT, quantity INTEGER)''')
cur.execute("INSERT INTO sales VALUES (1, 'Python Basics', 5)")
cur.execute("INSERT INTO sales VALUES (2, 'Data Science 101', 3)")
cur.execute("INSERT INTO sales VALUES (3, 'Python Basics', 2)")
cur.execute("INSERT INTO sales VALUES (4, 'Machine Learning', 4)")
conn.commit()

# Read sales data into DataFrame df
# Your code here

Need a hint?

Use pd.read_sql_query() with the SQL query and the connection to get a DataFrame.

3

Filter the DataFrame for 'Python Basics' sales

Create a new DataFrame called python_sales by filtering df to include only rows where the book column equals 'Python Basics'.

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''CREATE TABLE sales (id INTEGER PRIMARY KEY, book TEXT, quantity INTEGER)''')
cur.execute("INSERT INTO sales VALUES (1, 'Python Basics', 5)")
cur.execute("INSERT INTO sales VALUES (2, 'Data Science 101', 3)")
cur.execute("INSERT INTO sales VALUES (3, 'Python Basics', 2)")
cur.execute("INSERT INTO sales VALUES (4, 'Machine Learning', 4)")
conn.commit()

df = pd.read_sql_query("SELECT * FROM sales", conn)

# Filter df for book == 'Python Basics' into python_sales
# Your code here

Need a hint?

Use pandas filtering with df[df['book'] == 'Python Basics'] to get the rows you want.

4

Print the filtered sales DataFrame

Print the python_sales DataFrame to display the sales records for 'Python Basics'.

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''CREATE TABLE sales (id INTEGER PRIMARY KEY, book TEXT, quantity INTEGER)''')
cur.execute("INSERT INTO sales VALUES (1, 'Python Basics', 5)")
cur.execute("INSERT INTO sales VALUES (2, 'Data Science 101', 3)")
cur.execute("INSERT INTO sales VALUES (3, 'Python Basics', 2)")
cur.execute("INSERT INTO sales VALUES (4, 'Machine Learning', 4)")
conn.commit()

df = pd.read_sql_query("SELECT * FROM sales", conn)

python_sales = df[df['book'] == 'Python Basics']

# Print python_sales
# Your code here

Need a hint?

Use print(python_sales) to display the filtered DataFrame.