Pandas with SQL databases - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to read a SQL table into a pandas DataFrame.

Pandas

import pandas as pd
import sqlite3
conn = sqlite3.connect('example.db')
df = pd.read_sql_table([1], conn)

Drag options to blanks, or click blank then click option'

Aconn

B'users'

C'SELECT * FROM users'

Dusers

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the table name without quotes.

Passing a SQL query instead of a table name.

The read_sql_table function requires the table name as a string.

2fill in blank

medium

Complete the code to execute a SQL query and load the result into a DataFrame.

Pandas

query = 'SELECT * FROM sales WHERE amount > 100'
df = pd.read_sql_query([1], conn)

Drag options to blanks, or click blank then click option'

A'query'

Bconn

Cquery

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection object instead of the query string.

Passing the query string with extra quotes.

The read_sql_query function expects the SQL query string as the first argument.

3fill in blank

hard

Fix the error in the code to write a DataFrame to a SQL table.

Pandas

df.to_sql([1], conn, if_exists='replace', index=False)

Drag options to blanks, or click blank then click option'

Atable_name

Bconn

Cdf

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection object as the first argument.

Passing the DataFrame itself as the first argument.

The first argument to to_sql must be the table name as a string.

4fill in blank

hard

Fill both blanks to create a DataFrame from a SQL query and set the index column.

Pandas

df = pd.read_sql_query([1], conn, [2]='id')

Drag options to blanks, or click blank then click option'

A'SELECT * FROM employees'

Bindex_col

C'id'

D'conn'

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection as the first argument.

Using the wrong keyword for setting the index column.

The first argument is the SQL query string, and index_col sets the DataFrame index column.

5fill in blank

hard

Fill all three blanks to write a DataFrame to a SQL table with append mode and without the index.

Pandas

df.to_sql([1], conn, if_exists=[2], index=[3])

Drag options to blanks, or click blank then click option'

A'logs'

B'append'

CFalse

DTrue

Attempts:

3 left

💡 Hint

Common Mistakes

Using if_exists='replace' which deletes existing data.

Setting index=True which writes the index as a column.

Use the table name as a string, if_exists='append' to add data, and index=False to exclude the index.

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