SQL with Python — pandas & SQLAlchemy

import sqlite3
import pandas as pd

# Connect:
conn = sqlite3.connect("company.db")

# Query directly to DataFrame:
df = pd.read_sql_query("SELECT * FROM employees WHERE salary > 50000", conn)

# Parameterised query (safe — prevents SQL injection):
df = pd.read_sql_query(
    "SELECT * FROM employees WHERE department = ?",
    conn, params=("Engineering",)
)

conn.close()

pd.read_sql_query() combines SQL execution and DataFrame creation in one step. Always use parameterised queries (? placeholder) — never string-format user input into SQL queries (SQL injection risk).

from sqlalchemy import create_engine
import pandas as pd

# MySQL:
engine = create_engine("mysql+pymysql://user:password@host:3306/database")

# PostgreSQL:
engine = create_engine("postgresql+psycopg2://user:password@host:5432/database")

# Query:
df = pd.read_sql("SELECT * FROM sales WHERE year = 2026", engine)

# Write DataFrame to DB:
df.to_sql("new_table", engine, if_exists="replace", index=False)

SQLAlchemy is the standard Python database abstraction layer. The connection string format is driver://user:password@host:port/database. Never hardcode credentials — use environment variables instead.

df.to_sql(
    name="monthly_sales",
    con=engine,
    if_exists="append",   # append, replace, or fail
    index=False,           # do not write row index
    chunksize=500          # write in batches of 500
)

if_exists="append" adds rows to an existing table. "replace" drops and recreates the table. "fail" raises an error if the table exists. Use chunksize for large DataFrames to avoid memory issues.

import pandasql as ps

df_sales = pd.read_csv("sales.csv")
df_customers = pd.read_csv("customers.csv")

query = """
    SELECT c.city, SUM(s.amount) as total_sales
    FROM df_sales s
    JOIN df_customers c ON s.customer_id = c.id
    WHERE s.year = 2026
    GROUP BY c.city
    ORDER BY total_sales DESC
"""
result = ps.sqldf(query, locals())

pandasql lets you write SQL directly on Pandas DataFrames — useful when you think in SQL but want to stay in Python. Table names in the query are the variable names of your DataFrames.

import os
from dotenv import load_dotenv

load_dotenv()  # loads .env file

DB_USER = os.getenv("DB_USER")
DB_PASS = os.getenv("DB_PASS")
DB_HOST = os.getenv("DB_HOST")

engine = create_engine(f"postgresql+psycopg2://{DB_USER}:{DB_PASS}@{DB_HOST}/mydb")

Never hardcode credentials in Python scripts. Store them in a .env file (add to .gitignore), load with python-dotenv. This is the standard practice in every professional environment.