Connecting Python To Mysql And Postgresql The Real

You’ve been saving your data analysis results as CSVs. It worked fine when you were the only one using the data. But now your team needs to query the same dataset, share updates, and keep everything safe from accidental deletion. You know you need a database, but every tutorial you find assumes you already know what a ‘connection string’ is or why you’d need ‘connection pooling.’ The setup feels like a maze of drivers, error messages, and mysterious configuration files.

Here’s the good news: the hardest part isn’t writing SQL — it’s getting the connection right. Once you understand the few key pieces, connecting Python to MySQL or PostgreSQL becomes straightforward. By the end of this guide, you’ll be able to connect to either database, run queries, handle errors, and even make your code run faster with connection pooling.

Let’s start with the big picture: how do you actually talk to a database from Python?

The Two Paths: Direct Drivers vs. SQLAlchemy (and Why You Should Care)

Think of connecting to a database like getting around a new city. You have two main options:

1. Direct drivers are like renting a car. You’re in control, but you have to handle everything yourself — finding parking, reading maps, dealing with traffic. In Python terms, direct drivers like mysql-connector-python or psycopg2 give you a raw connection object. You write SQL strings, manage connections manually, and handle each database’s quirks yourself.

2. SQLAlchemy is like hiring a chauffeur. There’s more setup upfront (you need to tell the chauffeur where you’re going and what car you want), but once it’s set up, the chauffeur handles traffic, parking, and navigation. SQLAlchemy’s Engine object manages a pool of connections, translates between different databases, and protects you from SQL injection attacks.

Here’s what this looks like in code. Let’s connect to a PostgreSQL database both ways:

# Direct driver approach (psycopg2)
import psycopg2

# You get a raw connection object
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="sales_data",
    user="postgres",
    password="mysecretpassword"
)
print(type(conn))  # <class 'psycopg2.extensions.connection'>

# SQLAlchemy approach
from sqlalchemy import create_engine

# You get an Engine that manages a pool of connections
engine = create_engine(
    "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data"
)
print(type(engine))  # <class 'sqlalchemy.engine.base.Engine'>

Notice the difference? With psycopg2, you get a single connection object. With SQLAlchemy, you get an Engine — a factory that creates and manages connections. The engine doesn’t connect immediately; it waits until you actually run a query. This is called lazy initialization, and it’s a good thing because it means you can set up your engine once and reuse it throughout your application.

So which should you use? Here’s a simple rule:

• Use direct drivers when you’re writing a quick script, you know you’ll only ever use one database, and you want minimal dependencies.
• Use SQLAlchemy when you’re building a data pipeline that might switch databases later, when you need connection pooling, or when you want safer code.

Now let’s get your environment set up.

Setting Up Your Environment: What to Install and Why

The most common frustration I see is people installing the wrong package. Let’s clear that up right now.

For MySQL, you have two good options:

• mysql-connector-python — the official MySQL connector. It’s well-maintained and works reliably.
• pymysql — a pure Python driver. It’s lighter and doesn’t require any C extensions, making it easier to install in some environments.

For PostgreSQL, the standard choice is:

• psycopg2-binary — the easiest to install. It comes with pre-compiled binaries, so you don’t need to compile anything.
• psycopg2 — the production version. It requires the libpq library to be installed on your system. For most development work, psycopg2-binary is fine.

And for SQLAlchemy, you just need:

• sqlalchemy — the core library. Note that SQLAlchemy doesn’t include a database driver — you still need one of the drivers above.

Here’s the exact command to install everything you need for both databases:

# Install all the packages at once
# Run this in your terminal, not in Python
# pip install sqlalchemy psycopg2-binary pymysql mysql-connector-python

Now let’s verify the installation works:

# Test that everything imported correctly
import sqlalchemy
import psycopg2
import pymysql

print(f"SQLAlchemy version: {sqlalchemy.__version__}")
print("All imports successful!")

Important: Always use a virtual environment! Run python -m venv myenv before installing anything. This prevents version conflicts between projects. Trust me, you don’t want to debug a “works on my machine” issue caused by conflicting package versions.

The Connection String: Your Database’s Phone Number

This is where most beginners get stuck. The connection string looks like a jumble of characters, but it’s actually just a phone number for your database. Let’s break it down piece by piece.

The format is always:

dialect+driver://username:password@host:port/database

