Slide 1

Slide 1 text

Why SQLAlchemy is Awesome From Zero to Holy Shit in 30 Minutes a talk by Armin Ronacher for the Pyramid London Meetup June 2013

Slide 2

Slide 2 text

Armin @mitsuhiko Ronacher I do computers :-) Flask, Werkzeug, Jinja2, …

Slide 3

Slide 3 text

SQL Databases are Awesome

Slide 4

Slide 4 text

SQLAlchemy is not just an ORM

Slide 5

Slide 5 text

• Connection Management • Driver Abstraction • SQL Expression Language • ORM + Unit-of-Work

Slide 6

Slide 6 text

Way too much for one talk

Slide 7

Slide 7 text

might be hostile to start with but it's worth it.

Slide 8

Slide 8 text

What is a Pyramid? This is just raw SQLAlchemy (You guys really should build something like Flask-SQLAlchemy)

Slide 9

Slide 9 text

SQLAlchemy Components N

Slide 10

Slide 10 text

Meta DB Driver Engine Connection Pool Table Definitions Session Transaction State Unit of Work Identity Map Connection Compiler SQL Compiler DDL Compiler Mapper

Slide 11

Slide 11 text

Defining your Schema I

Slide 12

Slide 12 text

i. Reflection from Database Schema ii. Table Definitions iii. Class Definitions (Declarative Base)

Slide 13

Slide 13 text

import sqlalchemy as db metadata = db.MetaData() users = db.Table('users', metadata, db.Column('user_id', db.Integer(), primary_key=True), db.Column('username', db.String()), db.Column('email_address', db.String()) )

Slide 14

Slide 14 text

engine = db.create_engine('postgres:///yourdb') metadata.create_all(engine)

Slide 15

Slide 15 text

Inserts and Queries II

Slide 16

Slide 16 text

>>> q = users.insert({'username': 'john', ... 'email_address': '[email protected]'}) ... >>> engine.execute(q) >>> engine.execute(users.select() ... .where(users.c.username == 'john') ... ).fetchone() ... (1, u'john', u'[email protected]')

Slide 17

Slide 17 text

posts = db.Table('posts', metadata, db.Column('post_id', db.Integer(), primary_key=True), db.Column('author_id', db.Integer(), db.ForeignKey('users.user_id')), db.Column('title', db.String()), db.Column('body', db.String()) )

Slide 18

Slide 18 text

>>> engine.execute(posts.insert({ ... 'author_id': 1, ... 'title': 'Hello World', ... 'body': 'Oh, so interesting' ... }))

Slide 19

Slide 19 text

>>> dict(engine.execute(posts.join(users).select()).fetchone()) {u'body': u'Oh, so interesting', u'username': u'john', u'user_id': 1, u'title': u'Hello World', u'post_id': 1, u'author_id': 1, u'email_address': u'[email protected]'}

Slide 20

Slide 20 text

Sessions III

Slide 21

Slide 21 text

>>> from sqlalchemy.orm import Session >>> s = Session(engine) >>> s.execute(users.select()).fetchone() (1, u'john', u'[email protected]')

Slide 22

Slide 22 text

Sessions wrap Transactions

Slide 23

Slide 23 text

>>> s.execute('invalid sql') Traceback (most recent call last): File "", line 1, in sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "invalid" LINE 1: invalid sql ^ >>> s.execute(users.select()).fetchone() Traceback (most recent call last): File "", line 1, in sqlalchemy.exc.InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block

Slide 24

Slide 24 text

The Mapper IV

Slide 25

Slide 25 text

Mapper: maps Tables to Classes

Slide 26

Slide 26 text

>>> from sqlalchemy.orm import mapper >>> >>> class User(object): ... pass ... >>> mapper(User, users) >>> s.query(User).filter(User.username == 'john').first() <__main__.User object at 0x106b19810> >>> _.email_address u'[email protected]'

Slide 27

Slide 27 text

Declarative Base V

Slide 28

Slide 28 text

Nicer API for Class + Table Creation

Slide 29

Slide 29 text

import sqlalchemy as db from sqlalchemy.ext.declarative import declarative_base metadata = db.MetaData() Base = declarative_base(metadata=metadata) class User(Base): __tablename__ = 'users' id = db.Column('user_id', db.Integer(), primary_key=True) username = db.Column(db.String()) email_address = db.Column(db.String())

Slide 30

Slide 30 text

import sqlalchemy as db from sqlalchemy.ext.declarative import declarative_base metadata = db.MetaData() Base = declarative_base(metadata=metadata) engine = db.create_engine('postgresql:///yourdb')

Slide 31

Slide 31 text

