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.