Python and SQL: From Raw Queries to ORMs

Nearly every dynamic application needs persistent data storage. Python has become a powerhouse in backend development because it offers incredible flexibility in how you interact with databases. It provides a large repertoire of tools ranging from executing raw, low-level SQL commands to using sophisticated Object-Relational Mappers (ORMs) that let you forget SQL is even in the stack.

Working with databases in Python is about choosing the right level of abstraction. Sometimes you need total control over the query, and other times you just want to deal with regular Python objects.

1. The Standard Library: Raw SQL with sqlite3

Python comes with built-in support for SQLite through the standard library. This is the closest you can get to the metal without needing external dependencies. It involves establishing a connection, creating a "cursor" object to traverse database records, and executing SQL strings directly. It’s perfect for small scripts or small test programs.

import sqlite3

# Connect to an in-memory database for example
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

# Create a table using raw SQL
cursor.execute('''
    CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, email TEXT)
''')

# Insert data
cursor.execute("INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com')")
rows = cursor.fetchall()
# Commit the changes
connection.commit()

print("User table created and data inserted.")
print(f"Written data: {cursor.fetchall()}")

2. Safety First: Parameterized Queries

A critical mistake people often make when using raw SQL is using Python string formatting (like f-strings on the previous example.) to construct queries with variables. This opens the door to many SQL Injection attacks during production.

The correct approach is to use parameterized queries. Instead of inserting variables directly into the SQL string, you use placeholders (like ? in SQLite or %s in PostgreSQL libraries).

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
cursor.execute('CREATE TABLE users (username TEXT)')
connection.commit()

# Assume this input comes from a user web form
new_user = "bobby_tables'; DROP TABLE users; --"

# DANGEROUS: Do not do this!
# cursor.execute(f"INSERT INTO users VALUES ('{new_user}')") 

# SAFE: Use placeholders
# The driver handles the sanitization safely.
cursor.execute("INSERT INTO users VALUES (?)", (new_user,))
connection.commit()

print("Data inserted safely without dropping the table.")

3. Object-Relational Mappers (SQLAlchemy)

Writing raw SQL for complex relationships becomes tedious and error-prone. Enter the ORM (Object-Relational Mapper). An ORM allows you to define your database tables as Python classes. You interact with instances of these classes, and the library translates your Python actions into the underlying SQL automatically. SQLAlchemy is the standard in the Python ecosystem.

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

# Setup the engine and base class
engine = create_engine('sqlite:///:memory:')
Base = declarative_base()

# Define the table as a Python class
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)

# Create the table in the database
Base.metadata.create_all(engine)

# Start a session and add a Python object
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(username='charlie_dev')
session.add(new_user)
session.commit()

# We can retrieve it as a Python object
stored_user = session.query(User).filter_by(username='charlie_dev').first()
print(f"Retrieved user object: {stored_user.username} with ID: {stored_user.id}")

With ORM's we can reduce errors and lets us use common existing python actions such as interacting with database rows like normal Python objects. Instead of manually writing SQL queries, binding parameters, and parsing results, an ORM lets you treat rows as first‑class Python objects. Super useful since it uses python's automatic type handling, built-in validation, and makes it very easy to refactor if needed.