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

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

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

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

Hint

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

Practice

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

easy

A. To close the database connection

B. To create a new SQL database

C. To write data from a DataFrame back to the database

D. To run an SQL query and get the result as a DataFrame

Solution

1. Step 1: Understand the function purpose

   pandas.read_sql_query() runs an SQL query on a database connection and returns the result as a pandas DataFrame.

2. Step 2: Compare options with function use

   Creating databases, closing connections, or writing data are not done by this function.

3. Final Answer:

   To run an SQL query and get the result as a DataFrame -> Option D

4. Quick Check:

   read_sql_query() = runs query and returns DataFrame [OK]

Hint: Remember: read_sql_query fetches data as DataFrame [OK]

Common Mistakes:

• Thinking it creates or modifies the database
• Confusing it with connection closing functions
• Assuming it writes data back to the database

2. Which of the following is the correct way to open a connection to a SQLite database file named data.db using Python's sqlite3 module?

easy

A. conn = sqlite3.open('data.db')

B. conn = sqlite3.connect('data.db')

C. conn = sqlite3.start('data.db')

D. conn = sqlite3.create('data.db')

Solution

1. Step 1: Recall sqlite3 connection syntax

   The correct method to open a connection is sqlite3.connect() with the database filename as argument.

2. Step 2: Check other options

   Methods like open, start, or create do not exist in sqlite3 for opening connections.

3. Final Answer:

   conn = sqlite3.connect('data.db') -> Option B

4. Quick Check:

   sqlite3.connect() opens DB connection [OK]

Hint: Use connect() to open SQLite DB connections [OK]

Common Mistakes:

• Using non-existent methods like open() or start()
• Confusing connect() with create()
• Forgetting to pass the database filename as a string

3. Given the following code, what will be the output?

import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER, name TEXT)')
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
cursor.execute("INSERT INTO users VALUES (2, 'Bob')")
df = pd.read_sql_query('SELECT * FROM users WHERE id = 2', conn)
print(df)

medium

A. id name
0 2 Bob

B. id name
0 1 Alice

C. Empty DataFrame with columns [id, name]

D. SyntaxError due to SQL query

Solution

1. Step 1: Understand the SQL query

   The query selects rows from 'users' where id equals 2, so only Bob's row matches.

2. Step 2: Check DataFrame output

   The DataFrame will have one row with id=2 and name='Bob'.

3. Final Answer:

   id name 0 2 Bob -> Option A

4. Quick Check:

   SELECT * WHERE id=2 returns Bob [OK]

Hint: Filter by id=2 returns Bob's row only [OK]

Common Mistakes:

• Confusing id=1 with id=2
• Expecting all rows instead of filtered
• Thinking the query has syntax errors

4. What is wrong with this code snippet for reading data from a SQLite database?

import sqlite3
import pandas as pd
conn = sqlite3.connect('mydb.db')
df = pd.read_sql_query('SELECT * FROM sales', conn)
conn.close()
print(df.head())
print(conn.execute('SELECT * FROM sales'))

medium

A. Trying to execute a query after closing the connection causes an error

B. The SQL query syntax is incorrect

C. pandas.read_sql_query() cannot read from SQLite databases

D. The connection is never closed

Solution

1. Step 1: Identify connection usage

   The connection is closed with conn.close() before the last execute call.

2. Step 2: Understand error cause

   After closing, trying to run conn.execute() causes a runtime error because the connection is no longer open.

3. Final Answer:

   Trying to execute a query after closing the connection causes an error -> Option A

4. Quick Check:

   Closed connection cannot execute queries [OK]

Hint: Don't use connection after closing it [OK]

Common Mistakes:

• Assuming queries work after closing connection
• Thinking SQL syntax is wrong here
• Believing pandas can't read SQLite data

5. You want to read data from a SQL database and filter rows where the column age is greater than 30. Which of the following code snippets correctly does this and returns a pandas DataFrame?

hard

A. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', 'conn')

B. df = pd.read_sql_query('SELECT * FROM users WHERE age => 30', conn)

C. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn)

D. df = pd.read_sql_query('SELECT * FROM users WHERE age > "30"', conn)

Solution

1. Step 1: Check SQL syntax for filtering

   The correct SQL syntax for greater than is age > 30. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn) uses this correctly.

2. Step 2: Identify errors in other options

   df = pd.read_sql_query('SELECT * FROM users WHERE age => 30', conn) uses invalid operator =>. df = pd.read_sql_query('SELECT * FROM users WHERE age > "30"', conn) compares age to a string which may cause type issues. df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', 'conn') passes connection as a string, which is incorrect.

3. Final Answer:

   df = pd.read_sql_query('SELECT * FROM users WHERE age > 30', conn) -> Option C

4. Quick Check:

   Correct SQL and connection object needed [OK]

Hint: Use > operator and pass connection object, not string [OK]

Common Mistakes:

• Using => instead of >
• Comparing numeric column to string
• Passing connection as string instead of object