• Library which saves developers lifes
• Proper ORM (Object Relationship Mapper) for Python
• Most advanced way to interact with database in Python applications
• Recent version: 0.9.3
• Supports: Python 2, Python 3

• EXPLICIT BETTER THAN IMPLICIT!
• THIS MEANS NO MAGIC!
• REALLY, NO DJANGO ORM MAGIC!

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@host:port/table')

• No DATABASES dict, just one database URL
  dj-database-url does the same for Django
• Raw connection?
  Yes connection = engine.connect(); connection.execute(...)
• Connection pools?
  Yes create_engine(..., pool_size=..., max_overflow=...)
• Multiple databases?
  Yes first = create_engine(...); second = create_engine(...);

connection = engine.connect()
result = connection.execute('SELECT username FROM users;')
usernames = [row['username'] for row in result]
connection.close()

• No ORM required
• Core and ORM is two distinct components.
• Core provides abstraction over various DBAPI implementations and behaviors
• And SQL Expressive Language which allows expression of the SQL language via generative Python expressions.
• ORM built on top of Core.

import sqlalchemy as sa

metadata = sa.MetaData()
users = sa.Table('users', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('username', sa.String(64), unique=True),
    ...
)

metadata.create_all(engine)

• Creates all tables in database
• Safe to call multiple times
• For migrations there is Alembic and SQLAlchemy-Migrate

metadata.reflect(engine)
users = metadata.tables['users']

• Autoload all existed tables to metadata
• Real-world example: Sandman.io (Your Database In Your Browser)

connection = engine.connect()
ins = users.insert().values(username='johndoe')
connection.execute(ins)
connection.execute(users.insert(), username='janedoe')
connection.execute(users.insert(), [
    {'username': 'gepa'},
    {'username': 'dopa'},
    {'username': 'ptnpnh'},
])
connection.close()

from sqlalchemy.sql import select
connection = engine.connect()

result = connection.execute(select([users]))
usernames = [row['username'] for row in result]
result.close()

query = select([users]).where(users.c.username == 'johndoe')
result = connection.execute(query)
username = result.fetchone()
result.close()

• Operators rule the SQLAlchemy party!

users.c.username == 'johndoe'
users.c.username != 'johndoe'
users.c.username.contains('doe')
~users.c.username.contains('doe')

users.c.username.like('doe')
users.c.username.match('doe$')
users.c.username.startswith('j')
users.c.username.endswith('doe')

• Conjuctions made as easy as possible

from sqlalchemy.sql import and_, or_, not_
users.c.username.like('%doe') & (users.c.id > 2)
and_(users.c.username.like('%doe'), users.c.id > 2)

users.c.username.like('%doe') | ~(users.c.id < 2)
or_(users.c.username.like('%doe'), not_(users.c.id < 2))

select([users]).where(...).where(...)
select([users]).where(or_(...))

• You can use aliases in Python

u1 = users.alias()
u2 = users.alias()
(u1.username == 'johndoe') & (u2.username == 'janedoe')

• And if you're guru, raw SQL is here and waiting for you

from sqlalchemy.sql import text
query = text(
    'SELECT username '
    'FROM users '
    'WHERE username LIKE :like AND id < :threshold'
)
result = connection.execute(query, like='%doe', threshold=3)

• Yes, it can make JOIN for you

users.join(orders)
select([users.c.username]).select_from(
    users.join(
        orders,
        orders.c.price > 50
    )
)
select([users.c.username]).select_from(users.outerjoin(orders))

• And that was only basics
• SQLAlchemy also can:
  • SQL functions
  • Window functions
  • Unions and other Sets operations
  • Subqueries
  • Ordering, grouping, limiting, offset...ing...
  • Inserts, updates, deletes
  • Multiple table updates
• SQLAlchemy is the Python SQL toolkit. I'm telling you!

from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Country(Base):
    """
    Country model.
    """
    __tablename__ = 'countries'

    code = Column(String(2), primary_key=True)
    name = Column(String(64), unique=True)

from sqlalchemy import ForeignKey, Integer, Numeric
from sqlalchemy.orm import backref, relationship

class City(Base):
    """
    City model.
    """
    __tablename__ = 'orders'

    id = Column(Integer, primary_key=True)
    country_code = Column(String(2), ForeignKey('countries.code'))
    name = Column(String(128))

    country = relationship(
        'Country', backref=backref('cities', lazy='dynamic')
    )

class Airport(Base):
    """
    Airport model.
    """
    __tablename__ = 'airports'

    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, ForeignKey('cities.id'))
    main_city_id = Column(Integer, ForeignKey('cities.id'), nullable=True)

    ...

    city = relationship('City',
                        backref=backref('airports', lazy='dynamic'),
                        foreign_keys=[city_id])
    main_city = relationship(
        'City',
        backref=backref('closest_airports', lazy='dynamic'),
        foreign_keys=[main_city_id]
    )

