Pandas with SQL databases - Step-by-Step Execution

Concept Flow - Pandas with SQL databases

Connect to SQL database

↓

Write SQL query or table name

↓

Use pandas.read_sql() to get data

↓

Data loaded into DataFrame

↓

Analyze or modify DataFrame

↓

Optionally write DataFrame back to SQL with to_sql()

↓

Close connection

This flow shows how pandas connects to a SQL database, reads data into a DataFrame, allows analysis, and optionally writes data back.

Execution Sample

Pandas

import pandas as pd
import sqlite3
conn = sqlite3.connect(':memory:')

# Create table and insert data
conn.execute('CREATE TABLE sales (id INTEGER, amount INTEGER)')
conn.execute('INSERT INTO sales VALUES (1, 100), (2, 200)')

# Read data into DataFrame
df = pd.read_sql('SELECT * FROM sales', conn)
print(df)

# Close connection
conn.close()

This code creates an in-memory SQL database, adds a table with data, reads it into a pandas DataFrame, prints it, and closes the connection.

Execution Table

Step	Action	SQL Command / pandas Function	Result / Output
1	Connect to in-memory SQLite database	sqlite3.connect(':memory:')	Connection object created
2	Create table 'sales'	CREATE TABLE sales (id INTEGER, amount INTEGER)	Table 'sales' created
3	Insert two rows into 'sales'	INSERT INTO sales VALUES (1, 100), (2, 200)	2 rows inserted
4	Read all rows from 'sales' into DataFrame	pd.read_sql('SELECT * FROM sales', conn)	DataFrame with 2 rows and columns 'id', 'amount'
5	Print DataFrame	print(df)	id amount 0 1 100 1 2 200
6	Close connection	conn.close()	Connection closed

💡 All steps completed successfully; data loaded and displayed.

Variable Tracker

Variable	Start	After Step 4	After Step 5	Final
conn	None	Connection object	Connection object	Closed
df	None	DataFrame with 2 rows	Same DataFrame	Same DataFrame

Key Moments - 2 Insights

Why do we use pd.read_sql() instead of just executing SQL commands?

pd.read_sql() runs the SQL query and directly returns the results as a pandas DataFrame, making it easy to analyze data in Python. See execution_table step 4 where the DataFrame is created.

What happens if we forget to close the database connection?

The connection stays open, which can cause resource leaks. Step 6 shows closing the connection to free resources.

Visual Quiz - 3 Questions

Look at the execution table, what does the DataFrame 'df' contain after step 4?

A A DataFrame with 2 rows and columns 'id' and 'amount'

B An empty DataFrame

C A list of SQL commands

D A connection object

💡 Hint

Check the 'Result / Output' column in step 4 of the execution_table.

At which step is the SQL table 'sales' created?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' and 'SQL Command' columns in the execution_table.

If we skip step 6 (closing connection), what is the likely effect?

A SQL commands won't run

B Resources may not be freed properly

C DataFrame will be empty

D DataFrame will have wrong data

💡 Hint

Refer to key_moments about connection closing.

Concept Snapshot

Pandas with SQL databases:
- Use sqlite3 or other DB connectors to connect
- Use pd.read_sql(query, connection) to load data into DataFrame
- Analyze or modify DataFrame as usual
- Use df.to_sql() to write back to DB
- Always close the connection when done

Full Transcript

This example shows how to connect pandas to a SQL database using sqlite3. First, we connect to an in-memory database. Then, we create a table and insert data using SQL commands. Next, we use pandas' read_sql function to run a SQL query and load the results into a DataFrame. We print the DataFrame to see the data. Finally, we close the database connection to free resources. This process helps us work with SQL data easily inside pandas for analysis.

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