SQL with Python (SQLite, MySQL, PostgreSQL)

SQL with Python (SQLite, MySQL, PostgreSQL)#

“Because your Python scripts deserve a proper database, not another CSV named ‘backup_final_please_work.csv’.”#

💬 Python Meets SQL: A Love Story#

Once upon a time, Python met SQL at a hackathon. SQL said, “I have all the data.” Python replied, “I have all the libraries.” And thus began the most powerful data relationship since Excel met Ctrl+Z. ❤️

Together, they could:

  • Store massive business data

  • Query insights in milliseconds

  • Power dashboards, models, and late-night debugging sessions

This section teaches you how to make that partnership work — without drama or data loss.

🧱 1. SQLite: The Introvert of Databases#

SQLite is that friend who doesn’t need a server or internet — just a .db file and a dream.

Perfect for:

  • Testing

  • Prototyping

  • Local data storage

It’s zero-setup, lightweight, and unreasonably reliable — like a good pocket calculator.

Example:#

import sqlite3

# Create a connection and table
conn = sqlite3.connect('business.db')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS sales (id INTEGER, revenue REAL)")

# Insert and fetch
cursor.execute("INSERT INTO sales VALUES (1, 2500.50)")
conn.commit()
cursor.execute("SELECT * FROM sales")
print(cursor.fetchall())

conn.close()

✅ Easy. ⚡ Fast. 💾 Everything saved in one .db file — like your data’s diary.

SQLite: “I’m small, but I remember everything.”

💼 2. MySQL: The Reliable Corporate Employee#

MySQL is what happens when you give structure a caffeine addiction. ☕ It’s everywhere — powering web apps, APIs, dashboards, and your old WordPress blog you forgot existed.

It’s great for business systems that require consistency, multi-user access, and transaction safety.

Python can talk to MySQL through the mysql.connector library:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="admin",
    password="12345",
    database="company_db"
)
cursor = db.cursor()
cursor.execute("SELECT name, salary FROM employees WHERE salary > 50000")

for row in cursor.fetchall():
    print(row)

You can almost hear SQL whisper,

“Relax. I’ve been doing this since the dot-com era.”

MySQL: The office veteran who’s seen it all and still delivers reports on time.

🧠 3. PostgreSQL: The Smart One with Too Many Features#

If databases were students, PostgreSQL would be the one who builds neural networks for fun on weekends.

Postgres supports:

  • Complex queries

  • JSON storage (it can behave like NoSQL!)

  • Full-text search

  • GIS (geospatial) data

  • Vector storage (via pgvector, if you’re feeling fancy)

Use it when you need serious data crunching — it’s built for data science, ML pipelines, and enterprise analytics.

import psycopg2

conn = psycopg2.connect(
    dbname="business",
    user="postgres",
    password="admin",
    host="localhost"
)
cursor = conn.cursor()
cursor.execute("SELECT department, AVG(salary) FROM employees GROUP BY department;")

for dept, avg in cursor.fetchall():
    print(dept, "→", round(avg, 2))

conn.close()

PostgreSQL: “I don’t just store your data, I understand it.”

🧩 SQL Query Examples That Make You Look Like a Pro#

-- Finding top 5 customers
SELECT name, SUM(amount) AS total_spent
FROM transactions
GROUP BY name
ORDER BY total_spent DESC
LIMIT 5;

-- Updating data safely
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

-- Joining tables (because relationships matter 💔)
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

SQL is like learning to speak in commands. Once you know the basics, you can make your database dance elegantly. 💃

⚙️ Why Use Python for SQL?#

Because Python:

  • Makes connecting to databases ridiculously easy

  • Lets you mix SQL + Pandas + ML in the same notebook

  • Has libraries for every database under the sun (sqlite3, mysql.connector, psycopg2, SQLAlchemy, etc.)

Example hybrid:

import pandas as pd
import sqlite3

conn = sqlite3.connect("business.db")
df = pd.read_sql_query("SELECT * FROM sales", conn)
print(df.describe())

SQL stores the data. Python turns it into business intelligence.

Together, they’re unstoppable. 🧠⚡

💬 Pro Tips for Database Awesomeness#

💡 Tip 1: Always close your connections — otherwise your database will haunt your memory (literally). 💡 Tip 2: Use parameterized queries to avoid SQL injection. 💡 Tip 3: For complex apps, use SQLAlchemy — it’s like having a translator between Python and SQL dialects.

🎬 Final Hook#

SQL is the language of data. Python is the voice of automation. Put them together and you get a system that:

  • Stores your business logic

  • Powers your ML pipelines

  • And never forgets a single transaction

So the next time someone says,

“Why not just use Excel?” Take a deep breath… and show them cursor.fetchall() like a boss. 😎

# Your code here
Contents