SQLAlchemy – Your Database Companion

Introduction

SQLAlchemy goes beyond simple connections; it offers an ORM and powerful abstractions. This primer covers connecting to databases, defining tables and relationships, and performing basic CRUD operations.

Connecting to Databases

Use create_engine to define the connection.

SQLite

1
2
from sqlalchemy import create_engine
engine = create_engine("sqlite://", echo=True)

PostgreSQL

1
engine = create_engine("postgresql+psycopg2://user:password@localhost/dbname")

Defining Tables

1
2
3
4
5
6
7
8
9
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Create the schema with Base.metadata.create_all(engine).

Relationships

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship('User', back_populates='addresses')

User.addresses = relationship('Address', back_populates='user')

CRUD Operations

1
2
3
4
5
6
from sqlalchemy.orm import Session

with Session(engine) as session:
    user = User(name='Alice')
    session.add(user)
    session.commit()

Conclusion

SQLAlchemy’s ORM and connection tools make database interaction in Python concise and maintainable.