SQLAlchemy 0.4 Documentation

Overview

The SQLAlchemy SQL Toolkit and Object Relational Mapper is a comprehensive set of tools for working with databases and Python. It has several distinct areas of functionality which can be used individually or combined together. Its major API components, all public-facing, are illustrated below:

           +-----------------------------------------------------------+
           |             Object Relational Mapper (ORM)                |
           |                [tutorial]    [docs]                       |
           +-----------------------------------------------------------+
           +---------+ +------------------------------------+ +--------+
           |         | |       SQL Expression Language      | |        |
           |         | |        [tutorial]  [docs]          | |        |
           |         | +------------------------------------+ |        |
           |         +-----------------------+ +--------------+        |
           |        Dialect/Execution        | |    Schema Management  |
           |              [docs]             | |        [docs]         |
           +---------------------------------+ +-----------------------+
           +----------------------+ +----------------------------------+
           |  Connection Pooling  | |              Types               |
           |        [docs]        | |              [docs]              |
           +----------------------+ +----------------------------------+

Above, the two most significant front-facing portions of SQLAlchemy are the Object Relational Mapper and the SQL Expression Language. These are two separate toolkits, one building off the other. SQL Expressions can be used independently of the ORM. When using the ORM, the SQL Expression language is used to establish object-relational configurations as well as in querying.

Tutorials

• Object Relational Tutorial - This describes the richest feature of SQLAlchemy, its object relational mapper. If you want to work with higher-level SQL which is constructed automatically for you, as well as management of Python objects, proceed to this tutorial.
• SQL Expression Tutorial - The core of SQLAlchemy is its SQL expression language. The SQL Expression Language is a toolkit all its own, independent of the ORM package, which can be used to construct manipulable SQL expressions which can be programmatically constructed, modified, and executed, returning cursor-like result sets. It's a lot more lightweight than the ORM and is appropriate for higher scaling SQL operations. It's also heavily present within the ORM's public facing API, so advanced ORM users will want to master this language as well.

Reference Documentation

• Datamapping - A comprehensive walkthrough of major ORM patterns and techniques.
• Session - A detailed description of SQLAlchemy's Session object
• Engines - Describes SQLAlchemy's database-connection facilities, including connection documentation and working with connections and transactions.
• Connection Pools - Further detail about SQLAlchemy's connection pool library.
• Metadata - All about schema management using MetaData and Table objects; reading database schemas into your application, creating and dropping tables, constraints, defaults, sequences, indexes.
• Types - Datatypes included with SQLAlchemy, their functions, as well as how to create your own types.
• Plugins - Included addons for SQLAlchemy

Installing SQLAlchemy

Installing SQLAlchemy from scratch is most easily achieved with setuptools. (setuptools installation). Just run this from the command-line:

# easy_install SQLAlchemy

This command will download the latest version of SQLAlchemy from the Python Cheese Shop and install it to your system.

Otherwise, you can install from the distribution using the setup.py script:

# python setup.py install

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
0.4.0

0.3 to 0.4 Migration

From version 0.3 to version 0.4 of SQLAlchemy, some conventions have changed. Most of these conventions are available in the most recent releases of the 0.3 series starting with version 0.3.9, so that you can make a 0.3 application compatible with 0.4 in most cases.

This section will detail only those things that have changed in a backwards-incompatible manner. For a full overview of everything that's new and changed, see WhatsNewIn04.

from sqlalchemy import *

from sqlalchemy import *
from sqlalchemy.orm import *

from sqlalchemy import create_engine, MetaData, Table, Column, types
from sqlalchemy.orm import mapper, relation, backref, create_session

# plain metadata
meta = MetaData()

# metadata bound to an engine
meta = MetaData(engine)

# bind metadata to an engine later
meta.bind = engine

s = table.select().order_by(table.c.id).where(table.c.x==7)
result = engine.execute(s)

myengine = create_engine('sqlite://')

meta = MetaData(myengine)

meta2 = MetaData()
meta2.bind = myengine

session = create_session(bind=myengine)

statement = select([table], bind=myengine)

meta.create_all(bind=myengine)

s = select([mytable], mytable.c.x=bindparam(y, type_=DateTime))

func.now(type_=DateTime)