Enterprise Database Connectivity: PostgreSQL, MySQL & SQLAlchemy

From CSV Files to Live Databases: Connecting Python to Production Data You have spent the last two lessons loading data with pd.read_csv("sales.csv") . That works in a classroom. On your first real job you will hear this instead: "The orders are in the Postgres warehouse — pull the last 90 days into Python and send me the RFM." No CSV was attached. No file to download. The table lives on a server and you must query it into a DataFrame, analyze it, and write your results back . This article makes that jump concrete — with the same e-commerce data you will use in the notebook and Project 35.

1. Why Your Notebook Needs a Database Connection Think of a database as a shared Excel file that 200 people and 15 services write to at the same time — but it enforces types, prevents corruption, and answers questions in milliseconds. Your Python notebook is the client that asks questions (SQL) and gets answers (rows) back as a DataFrame.

You want to… Old CSV way Production way (this article) Load orders pd.read_csv("orders.csv") pd.read_sql_query("SELECT * FROM orders", engine) Filter completed only Load everything, then df[df.status=="completed"] Filter inside the DB: WHERE status='completed' — less RAM, faster Save your RFM result df.to_csv("rfm.csv") on your laptop df.to_sql("rfm_scores", engine, schema="marketing_analytics") — visible to the whole team

2. The Two Layers Between Python and the Database Every Python DB connection has two parts. You already used Layer 1 with SQLite — now we add Layer 2 for everything else:

Layer What it does Examples Analogy 1 — DB-API Driver Speaks the database's native network protocol (TCP, auth, wire format) sqlite3 (built-in), psycopg2 (Postgres), pymysql (MySQL) The phone that dials the warehouse 2 — SQLAlchemy Engine Manages connection pooling, translates dialects, plugs directly into pd.read_sql / to_sql create_engine("postgresql+psycopg2://...") The switchboard that pools calls and translates languages For SQLite the driver is built into Python ( import sqlite3 ). For Postgres/MySQL you install the driver once with pip install psycopg2-binary or pymysql and SQLAlchemy handles the rest.

3. Your Starting Point: The Same Orders Table — Now Inside Postgres In the notebook you saw this table in SQLite :memory: . In production it lives at prod-db.company.com in the table orders . Same columns, same 280 rows — just a different address:

transaction_id customer_id transaction_date status quantity unit_price 1001 C001 2024-06-22 completed 1 899.00 <tr style="border-bottom:1px solid #e