Essential SQLAlchemy

An Overview of SQLAlchemy

Rick Copeland
Author, Essential SQLAlchemy
Predictix, LLC

   
 SQLAlchemy Philosophy
 SQL databases behave less like object 
collections the more size and performance start 
to matter
 Object collections behave less like tables and 
rows the more abstraction starts to matter 
 SQLAlchemy aims to accommodate both of 
these principles
From [Link]

   
SQLAlchemy Philosophy (abridged)

Let tables be tables

Let objects be objects

(my book is short)

   
 SQLAlchemy Architecture
Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 SQLAlchemy Architecture
(Interesting parts)
Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 SQLAlchemy “Plumbing”

Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 SQLAlchemy “Plumbing”
 Connection Pooling
 Manage a pool of long­lived connections to the database
 Different strategies available (one connection per thread, 
one per statement, one per database)
 Usually “just works” without intervention
 Dialect / Execution
 Provides a database independence layer
 Postgres, SQLite, MySQL, Oracle, MS­SQL, Firebird, 
Informix, .... (more?)

   
 SQLAlchemy “Plumbing”
 Types
 Support for a variety of common SQL types
 Support for driver­specific types (at the cost of 
portability)
 TypeEngines convert Python values to SQL values and 
vice­versa
 Custom TypeEngines easy to implement

   
 Schema Management

Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 Schema Management

MetaData

Table

Column Index Constraint

   
 Schema Management
 For “blue sky” development, you can define your 
schema in Python and stay in Python
 For “legacy” development, you can tell tables (or 
even the entire MetaData object!) to autoload from 
the database
 The MetaData, Tables, and Columns provide 
convenient proxy objects for SQL constructs used in 
the SQL Expression Language
 Foreign key relationships let SQLAlchemy 
automatically create join conditions for you
   
 Schema Management
 Simple syntax for simple constraints
 Column('col', Integer, index=True, unique=True)
 Column('col', None, ForeignKey('[Link]'))
 Default Values
 Column('col', Integer, default=None)
 Column('col', DateTime, default=[Link])
 Column('col', Integer, default=select(...))
 Column('col', DateTime, PassiveDefault(text('sysdate')))

   
 Schema Management

Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 SQL Expression Language
 DDL (Data Definition Language) Statements 
 users_table.create() # table defined with MetaData
 users_table.drop()
 metadata.create_all()
 metadata.drop_all()
 DML (Data Manipulation Language)
 s_ins = [Link](values=dict(name='rick', pass='foo'))  
 s_del = [Link](whereclause=[Link]=='rick')
 s_upd = [Link](values=dict(age=[Link] + 
timedelta(days=1)))
   
 SQL Expression Language
 Executing DML Statements
 s_ins.execute()
 s_ins.execute(a=5, b=6)
 [Link](s_ins, [ dict(a=1,b=1), dict(a=1,b=2)...])

   
 SQL Expression Language
 DQL (Data Query Language) statements
 [Link]()
 select([users.c.user_name])
 [Link](users.c.user_name=='rick')
 select([users, addresses], [Link]==[Link])
 s = text('''SELECT [Link] FROM users WHERE 
[Link] LIKE :x''')
 [Link](x='rick')
 [Link](addresses).select()
 [Link](addresses).select()
   
 Schema Management

Object Relational Mapper (ORM)

SQL Expression Language

Schema Management
Dialect / Execution
(MetaData)

Connection Pooling Types

   
 ORM Design
 Basic idea: use the database as a persistence layer 
for Python objects
 Tables are classes, rows are instances
 Relationships modeled as properties

   
 ORM Design

Active Record – wrap every table in a class

   
 ORM Design

Data Mapper – use a mapper to connect tables to classes

   
 The Session
 Unlike other ORMs (at least SQLObject), 
SQLAlchemy uses the Unit of Work (UoW) pattern 
to collect changes to your objects as you make them
 At some point, these changes are flushed to the 
database
 This is a Good Thing
 Less chattiness with the DB server
 Sometimes the DB server can amortize compilation 
overhead for many updates

   
 Simple Mapping
 Example:
 users = Table('users', metadata, Column(....))
 class User(object): pass
 mapper(User, users)
 All columns are mapped as properties

   
 Mapping Relations
 users = Table('users', metadata, Column('id', ...))
 addresses = Table('addresses', metadata,
 Column('id', ...),
 Column('user_id', None, ForeignKey('[Link]')...)
 class User(object): pass
 class Address(object): pass
 mapper(User, users, properties=dict(
 addresses=relation(Address, backref='user')))
 mapper(Address, addresses)
   
 Cool advanced features I won't go 
over in detail
 Eager / lazy loaded relations
 Deferred column loading
 Custom collection types
 Database partitioning
 Vertical (some tables in DB1, some in DB2)
 Horizontal (sharding ­ one table partitioned)
 Mapping classes against arbitrary SELECT 
statements
 Inheritance mapping
   
 Questions?