class Flight(Base):
    """
    Flight model.
    """
    __tablename__ = 'flights'

    id = Column(Integer, primary_key=True)
    back_flight_id = Column(Integer, ForeignKey('flights.id'), nullable=True)
    ...
    back_flight = relationship('Flight')

class Point(object):
    """
    Simple geo point.
    """
    __slots__ = ('latitude', 'longitude')

    def __init__(self, latitude, longitude):
        self.latitude, self.longitude = latitude, longitude

    def __composite_fields__(self):
        return (self.latitude, self.longitude)

    def __eq__(self, other):
        if not isinstance(other, Point):
            return False
        return (
            self.latitude == other.latitude and
            self.longitude == other.longitude
        )

    def __repr__(self):
        return 'Point({0:.4f}, {1:.4f})'.format(self.latitude, self.longitude)

from sqlalchemy import Numeric
from sqlalchemy.orm import composite

class Airport(Base):
    ...
    latitude = Column(Numeric(6, 4, True))
    longitude = Column(Numeric(6, 4, True))
    point = composite(Point, latitude, longitude)

categories_m2m = Table(
    'categories_m2m',
    Base.metadata,
    Column('category_id', Integer, ForeignKey('categories.id')),
    Column('post_id', Integer, ForeignKey('posts.id')).
)

class Category(Base):
    ...

class Post(Base):
    ...
    categories = relationship('Category',
                              backref=backref('posts', lazy='dynamic'),
                              secondary=categories_m2m)

engine = create_engine(DATABASE_URL)
for model in (Airport, City, Country, Flight):
    model.metadata.bind = engine
    model.metadata.create_all()

from sqlalchemy.orm import sessionmaker

engine = create_engine(DATABASE_URL)
Session = sessionmaker(engine)
session = Session()

• The ORM database "handle" is Session
• Session is "buffer" between your code and database
• Session gives connection from engine pool only when requesting

# Add (create) objects to database
session.add(Country(code='UA', name='Ukraine'))
session.add_all([
    Country(code='GB', name='Great Britain'),
    Country(code='US', name='United States of America')
])
# Commit changes to database
session.commit()
# Perform queries
ua = session.query(Country).filter_by(code='UA').first()
session.query(Country).filter(Country.code.in_(['GB', 'US']).all()
# Update object
ua.name = 'Viva la Ukraine'
# Apply change
session.commit()
# Or revert change
session.rollback()
# Delete object
gb = session.query(Country).filter(Country.code == 'GB').first()
session.delete(gb)
# Don't forget to commit session
session.commit()

from sqlalchemy import func
from sqlalchemy.orm.exc import NoResultFound

counter = session.query(func.count(Country.code)).scalar()
ua = session.query(Country).get('UA')
session.query(Country.name).filter(Country.code.in_(['GB', 'UA', 'US'])).all()
session.query(Airport).filter(Airport.iata.like('K%')).all()
kbp = session.query(Airport).filter(Airport.iata == 'KBP').first()
try:
    session.query(Airport).filter(Airport.iata == 'XXX').one()
except NoResultFound:
    ...
session.query(Airport).filter(Airport.main_city_id != None)

ua = session.query(Country).get('UA')
ua.cities.all()
ua.cities.filter(City.name.in_([
  'Kyiv', 'Kharkiv', 'Lviv', 'Odesa', 'Dnipropetrivsk', 'Donetsk'
])

kyiv = (session.query(City)
               .join(Country)
               .filter((City.name == 'Kyiv') & (Country.code == 'UA'))
               .first())
kyiv.airports.all()
kyiv.closest_airports.all()

session.query(City).join(Country).filter(Country.code == 'UA').all()
session.query(Airport, City).join(City, Airport.city_id == City.id).all()

from collections import defaultdict
from sqlalchemy.orm import joinedload
closest_cities = defaultdict(set)
query = (session.query(City, Airport)
                .options(joinedload(Airport.city))
                .join(Airport, City.id == Airport.main_city_id))
for city, airport in query.all():
    closest_cities[city.name].add(airport.city.name)

• Context manager to do session routine

from contextlib import contextmanager
from sqlalchemy.exc import SQLAlchemyError

@contextmanager
def session():
    session = Session()
    try:
        yield session
        session.commit()
    except SQLAlchemyError:
        session.rollback()
        raise
    finally:
        session.close()

• Declare proxy models
• Cascade operations
• Savepoints
• Autocommit sessions
• Partitions support
• Subqueries
• ...
• Put whatever else database operation here

• SQLAlchemy supported in
  • Flask (Flask-SQLAlchemy)
  • Pyramid (out of box)
  • Your framework
• There are some bridges between Django and SQLAlchemy
  • sqlalchemy-django-query (based on perfect Armin's SQLAlchemy and You article)
  • aldjemy
  • django-sabridge
• SQLAlchemy is the right way for working with database in Python

• Flask-Admin

• pyramid_formalchemy