.. include:: .. Copyright 2007, Jason Kirtland License: Creative Commons Attribution-Share Alike 3.0 ======================= SQLAlchemy in 5 Minutes ======================= A whirlwind tour ---------------- :Author: Jason Kirtland :Date: July 10th, 2007 .. footer:: Portland Python Users Group Lighting Talk What is SQLAlchemy? =================== .. class:: incremental 1. an ORM 2. a powerful layer just above DBAPI 3. a toolkit for SQL-literate applications Database Support ================ - SQLite - PostgreSQL - MySQL - SQL Server - Oracle - Firebird - Informix - ...and more on the way SQLAlchemy can use any of these- or all of them, all at once. Pythonic SQL Generation ======================= .. class:: incremental - High quality SQL can be created from regular Python expressions - And even better SQL when it knows which database is targeted - No strings. :py:`"SELECT * FROM slides"` is ``slides.select()`` - Unless you want to. :py:`"SELECT * FROM slides"` - You can use generated or hand-written SQL anywhere Database Schema =============== SQLAlchemy maintains a schema catalog for SQL generation. Database Schema =============== You can specify table schemas generically .. code-block:: python slides = Table('slides', catalog, Column('id', Integer, primary_key=True), Column('title', String(50))) bullets = Table('bullets', metadata, Column('id', Integer, primary_key=True), Column('slide_id', Integer, ForeignKey('slides.id'), nullable=False), Column('pos', Integer), Column('text', String)) SQL Types ========= Or read tables from the database via reflection .. code-block:: python slides = Table('slides', catalog, autoload=True) bullets = Table('bullets', catalog, autoload=True) Or just snarf everything .. code-block:: python catalog = MetaData('sqlite://file.db', autoload=True) SQL Types ========= Or target features of a particular database engine .. code-block:: python slides = Table('slides', catalog, Column('id', mysql.Integer(unsigned=True), primary_key=True) Column('title', mysql.SmallText(collate='utf8_bin'), nullable=False)) SQL Types ========= The catalog of tables and columns is used for SQL generation and can also generate DDL. Tables can be easily created and dropped: - ``slides.create()`` - ``catalog.drop_all()`` Generic DDL is adapted to the database engine on creation. Cross-platform is easy, and exploiting the capabilities of a single engine is easy too. Simple INSERTs and SELECTs ========================== .. code-block:: pycon >>> slides.insert().execute(title='slide 1') >>> row = slides.select().execute().fetchone() >>> row (1, u'slide 1') >>> row.title u'slide 1' Expressions =========== .. code-block:: python slides.select(slides.c.id == 1) .. code-block:: sql SELECT slides.id, slides.title FROM slides WHERE slides.id = ? Expressions =========== .. code-block:: python slides.select(slides.c.title.in_('slide 1', 'slide 2')) .. code-block:: sql SELECT slides.id, slides.title FROM slides WHERE slides.title IN (?, ?) Inner Joins =========== .. code-block:: python slides.join(bullets).select() .. code-block:: sql SELECT slides.id, slides.title, bullets.id, bullets.slide_id, bullets.pos, bullets.text FROM slides JOIN bullets ON slides.id = bullets.slide_id Outer Joins =========== .. code-block:: python outerjoin(slides, bullets).select(slides.c.title.like('%SQL Types%')) .. code-block:: sql SELECT slides.id, slides.title, bullets.id, bullets.slide_id, bullets.pos, bullets.text FROM slides LEFT OUTER JOIN bullets ON slides.id = bullets.slide_id WHERE slides.title LIKE ? Aggregates ========== .. code-block:: python select([bullets.c.slide_id, func.count(bullets.c.slide_id).label('total')], group_by=[bullets.c.slide_id]) .. code-block:: sql SELECT bullets.slide_id, count(bullets.slide_id) AS total FROM bullets GROUP BY bullets.slide_id Subqueries ========== .. code-block:: python cs = select([ bullets.c.slide_id, func.count(bullets.c.slide_id).label('total') ], group_by=[bullets.c.slide_id]).alias('totals') slides.select(and_(slides.c.id==cs.c.slide_id, cs.c.total>1)) .. code-block:: sql SELECT slides.id, slides.title FROM slides, (SELECT bullets.slide_id AS slide_id, count(bullets.slide_id) AS total FROM bullets GROUP BY bullets.slide_id) AS totals WHERE slides.id = totals.slide_id AND totals.total > ? And More... =========== - Supports all SQL operators and functions. - Joins, unions, correlated and uncorrelated subqueries, ... - Queries can be built up or modifified programaticaly SQLAlchemy as an ORM ==================== - An ORM is supplied with the distribution - Others are possible, and remixes of the default are available - Default ORM uses the "data mapper" model - "active record" available through Elixir ORM extension, others Mapping Is Simple ================= .. code-block:: python class Slide(object): def __repr__(self): return 'Slide("%s")' % self.title class Bullet(object): def __repr__(self): return 'Bullet("%s")' % self.text mapper(Slide, slides, properties={ 'bullets': relation(Bullet, cascade='all, delete-orphan') }) mapper(Bullet, bullets) Working With Instances ====================== .. code-block:: pycon >>> session = create_session() >>> s1 = session.query(Slide).get(1) >>> s1 Slide("slide 1") >>> s1.bullets [Bullet("foo"), Bullet("bar"), Bullet("baz")] >>> s1.title = 'new title!' >>> s1.bullets.pop() Bullet("baz") >>> session.flush() info: BEGIN info: UPDATE slides SET title=? WHERE slides.id = ? info: ['new title!', 1] info: DELETE FROM bullets WHERE bullets.id = ? info: [3] info: COMMIT Working With Instances ====================== You can mix SQL and objects .. code-block:: pycon >>> q = session.query(Bullet).filter(bullets.c.text.startswith('b')) >>> q >>> list(q) [Bullet("bar"), Bullet("baz")] So Much More ============ - Object mapping is very flexible - Work with any legacy schema or schemas - Multi-database - Rich relations, collections, cascade rules - Eager and lazy loading, whole object graphs or individual columns - Stored procedures and triggers, no problem - Optional! The SQLAlchemy ORM is decoupled from the base library. Summary ======= - The RDBMS is a powerful tool, use it - Object encapsulation is a powerful tool, use it - SQLAlchemy lets you find the right mix for your problem and performance requirements, without hacks Thanks! ======= .. class:: small :Date: July 10th, 2007 :Copyright: Jason Kirtland :License: Creative Commons Attribution-Share Alike 3.0 :Version: 1 .. image:: ui/cc.png