SQL with Python (SQLite, MySQL, PostgreSQL) - Programming for Machine Learning and Business

Querying Relational Data from Python Workflows¶

Notebook Guide¶

This notebook introduces relational access from Python with a runnable SQLite example.

Learning objectives¶

open a database connection
create a simple table
insert rows and query them back
understand how the same workflow generalizes to MySQL and PostgreSQL with different drivers

Guided Example¶

The code cell below uses SQLite because it ships with Python and runs locally without extra setup. The same high-level flow applies to other relational databases:

connect to the database
create or access a table
insert records
query results for analysis

import sqlite3
import pandas as pd

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("CREATE TABLE sales (product TEXT, units INTEGER, price REAL)")
cursor.executemany(
    "INSERT INTO sales VALUES (?, ?, ?)",
    [("Notebook", 12, 5.5), ("Pen", 30, 1.2), ("Marker", 10, 2.0)],
)
connection.commit()

query = "SELECT product, units, price, units * price AS revenue FROM sales"
result = pd.read_sql_query(query, connection)
print(result)

connection.close()

    product  units  price  revenue
0  Notebook     12    5.5     66.0
1       Pen     30    1.2     36.0
2    Marker     10    2.0     20.0

Exercises¶

Exercise¶

Wrap-Up¶

Relational databases are strongest when data has clear structure, keys, and join relationships. After you understand the SQLite pattern here, moving to MySQL or PostgreSQL mainly changes the connection layer, not the analytical logic.

8. Interactive Code¶

9. Guided Practice¶

Why combine SQL with Python?¶

To avoid all data retrievalSQL and Python are often used together for retrieval and processing.

To query stored data and then process it programmaticallyCorrect. SQL retrieves data and Python can analyze it further.

Because SQL replaces Python syntaxThey solve different parts of the workflow.

Because SQL cannot store rowsSQL databases are built around structured rows and tables.

What is the average revenue returned in the example?¶

1200.0That is one row value, not the average.

1350.0Correct. The average of 1200 and 1500 is 1350.

1500.0That is the maximum, not the average.

2700.0That would be the total, not the average.