Here’s what each part means:

• dialect: The database type (postgresql, mysql, sqlite, etc.)
• driver: The Python library that handles the actual communication (psycopg2, pymysql, etc.)
• username: Your database user (often postgres for PostgreSQL, root for MySQL)
• password: The user’s password
• host: Where the database lives (localhost if it’s on your machine)
• port: The port number (5432 for PostgreSQL, 3306 for MySQL)
• database: The specific database you want to connect to

Let’s see this in action with a concrete example. We’ll create an engine for a local PostgreSQL database called sales_data:

from sqlalchemy import create_engine

# PostgreSQL connection string
postgres_url = "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data"
engine = create_engine(postgres_url)

# Notice: the engine doesn't connect yet — it's just a blueprint
print(f"Engine created: {engine}")
print("No connection made yet — SQLAlchemy waits until you run a query.")

Now let’s see the MySQL version. The only changes are the dialect, driver, and port:

from sqlalchemy import create_engine

# MySQL connection string (notice the different dialect and port)
mysql_url = "mysql+pymysql://root:mysecretpassword@localhost:3306/sales_data"
engine = create_engine(mysql_url)

print(f"MySQL engine created: {engine}")

Common mistakes to watch out for:

1. Forgetting the port: If you don’t specify a port, SQLAlchemy uses the default for that dialect. But if your database runs on a non-standard port, your connection will fail silently.
2. Wrong dialect name: It’s postgresql, not postgres or psql.
3. Special characters in password: If your password contains @, :, or /, you need to URL-encode them. For example, a password like pass@word becomes pass%40word.

Security warning: Never hardcode credentials in your code! Use environment variables instead:

import os
from sqlalchemy import create_engine

# Read credentials from environment variables
DB_USER = os.getenv("DB_USER", "postgres")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME", "sales_data")

# Build the connection string safely
url = f"postgresql+psycopg2://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(url)
print("Engine created from environment variables.")

Your First Query: SELECT, INSERT, and What Actually Happens

Now that we have an engine, let’s actually talk to the database. We’ll use a running example: a table called orders with columns order_id, customer_name, amount, and order_date.

First, let’s create the table and insert some data. We’ll use SQLAlchemy Core (not the ORM) to keep things simple:

from sqlalchemy import create_engine, text

# Create engine (replace with your actual credentials)
engine = create_engine("postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data")

# Use a connection context manager — it automatically closes the connection
with engine.connect() as conn:
    # Create the table (IF NOT EXISTS prevents errors on re-run)
    conn.execute(text("""
        CREATE TABLE IF NOT EXISTS orders (
            order_id SERIAL PRIMARY KEY,
            customer_name VARCHAR(100),
            amount DECIMAL(10, 2),
            order_date DATE
        )
    """))
    
    # Insert a row using parameterized query (safe from SQL injection)
    conn.execute(
        text("INSERT INTO orders (customer_name, amount, order_date) VALUES (:name, :amount, :date)"),
        {"name": "Alice", "amount": 150.00, "date": "2024-01-15"}
    )
    
    # Commit the transaction
    conn.commit()
    
    print("Table created and row inserted.")

Now let’s query the data back:

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data")

with engine.connect() as conn:
    # Execute a SELECT query
    result = conn.execute(text("SELECT * FROM orders"))
    
    # The result is a Result object — you need to iterate or call fetchall()
    print("All orders:")
    for row in result:
        # Each row is tuple-like — you can access by position or column name
        print(f"  Order {row.order_id}: {row.customer_name} spent ${row.amount:.2f} on {row.order_date}")

What’s actually going on here?

1. engine.connect() opens a connection from the pool (or creates a new one).
2. conn.execute() sends the SQL to the database and returns a Result object.
3. The Result object doesn’t contain all the data yet — it’s more like a cursor that fetches rows as you iterate.
4. conn.commit() saves the changes. Without it, the INSERT would be rolled back when the connection closes.

This is the hardest part: understanding that execute() returns a Result object, not the actual data. You have to iterate over it or call .fetchall() to get the rows. Many beginners forget this and wonder why their query “didn’t work.”

Let’s see what happens if we try to print the result directly:

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data")

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM orders"))
    
    # This won't give you the data — it just shows the object type
    print(f"Result type: {type(result)}")
    print(f"Result object: {result}")
    
    # To get the actual data, you need to iterate or fetch
    rows = result.fetchall()
    print(f"Number of rows: {len(rows)}")
    for row in rows:
        print(row)

Connection Pooling: Why Your App Slows Down (and How to Fix It)

Imagine you’re driving to the grocery store. Without pooling, you’d start your car engine, drive to the store, buy your items, drive home, and turn off the engine. Then if you forgot something, you’d have to start the engine again. That’s what happens without connection pooling — every query opens and closes a new database connection.

Opening a database connection takes time — typically 20-50 milliseconds. That doesn’t sound like much, but if your app handles 100 requests per second, that’s 2-5 seconds just spent on connection overhead.

Connection pooling keeps a few connections “warm” so you can reuse them instantly. SQLAlchemy’s default pool is called QueuePool, and it keeps 5 connections alive by default.

Let’s see the difference in action. First, without pooling (simulating by creating a new engine for each query):

import time
from sqlalchemy import create_engine, text

# Simulate no pooling by creating a new engine for each query
def query_without_pooling():
    start = time.time()
    for i in range(10):
        # Creating a new engine each time (no pooling)
        engine = create_engine("postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data",
                               poolclass=None)  # Disable pooling
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
    return time.time() - start

# This might take a while, so let's just measure a smaller sample
print("Measuring time without pooling...")
no_pool_time = query_without_pooling()
print(f"Time without pooling: {no_pool_time:.3f} seconds")

Now with pooling:

import time
from sqlalchemy import create_engine, text

# With pooling — create one engine and reuse it
def query_with_pooling():
    engine = create_engine(
        "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data",
        pool_size=5,  # Keep 5 connections in the pool
        max_overflow=10  # Allow up to 10 extra connections if needed
    )
    
    start = time.time()
    for i in range(10):
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
    return time.time() - start

print("Measuring time with pooling...")
pool_time = query_with_pooling()
print(f"Time with pooling: {pool_time:.3f} seconds")
print(f"Speedup: {no_pool_time/pool_time:.1f}x faster")

What this means in plain English: Without pooling, each query had to open a new connection (50ms overhead). With pooling, the connections were already open, so each query took just 1-2ms. That’s a 25x speedup for a simple query.

Here’s the catch: Too many idle connections can exhaust your database’s resources. If you set pool_size=100 but only use 5 connections at a time, you’re wasting database memory. A good rule of thumb is to set pool_size to the number of concurrent users your app typically handles, and max_overflow to handle traffic spikes.

Going Async: When Your Data Pipeline Needs Speed (Optional Deep Dive)

For most data science work, synchronous connections are perfectly fine. But if you’re building a real-time dashboard or an API endpoint that handles many requests simultaneously, synchronous code blocks the event loop. That’s where async drivers come in.

Async drivers like asyncpg (for PostgreSQL) and aiomysql (for MySQL) let you run multiple queries concurrently without blocking. In benchmarks, asyncpg is often 2-3x faster than psycopg2 for high-concurrency workloads.

Here’s a simple async example using asyncpg:

import asyncio
import asyncpg

async def query_async():
    # Connect to the database
    conn = await asyncpg.connect(
        user="postgres",
        password="mysecretpassword",
        database="sales_data",
        host="localhost",
        port=5432
    )
    
    # Run a query — note the await keyword
    rows = await conn.fetch("SELECT * FROM orders")
    
    # Print results
    for row in rows:
        print(f"Order {row['order_id']}: {row['customer_name']}")
    
    # Close the connection
    await conn.close()

# Run the async function
asyncio.run(query_async())

What’s the difference? With synchronous code, when you call conn.execute(), your Python script stops and waits for the database to respond. With async code, await conn.fetch() tells Python: “Start this database query, and while we wait, go do something else.” This means your app can handle other requests while the database is working.

When should you use async?

• Use async if you’re already using FastAPI, aiohttp, or another async framework.
• Use sync for batch scripts, data pipelines, or any application where you’re only doing one thing at a time.

SQLAlchemy 1.4+ also supports async via create_async_engine:

from sqlalchemy.ext.asyncio import create_async_engine

# Note: use asyncpg driver for async PostgreSQL
async_engine = create_async_engine(
    "postgresql+asyncpg://postgres:mysecretpassword@localhost:5432/sales_data"
)

