Pandas with SQL databases - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this pandas SQL query?

Given a SQLite database connection, what will be the output of the following code snippet?

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE sales (id INTEGER, amount INTEGER)')
conn.execute('INSERT INTO sales VALUES (1, 100), (2, 200), (3, 300)')
df = pd.read_sql_query('SELECT * FROM sales WHERE amount > 150', conn)
print(df)

A

Empty DataFrame
Columns: [id, amount]
Index: []

B

   id  amount
0   1     100
1   2     200
2   3     300

C

   id  amount
0   2     200
1   3     300

D

   id  amount
0   1     100

💡 Hint

Look at the SQL WHERE clause filtering amounts greater than 150.

The SQL query selects rows where amount is greater than 150, so only rows with amounts 200 and 300 are included.

❓ data_output

intermediate

How many rows does this DataFrame have after reading from SQL?

Consider this code that reads data from a SQL table into a pandas DataFrame. How many rows will the DataFrame contain?

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE employees (name TEXT, age INTEGER)')
conn.execute("INSERT INTO employees VALUES ('Alice', 30), ('Bob', 25), ('Charlie', 35)")
df = pd.read_sql_query('SELECT * FROM employees WHERE age >= 30', conn)
print(len(df))

A 2

B 3

C 1

D 0

💡 Hint

Check which employees have age 30 or more.

Only Alice (30) and Charlie (35) meet the condition age >= 30, so the DataFrame has 2 rows.

🔧 Debug

advanced

What error does this code raise when reading SQL into pandas?

This code tries to read data from a SQL database into a pandas DataFrame. What error will it raise?

Pandas

import pandas as pd

query = 'SELECT * FROM orders'
df = pd.read_sql_query(query, 'not_a_connection')

A AttributeError: 'str' object has no attribute 'execute'

B TypeError: argument 2 must be a sqlite3.Connection or SQLAlchemy connectable

C NameError: name 'orders' is not defined

D ValueError: SQL query is empty

💡 Hint

The second argument to read_sql_query must be a connection object, not a string.

Passing a string instead of a connection causes pandas to try calling execute on a string, which fails with AttributeError.

🚀 Application

advanced

Which option correctly writes a DataFrame to a SQL table?

You have a pandas DataFrame df and a SQLite connection conn. Which code snippet correctly writes df to a SQL table named 'products'?

Pandas

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
df = pd.DataFrame({'product': ['A', 'B'], 'price': [10, 20]})

A conn.to_sql(df, 'products', if_exists='replace')

B df.to_sql('products', conn, if_exists='replace', index=False)

C pd.to_sql(df, 'products', conn)

D df.write_sql('products', conn)

💡 Hint

Check the pandas DataFrame method to_sql and its parameters.

The correct method is df.to_sql with table name, connection, and optional parameters like if_exists and index.

🧠 Conceptual

expert

Why use pandas with SQL databases in data science?

Which of the following is the best reason to use pandas with SQL databases in data science workflows?

A To perform fast in-memory computations on large datasets without any disk I/O

B To avoid learning SQL by using pandas methods only

C To replace SQL databases entirely with pandas DataFrames for all data storage needs

D To combine the power of SQL for data retrieval with pandas for flexible data manipulation and analysis

💡 Hint

Think about the strengths of SQL and pandas and how they complement each other.

SQL is great for querying and filtering data efficiently in databases, while pandas offers powerful tools for data analysis once data is loaded.