Reading from SQL databases in Data Analysis Python - Mini Project: Build & Apply

Reading from SQL databases

📖 Scenario: You work at a small bookstore. You have a database that stores information about books and their prices. You want to read this data into Python to analyze it.

🎯 Goal: Learn how to connect to a SQL database, run a query to get book data, and display the results in Python.

📋 What You'll Learn

Use the sqlite3 library to connect to a database

Create a table called books with columns title and price

Insert the exact book data given

Write a SQL query to select all books with price less than a set threshold

Print the query results

💡 Why This Matters

🌍 Real World

Reading data from SQL databases is common in many jobs where data is stored in databases. It helps you analyze and report data easily.

💼 Career

Data analysts and data scientists often need to extract data from SQL databases to perform analysis, build reports, or create visualizations.

1

Create the books table and insert data

Use sqlite3 to connect to an in-memory database. Create a table called books with columns title (text) and price (real). Insert these exact entries: ('Python Basics', 29.99), ('Data Science 101', 39.95), ('Machine Learning', 49.50).

Data Analysis Python

# Connect to SQLite and create books table
# Your code here

Need a hint?

Use sqlite3.connect(':memory:') to create a temporary database in memory.

Use cursor.execute to create the table and cursor.executemany to insert multiple rows.

2

Set the price threshold

Create a variable called max_price and set it to 40. This will be used to select books cheaper than this price.

Data Analysis Python

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE books (title TEXT, price REAL)')
cursor.executemany('INSERT INTO books VALUES (?, ?)', [
    ('Python Basics', 29.99),
    ('Data Science 101', 39.95),
    ('Machine Learning', 49.50)
])
conn.commit()

# Set max_price to 40
# Your code here

Need a hint?

Just create a variable named max_price and assign it the value 40.

3

Query books cheaper than max_price

Write a SQL query using cursor.execute to select all columns from books where price is less than max_price. Use a parameterized query with ? and pass (max_price,) as parameters. Fetch all results into a variable called cheap_books.

Data Analysis Python

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE books (title TEXT, price REAL)')
cursor.executemany('INSERT INTO books VALUES (?, ?)', [
    ('Python Basics', 29.99),
    ('Data Science 101', 39.95),
    ('Machine Learning', 49.50)
])
conn.commit()

max_price = 40

# Query books cheaper than max_price and store in cheap_books
# Your code here

Need a hint?

Use cursor.execute with a parameter ? and pass (max_price,) as a tuple.

Then use cursor.fetchall() to get all matching rows.

4

Print the books cheaper than max_price

Print the variable cheap_books to display the list of books with price less than max_price.

Data Analysis Python

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE books (title TEXT, price REAL)')
cursor.executemany('INSERT INTO books VALUES (?, ?)', [
    ('Python Basics', 29.99),
    ('Data Science 101', 39.95),
    ('Machine Learning', 49.50)
])
conn.commit()

max_price = 40

cursor.execute('SELECT * FROM books WHERE price < ?', (max_price,))
cheap_books = cursor.fetchall()

# Print the cheap_books list
# Your code here

Need a hint?

Use print(cheap_books) to show the list of books cheaper than max_price.