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

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

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

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

Hint

Use print(python_sales) to display the filtered DataFrame.

Practice

1. What is the main purpose of using pandas.read_sql() when working with SQL databases?

easy

A. To read data from a SQL database into a pandas DataFrame

B. To write a pandas DataFrame into a SQL database

C. To create a new SQL database

D. To delete tables from a SQL database

Solution

1. Step 1: Understand the function purpose

   pandas.read_sql() is designed to read data from a SQL query or table.

2. Step 2: Match function to action

   Reading data means loading it into a pandas DataFrame for analysis.

3. Final Answer:

   To read data from a SQL database into a pandas DataFrame -> Option A

4. Quick Check:

   read_sql() = read data [OK]

Hint: read_sql reads data into DataFrame fast [OK]

Common Mistakes:

• Confusing read_sql with to_sql
• Thinking it creates or deletes databases
• Assuming it writes data instead of reading

2. Which of the following is the correct way to create a connection engine to a SQLite database named data.db using SQLAlchemy for pandas?

easy

A. engine = create_engine('sqlite://data.db')

B. engine = create_engine('sqlite:///data.db')

C. engine = create_engine('sqlite:data.db')

D. engine = create_engine('sqlite:/data.db')

Solution

1. Step 1: Recall SQLite connection string format

   SQLite uses the format 'sqlite:///' followed by the database file path.

2. Step 2: Match correct syntax

   engine = create_engine('sqlite:///data.db') uses 'sqlite:///data.db' which is the correct format for a local file.

3. Final Answer:

   engine = create_engine('sqlite:///data.db') -> Option B

4. Quick Check:

   SQLite engine needs three slashes [OK]

Hint: SQLite needs three slashes in connection string [OK]

Common Mistakes:

• Using two slashes instead of three
• Missing slashes or colon
• Confusing with other database connection formats

3. Given the following code, what will be the output of print(df.shape)?

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db')
df = pd.read_sql('SELECT * FROM users WHERE age > 30', engine)

medium

A. (0, 0) because no data is returned

B. (total_rows_in_users_table, number_of_columns_in_users_table)

C. An error because of incorrect SQL syntax

D. (number_of_rows_with_age_greater_than_30, number_of_columns_in_users_table)

Solution

1. Step 1: Understand the SQL query

   The query selects all columns from 'users' where age is greater than 30, so only matching rows are returned.

2. Step 2: Understand pandas DataFrame shape

   The DataFrame shape is (rows, columns). Rows = filtered rows, columns = all columns from 'users'.

3. Final Answer:

   (number_of_rows_with_age_greater_than_30, number_of_columns_in_users_table) -> Option D

4. Quick Check:

   Filtered rows and all columns = shape [OK]

Hint: DataFrame shape = filtered rows, all columns [OK]

Common Mistakes:

• Assuming all rows are returned
• Thinking shape returns data content
• Confusing SQL syntax with pandas syntax

4. What is wrong with this code snippet that tries to write a DataFrame df to a SQL table named sales?

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db')
df.to_sql('sales', engine)

medium

A. Missing the argument if_exists to handle existing tables

B. The engine connection string is incorrect

C. to_sql cannot write to SQLite databases

D. DataFrame df must be converted to a list first

Solution

1. Step 1: Check to_sql default behavior

   By default, to_sql will raise an error if the table exists unless if_exists is set.

2. Step 2: Identify missing argument

   The code lacks if_exists='replace' or if_exists='append' to avoid errors on existing tables.

3. Final Answer:

   Missing the argument if_exists to handle existing tables -> Option A

4. Quick Check:

   to_sql needs if_exists for existing tables [OK]

Hint: Add if_exists='replace' or 'append' to avoid errors [OK]

Common Mistakes:

• Assuming to_sql always overwrites
• Thinking engine string is wrong
• Believing DataFrame must convert before writing

5. You want to load data from a SQL database into pandas, filter rows where status = 'active', and then write only these filtered rows back to a new SQL table named active_users. Which sequence of commands correctly achieves this?

hard

A. df = pd.read_sql('SELECT * FROM users WHERE status = "active"', engine)
df.to_sql('active_users', engine, if_exists='append')

B. df = pd.read_sql('SELECT * FROM users WHERE status = active', engine)
df.to_sql('active_users', engine)

C. df = pd.read_sql('SELECT * FROM users', engine)
df_filtered = df[df['status'] == 'active']
df_filtered.to_sql('active_users', engine, if_exists='replace')

D. df = pd.read_sql('SELECT * FROM users', engine)
df_filtered = df[df.status = 'active']
df_filtered.to_sql('active_users', engine)

Solution

1. Step 1: Read all data and filter in pandas

   df = pd.read_sql('SELECT * FROM users', engine) df_filtered = df[df['status'] == 'active'] df_filtered.to_sql('active_users', engine, if_exists='replace') reads all users, then filters rows where status equals 'active' using correct pandas syntax.

2. Step 2: Write filtered data with proper if_exists argument

   df = pd.read_sql('SELECT * FROM users', engine) df_filtered = df[df['status'] == 'active'] df_filtered.to_sql('active_users', engine, if_exists='replace') writes filtered data to 'active_users' table, replacing if it exists, avoiding errors.

3. Final Answer:

   df = pd.read_sql('SELECT * FROM users', engine) df_filtered = df[df['status'] == 'active'] df_filtered.to_sql('active_users', engine, if_exists='replace') -> Option C

4. Quick Check:

   Read all, filter in pandas, write with if_exists [OK]

Hint: Filter in pandas, use if_exists='replace' when writing [OK]

Common Mistakes:

• Incorrect SQL syntax in WHERE clause
• Using single equals (=) in pandas filter
• Missing if_exists argument causing errors