Reading from SQL databases in Data Analysis Python - Step-by-Step Execution

Concept Flow - Reading from SQL databases

Connect to SQL database

↓

Write SQL query string

↓

Send query to database

↓

Database executes query

↓

Fetch results into DataFrame

↓

Use DataFrame for analysis

This flow shows how Python connects to a SQL database, sends a query, gets results, and loads them into a DataFrame for analysis.

Execution Sample

Data Analysis Python

import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
query = 'SELECT 1 AS number'
df = pd.read_sql_query(query, conn)
print(df)

This code connects to a temporary in-memory SQL database, runs a simple query, and loads the result into a DataFrame.

Execution Table

Step	Action	Evaluation	Result
1	Import sqlite3 and pandas	Modules loaded	sqlite3 and pandas ready
2	Connect to in-memory database	sqlite3.connect(':memory:')	Connection object created
3	Define SQL query string	query = 'SELECT 1 AS number'	Query string stored
4	Execute query with pd.read_sql_query	pd.read_sql_query(query, conn)	DataFrame with one row and column 'number' = 1
5	Print DataFrame	print(df)	Output: number 0 1

💡 Query executed and results loaded into DataFrame; program ends after printing.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
conn	None	Connection object	Connection object	Connection object	Connection object
query	None	None	'SELECT 1 AS number'	'SELECT 1 AS number'	'SELECT 1 AS number'
df	None	None	None	DataFrame with number=1	DataFrame with number=1

Key Moments - 3 Insights

Why do we need to create a connection object before running the query?

The connection object represents the link to the database. Without it, Python cannot send the SQL query to the database. See execution_table step 2 where the connection is created before the query runs in step 4.

What does pd.read_sql_query do with the SQL query and connection?

It sends the SQL query through the connection to the database, executes it, and fetches the results into a pandas DataFrame. This is shown in execution_table step 4.

Why is the result stored in a DataFrame?

DataFrames are easy to analyze and manipulate in Python. The SQL query results become tabular data inside the DataFrame, as shown in execution_table step 4 and 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the value of 'df' after step 4?

A The SQL query string

B A DataFrame with one row and column 'number' = 1

C None

D A connection object

💡 Hint

Check the 'Result' column in row for step 4 in the execution_table.

At which step is the SQL query string defined?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Action' and 'Evaluation' columns in execution_table to find when query is assigned.

If we skip creating the connection object, what will happen when running pd.read_sql_query?

A It will raise an error because no connection is provided

B It will run successfully with no errors

C It will return an empty DataFrame

D It will create a new connection automatically

💡 Hint

Refer to key_moments about the importance of the connection object before running queries.

Concept Snapshot

Reading from SQL databases in Python:
1. Import database and pandas modules.
2. Create a connection to the database.
3. Write your SQL query as a string.
4. Use pd.read_sql_query(query, connection) to run query.
5. Results load into a pandas DataFrame for easy analysis.

Full Transcript

This lesson shows how to read data from SQL databases using Python. First, you import the needed modules sqlite3 and pandas. Then, you create a connection object to the database. Next, you write your SQL query as a string. Using pandas' read_sql_query function, you send the query through the connection to the database. The database runs the query and returns the results. These results are loaded into a pandas DataFrame, which you can use for data analysis. The example uses an in-memory SQLite database and a simple query that returns the number 1. The execution table traces each step, showing how variables change and how the DataFrame is created. Key moments clarify why the connection is needed and how the query results become a DataFrame. The quiz tests understanding of these steps. This process is common in data science to get data from databases into Python for analysis.

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