class User(Base): __tablename__ = 'users' id = db.Column('user_id', db.Integer(), primary_key=True) username = db.Column(db.String()) email_address = db.Column(db.String()) posts = db.orm.relationship('Post', backref='author', lazy='dynamic') class Post(Base): __tablename__ = 'posts' id = db.Column('post_id', db.Integer(), primary_key=True) author_id = db.Column(db.Integer(), db.ForeignKey('users.user_id')) title = db.Column(db.String()) body = db.Column(db.String())

Slide 32

Slide 32 text

>>> s = db.orm.Session(engine) >>> john = s.query(User).filter( ... User.username == 'john').first() ... >>> john.email_address u'[email protected]' >>> john.posts.count() 1L >>> john.posts.first().author is john True notice how john is in memory only once!

Slide 33

Slide 33 text

>>> me = User() >>> me.username = u'mitsuhiko' >>> me.email_address = u'[email protected]' >>> s.add(me) >>> s.commit() >>> me.email_address = u'[email protected]' >>> s.commit() notice how it automatically knows what to update

Slide 34

Slide 34 text

Session Scoping VI

Slide 35

Slide 35 text

Sessions can be context bound

Slide 36

Slide 36 text

import sys from sqlalchemy.orm import scoped_session, Session session = scoped_session(lambda: Session(engine)) def handle_request(request): try: return actual_request_handling(request) finally: if sys.exc_info()[2] is None: session.commit() session.remove()

Slide 37

Slide 37 text

Base.query = session.query_property() >>> session.query(User).first() <__main__.User object at 0x106b19810> >>> User.query.first() <__main__.User object at 0x106b19810> that's where context binding comes in useful

Slide 38

Slide 38 text

from sqlalchemy.orm import class_mapper, Query from sqlalchemy.exc import UnmappedClassError class _QueryProperty(object): def __get__(self, obj, type): try: mapper = class_mapper(type) if mapper: return type.query_class(mapper, session=session()) except UnmappedClassError: raise AttributeError('Model not mapped') Base = declarative_base() Base.query = _QueryProperty() Base.query_class = Query personal favorite

Slide 39

Slide 39 text

Advanced Queries VII

Slide 40

Slide 40 text

entries_a_month = s.query(Entry).filter( (db.extract(Entry.pub_date, 'year') == 2013) & (db.extract(Entry.pub_date, 'month') == 1) ).all()

Slide 41

Slide 41 text

q = session.query( User.age, db.func.count(User.id) ).group_by(User.age) for age, count in q.all(): print 'Users aged %d: %d' % (age, count)

Slide 42

Slide 42 text

session.query(Thread) \ .filter(Thread.id == thread.id) \ .update({Thread.view_count: Thread.view_count + 1})

Slide 43

Slide 43 text

thread = session.query(Thread).get(42) thread.views = Thread.views + 1 session.commit() the attribute temporarily will be an expression – commit early!

Slide 44

Slide 44 text

session.query(PlayerStats) \ .filter(PlayerStats.player_id == player.id) \ .update({ PlayerStats.score: db.func.greatest( PlayerStats.score, new_score ) })

Slide 45

Slide 45 text

The Sky is the Limit VIII

Slide 46

Slide 46 text

from sqlalchemy.dialects.postgresql import UUID, ARRAY class Post(Base): __tablename__ = 'posts' id = db.Column('post_id', UUID(as_uuid=True), primary_key=True) tags = db.Column(ARRAY(db.String()))

Slide 47

Slide 47 text

from sqlalchemy.sql import expression as expr from sqlalchemy.ext.compiler import compiles class Explain(expr.Executable, expr.ClauseElement): def __init__(self, stmt, analyze=False): self.stmt = stmt self.analyze = analyze @compiles(Explain) def visit_explain(element, compiler, **kw): return 'EXPLAIN (FORMAT JSON, ANALYZE %s) %s' % ( element.analyze and 'true' or 'false', compiler.process(element.stmt), )

Slide 48

Slide 48 text

from sqlalchemy.orm import Query from yourframework import NotFound class WebQuery(Query): def first_or_404(self): rv = self.first() if rv is None: raise NotFound() return rv

Slide 49

Slide 49 text

• Hybrid Properties • Association Proxies • Custom SQL Generation • Database Extensions • Update + Readback

Slide 50

Slide 50 text

• Automatic Partitioning • Automatic Sharding • Master+Slave Setup

Slide 51

Slide 51 text

Now build stuff!

Slide 52

Slide 52 text

That's it. Now ask questions. And add me on twitter: @mitsuhiko Slides at lucumr.pocoo.org/talks Like the talk: gittip.com/mitsuhiko Also send something Mike's way: gittip.com/zzzeek ?