Working with Databases SQLite, MySQL, ORMs (SQLAlchemy)

Python Databases: SQLite, MySQL & ORMs

Learn to store, query, and manage data efficiently using Python!

1. SQLite (Built-in Database)

SQLite is a lightweight, serverless database ideal for small projects and prototyping.

Basic Operations

import sqlite3  

# Connect to a database (creates if missing)  
conn = sqlite3.connect("school.db")  
cursor = conn.cursor()  

# Create a table  
cursor.execute("""  
    CREATE TABLE IF NOT EXISTS students (  
        id INTEGER PRIMARY KEY,  
        name TEXT,  
        age INTEGER  
    )  
""")  

# Insert data  
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Alice", 15))  
conn.commit()  # Save changes  

# Query data  
cursor.execute("SELECT * FROM students")  
print(cursor.fetchall())  # Output: [(1, 'Alice', 15)]  

# Close connection  
conn.close()  

            

Key Functions:

• sqlite3.connect(): Opens a connection.
• cursor.execute(): Runs SQL queries.
• conn.commit(): Saves changes (essential for writes).

2. MySQL (Server-Based Database)

For larger applications, use MySQL with the mysql-connector library.

Setup & CRUD Operations

Install the driver:

pip install mysql-connector-python  

            

Connect and interact:

import mysql.connector  

# Establish connection  
conn = mysql.connector.connect(  
    host="localhost",  
    user="root",  
    password="password",  
    database="school"  
)  
cursor = conn.cursor()  

# Create table (similar to SQLite syntax)  
cursor.execute("CREATE TABLE IF NOT EXISTS students (...)")  

# Insert data  
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Bob", 16))  
conn.commit()  

# Fetch data  
cursor.execute("SELECT * FROM students")  
for row in cursor.fetchall():  
    print(row)  

conn.close()  

            

Key Differences from SQLite:

• Requires a running MySQL server.
• Uses %s placeholders instead of ?.
• Supports transactions and user permissions.

3. ORMs: SQLAlchemy (Pythonic Database Interaction)

Object-Relational Mappers (ORMs) let you interact with databases using Python classes instead of SQL.

SQLAlchemy Setup

Install the library:

pip install sqlalchemy  

            

Define a model and interact:

from sqlalchemy import create_engine, Column, Integer, String  
from sqlalchemy.orm import declarative_base, sessionmaker  

# Define the database (SQLite in this example)  
engine = create_engine("sqlite:///school.db")  
Base = declarative_base()  

# Create a model  
class Student(Base):  
    __tablename__ = "students"  
    id = Column(Integer, primary_key=True)  
    name = Column(String)  
    age = Column(Integer)  

# Create tables  
Base.metadata.create_all(engine)  

# Create a session  
Session = sessionmaker(bind=engine)  
session = Session()  

# Add a student  
student = Student(name="Charlie", age=14)  
session.add(student)  
session.commit()  

# Query all students  
students = session.query(Student).all()  
for s in students:  
    print(s.name, s.age)  

session.close()  

            

Benefits of ORMs:

• Write Python code instead of SQL.
• Avoid SQL injection attacks.
• Easily switch databases (SQLite ↔ MySQL ↔ PostgreSQL).

4. Real-World Project: Task Manager

Combine SQLAlchemy with a simple CLI:

class Task(Base):  
    __tablename__ = "tasks"  
    id = Column(Integer, primary_key=True)  
    title = Column(String)  
    completed = Column(Boolean, default=False)  

def add_task(title):  
    task = Task(title=title)  
    session.add(task)  
    session.commit()  

def show_tasks():  
    tasks = session.query(Task).filter_by(completed=False).all()  
    for task in tasks:  
        print(f"{task.id}. {task.title}")  

add_task("Learn Python Databases")  
show_tasks()  

            

Comparison: SQLite vs MySQL

Feature	SQLite	MySQL
Server	Serverless (file-based)	Requires a running server
Scalability	Good for small projects	Handles large-scale applications
Concurrency	Limited	High
Setup	No installation needed	Requires server setup

Best Practices

• Use Context Managers: Automatically handle connections.

with sqlite3.connect("school.db") as conn:  
    cursor = conn.cursor()  
    # Operations...  

            

• Parameterized Queries: Prevent SQL injection.

# BAD: cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")  
# GOOD: cursor.execute("SELECT * FROM users WHERE name = ?", (name,))  

            

• Indexing: Speed up queries on large tables.

CREATE INDEX idx_students_name ON students (name);  

            

Key Takeaways

• ✅ SQLite: Simple, file-based database for prototyping.
• ✅ MySQL: Robust server-based DB for production apps.
• ✅ SQLAlchemy: ORM for Pythonic, database-agnostic code.

Practice Problem

Create a books database (using SQLAlchemy) with columns: id, title, author, year.

Add 3 books.

Query books published after 2000.

What’s Next?

Learn web development with Flask/Django to build database-driven web apps!

Previous Next