# Usage requires async context manager
async def query():
    async with async_engine.connect() as conn:
        result = await conn.execute(text("SELECT * FROM orders"))
        rows = result.fetchall()
        return rows

Putting It All Together: A Real-World Data Pipeline

Let’s combine everything into a single script that reads a CSV of orders, inserts them into both MySQL and PostgreSQL, then queries and prints summary statistics. This shows the power of SQLAlchemy — switching databases is a one-line change.

First, let’s create a sample CSV file:

import pandas as pd
import numpy as np

# Generate sample data
np.random.seed(42)
data = {
    "customer_name": [f"Customer_{i}" for i in range(100)],
    "amount": np.random.uniform(10, 500, 100),
    "order_date": pd.date_range("2024-01-01", periods=100, freq="D")
}
df = pd.DataFrame(data)

# Save to CSV
df.to_csv("orders.csv", index=False)
print(f"Created orders.csv with {len(df)} rows")

Now let’s build the pipeline:

import pandas as pd
from sqlalchemy import create_engine, text
import time

# Read the CSV
print("Reading CSV...")
df = pd.read_csv("orders.csv")
print(f"Loaded {len(df)} orders")

# Create engines for both databases
# PostgreSQL engine
pg_engine = create_engine(
    "postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/sales_data",
    pool_size=5
)

# MySQL engine (uncomment if you have MySQL running)
# mysql_engine = create_engine(
#     "mysql+pymysql://root:mysecretpassword@localhost:3306/sales_data",
#     pool_size=5
# )

# Insert into PostgreSQL using pandas to_sql (uses SQLAlchemy under the hood)
print("Inserting into PostgreSQL...")
start = time.time()
df.to_sql("orders", pg_engine, if_exists="replace", index=False, method="multi")
pg_time = time.time() - start
print(f"Inserted into PostgreSQL in {pg_time:.2f} seconds")

# Verify by querying
print("\nVerifying PostgreSQL data...")
with pg_engine.connect() as conn:
    result = conn.execute(text("SELECT COUNT(*) as count FROM orders"))
    count = result.fetchone()[0]
    print(f"Total rows in PostgreSQL: {count}")
    
    result = conn.execute(text("SELECT AVG(amount) as avg_amount FROM orders"))
    avg = result.fetchone()[0]
    print(f"Average order amount: ${avg:.2f}")

print("\nPipeline complete!")

Notice the magic: We used df.to_sql() with method="multi" for faster bulk inserts. The if_exists="replace" option drops and recreates the table — useful for development but be careful in production!

To switch to MySQL, you’d only change the connection string. That’s the power of SQLAlchemy — your code doesn’t care which database is behind the engine.

What We Learned (and Where to Go Next)

Let’s recap what you’ve learned:

1. Two approaches to connect: Direct drivers (like psycopg2) give you raw connections; SQLAlchemy gives you an Engine that manages connection pools and abstracts database differences.
2. Connection strings: The format dialect+driver://user:pass@host:port/database is your database’s phone number. Never hardcode credentials.
3. Running queries: Use with engine.connect() as conn: for safe connection management. Remember that execute() returns a Result object — you need to iterate or call fetchall().
4. Connection pooling: Keeps connections warm, reducing query time from ~50ms to ~1ms. Configure pool_size and max_overflow based on your workload.
5. Async drivers: For high-concurrency apps, asyncpg and aiomysql let you run queries without blocking the event loop.
6. Real-world pipeline: With SQLAlchemy, switching between MySQL and PostgreSQL is a one-line change.

Next up: We’ll explore SQLAlchemy ORM — writing Python classes that map to database tables. This makes your code even cleaner and more maintainable.

Check Your Understanding

Remember: What is the format of a SQLAlchemy connection string? Name each component.

Understand: Explain why connection pooling improves performance in your own words. What happens without it?

Apply: Given a MySQL database with a table products (columns: product_id, name, price), write code to connect and select all rows where price > 100.

Analyze: Compare the output of a synchronous vs. async query when the database is slow (e.g., a query that takes 5 seconds). Which one would block your Python script from doing other work?

Evaluate: When would you choose psycopg2 over SQLAlchemy? When would you choose async over sync? Give concrete scenarios.

Create: Write a script that reads a CSV file, inserts the data into PostgreSQL using SQLAlchemy with connection pooling, and prints the time taken for the insert operation.