SQLAlchemy
SQLAlchemy is the most widely used database library in the Python ecosystem. It provides two layers of database interaction: the Core, which is a thin abst...
SQLAlchemy: Python ORM for Database Abstraction
SQLAlchemy is the most widely used database library in the Python ecosystem. It provides two layers of database interaction: the Core, which is a thin abstraction over raw SQL that gives you more control, and the ORM (Object-Relational Mapper), which lets you work with database tables as Python classes and rows as objects.
Install SQLAlchemy with pip:
pip install sqlalchemy
The ORM layer is what most developers use. You define Python classes that map to database tables, and SQLAlchemy handles generating and executing the SQL behind the scenes:
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import DeclarativeBase, Session
class Base(DeclarativeBase): pass
class Student(Base): __tablename__ = "students" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(100), nullable=False) grade = Column(Integer)
engine = create_engine("sqlite:///school.db", echo=False) Base.metadata.create_all(engine)
with Session(engine) as session: # Insert session.add(Student(name="Hannah", grade=88)) session.commit()
# Query results = session.query(Student).filter(Student.grade > 80).all() for student in results: print(student.name, student.grade)
# Update student = session.query(Student).filter_by(name="Hannah").first() student.grade = 92 session.commit()
The create_engine() function accepts a connection URL that specifies the database type and location. Switching from SQLite to PostgreSQL requires only changing this URL (and installing the appropriate adapter like psycopg2). The Python code that defines models and runs queries stays the same.
SQLAlchemy's query system is powerful and Pythonic. You chain filter(), order_by(), limit(), and other methods to build complex queries without writing raw SQL. For operations that SQL handles more efficiently, you can still execute raw SQL through session.execute().
Relationships between tables are defined using the relationship() function and foreign keys. This allows you to access related objects through Python attribute access rather than writing JOIN queries manually.
Interview tip: Be ready to explain the difference between SQLAlchemy Core and ORM. Core gives you finer control and maps closely to SQL, while ORM maps tables to classes and is more abstract. Also mention that SQLAlchemy supports connection pooling by default, which makes it production-ready for web applications with many concurrent users.