How SQLAlchemy saving the world?

What is SQLAlchemy?

  • 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

How it works?

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

No Magic

Connect to database

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(...);

Working without ORM

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.

SQL Expressive Language

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

SQL Expressive Language

metadata.create_all(engine)

SQL Expressive Language

metadata.reflect(engine)
users = metadata.tables['users']
  • Autoload all existed tables to metadata
  • Real-world example: Sandman.io (Your Database In Your Browser)

Sandman.io

SQL Expressive Language

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()

SQL Expressive Language

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()

SQL Expressive Language

  • 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')

SQL Expressive Language

  • 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_(...))

SQL Expressive Language

  • 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)

SQL Expressive Language

  • 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))

SQL Expressive Language

  • 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!

And here comes ORM

Declare models

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)

Declare Foreign Key

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')
    )

Declare multiple Foreign Keys

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]
    )

Declare self Foreign Key

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')

Declare composite field

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)

Declare composite field

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)

Declare Many to Many relationship

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)

Create tables in database

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

Meet the session, main part of ORM

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

Basic Session methods

# 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()

More fun with queries

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)

Accessing backref's

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()

Making JOINs

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)

And more, more, more

  • 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()

And more, more, more

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

In total

And if you asking

And if you asking

You might not need a Django

You might not need a Django!

Questions?