Python Database Interaction — SQLite and SQLAlchemy Guide
In this tutorial, you will learn about Python Database Interaction. We cover key concepts, practical examples, and best practices to help you master this topic.
Most applications need to store and retrieve data. Python makes database programming straightforward with SQLite (built-in) for embedded use cases and SQLAlchemy for production-grade ORM.
In this tutorial, you'll learn both approaches. DodaTech uses SQLite for local threat signature caches and client-side data storage, and SQLAlchemy for the main security analytics platform.
What You'll Learn
- SQLite: creating databases, tables, CRUD operations
- Parameterized queries to prevent SQL Injection
- SQLAlchemy ORM: declarative models, relationships
- Query building with SQLAlchemy
- Connection pooling and session management
SQLite — Embedded Database
SQLite comes with Python. No setup needed:
import sqlite3
# Connect (creates file if not exists)
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
# Create table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
# Insert data
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
("Alice", "alice@example.com", 30)
)
cursor.execute(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
("Bob", "bob@example.com", 25)
)
conn.commit()
# Query
cursor.execute("SELECT * FROM users WHERE age > ?", (20,))
for row in cursor.fetchall():
print(row)
# Close
conn.close()
Expected output:
(1, 'Alice', 'alice@example.com', 30, '2026-06-29 12:00:00')
(2, 'Bob', 'bob@example.com', 25, '2026-06-29 12:00:00')
Security: Always Use Parameterized Queries
# UNSAFE: string formatting — SQL injection vulnerability!
name = "admin' OR '1'='1"
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# Executes: SELECT * FROM users WHERE name = 'admin' OR '1'='1'
# Returns ALL users!
# SAFE: parameterized query
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
# Treats entire string as a literal value
print(cursor.fetchall()) # Empty — no user with that exact name
SQLAlchemy ORM
For production applications, SQLAlchemy provides a powerful ORM:
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session
Base = declarative_base()
# Define models
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
email = Column(String, unique=True)
posts = relationship("Post", back_populates="author")
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True)
title = Column(String, nullable=False)
content = Column(String)
user_id = Column(Integer, ForeignKey("users.id"))
author = relationship("User", back_populates="posts")
# Create database
engine = create_engine("sqlite:///blog.db")
Base.metadata.create_all(engine)
# Insert with session
with Session(engine) as session:
user = User(name="Alice", email="alice@example.com")
session.add(user)
session.add(Post(title="Hello World", content="First post!", author=user))
session.commit()
# Query with relationships
with Session(engine) as session:
user = session.query(User).filter_by(name="Alice").first()
print(f"User: {user.name}")
for post in user.posts:
print(f" Post: {post.title}")
Practice Questions
Design a schema for a library system with Books, Authors, and Members.
Write a function that searches users by name using LIKE with parameterized queries.
Implement pagination: fetch 10 records at a time using LIMIT and OFFSET.
Use SQLAlchemy to model a many-to-many relationship (students and courses).
Write a Migration script that adds a new column to an existing table.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro