SQLAlchemy 0.3 Documentation

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

Installing a Database API

SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database, and includes support for the most popular databases. If you have one of the supported DBAPI implementations, you can proceed to the following section. Otherwise SQLite is an easy-to-use database to get started with, which works with plain files or in-memory databases.

SQLite is included with Python 2.5 and greater.

If you are working with Python 2.3 or 2.4, SQLite and the Python API for SQLite can be installed from the following packages:

• pysqlite - Python interface for SQLite
• SQLite library

Note that the SQLite library download is not required with Windows, as the Windows Pysqlite library already includes it linked in. Pysqlite and SQLite can also be installed on Linux or FreeBSD via pre-made packages or from sources.

Imports

To start connecting to databases and begin issuing queries, we want to import the base of SQLAlchemy's functionality, which is provided under the module name of sqlalchemy. For the purposes of this tutorial, we will import its full list of symbols into our own local namespace.

>>> from sqlalchemy import *

Note that importing using the * operator pulls all the names from sqlalchemy into the local module namespace, which in a real application can produce name conflicts. Therefore its recommended in practice to either import the individual symbols desired (i.e. from sqlalchemy import Table, Column) or to import under a distinct namespace (i.e. import sqlalchemy as sa).

Connecting to the Database

After our imports, the next thing we need is a handle to the desired database, represented by an Engine object. This object handles the business of managing connections and dealing with the specifics of a particular database. Below, we will make a SQLite connection to a file-based database called "tutorial.db".

>>> db = create_engine('sqlite:///tutorial.db')

Technically, the above statement did not make an actual connection to the sqlite database just yet. As soon as we begine working with the engine, it will start creating connections. In the case of SQLite, the tutorial.db file will actually be created at the moment it is first used, if the file does not exist already.

For full information on creating database engines, including those for SQLite and others, see Database Engines.

Defining Metadata, Binding to Engines

Configuring SQLAlchemy for your database consists of creating objects called Tables, each of which represent an actual table in the database. A collection of Table objects resides in a MetaData object which is essentially a table collection. We will create a handy form of MetaData that automatically connects to our Engine (connecting a schema object to an Engine is called binding):

>>> metadata = BoundMetaData(db)

An equivalent operation is to create the BoundMetaData object directly with an Engine URL, which calls the create_engine call for us:

>>> metadata = BoundMetaData('sqlite:///tutorial.db')

Now, when we tell "metadata" about the tables in our database, we can issue CREATE statements for those tables, as well as execute SQL statements derived from them, without needing to open or close any connections; that will be all done automatically.

Note that SQLALchemy fully supports the usage of explicit Connection objects for all SQL operations, which may be in conjunction with plain MetaData objects that are entirely unbound to any Engine, providing a more decoupled pattern that allows finer-grained control of connections than the "bound" approach this tutorial will present. For the purposes of this tutorial, we will stick with "bound" objects, as it allows us to focus more on SA's general concepts, leaving explicit connection management as a more advanced topic.

Creating a Table

With metadata as our established home for tables, lets make a Table for it:

>>> users_table = Table('users', metadata,
...     Column('user_id', Integer, primary_key=True),
...     Column('user_name', String(40)),
...     Column('password', String(10))
... )

As you might have guessed, we have just defined a table named users which has three columns: user_id (which is a primary key column), user_name and password. Currently it is just an object that doesn't necessarily correspond to an existing table in our database. To actually create the table, we use the create() method. To make it interesting, we will have SQLAlchemy echo the SQL statements it sends to the database, by setting the echo flag on the Engine associated with our BoundMetaData:

>>> metadata.engine.echo = True
>>> users_table.create() 
CREATE TABLE users (
    user_id INTEGER NOT NULL,
    user_name VARCHAR(40),
    password VARCHAR(10),
    PRIMARY KEY (user_id)
)
...

Alternatively, the users table might already exist (such as, if you're running examples from this tutorial for the second time), in which case you can just skip the create() method call. You can even skip defining the individual columns in the users table and ask SQLAlchemy to load its definition from the database:

>>> users_table = Table('users', metadata, autoload=True)
>>> list(users_table.columns)[0].name
'user_id'

Loading a table's columns from the database is called reflection. Documentation on table metadata, including reflection, is available in Database Meta Data.

Inserting Rows

Inserting is achieved via the insert() method, which defines a clause object (known as a ClauseElement) representing an INSERT statement:

>>> i = users_table.insert()
>>> i 
<sqlalchemy.sql._Insert object at 0x...>
>>> # the string form of the Insert object is a generic SQL representation
>>> print i
INSERT INTO users (user_id, user_name, password) VALUES (?, ?, ?)

Since we created this insert statement object from the users table which is bound to our Engine, the statement itself is also bound to the Engine, and supports executing itself. The execute() method of the clause object will compile the object into a string according to the underlying dialect of the Engine to which the statement is bound, and will then execute the resulting statement.

>>> # insert a single row
>>> i.execute(user_name='Mary', password='secure') 
INSERT INTO users (user_name, password) VALUES (?, ?)
['Mary', 'secure']
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>

>>> # insert multiple rows simultaneously
>>> i.execute({'user_name':'Tom'}, {'user_name':'Fred'}, {'user_name':'Harry'}) 
INSERT INTO users (user_name) VALUES (?)
[['Tom'], ['Fred'], ['Harry']]
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>

Note that the VALUES clause of each INSERT statement was automatically adjusted to correspond to the parameters sent to the execute() method. This is because the compilation step of a ClauseElement takes into account not just the constructed SQL object and the specifics of the type of database being used, but the execution parameters sent along as well.

When constructing clause objects, SQLAlchemy will bind all literal values into bind parameters. On the construction side, bind parameters are always treated as named parameters. At compilation time, SQLAlchemy will convert them into their proper format, based on the paramstyle of the underlying DBAPI. This works equally well for all named and positional bind parameter formats described in the DBAPI specification.

Documentation on inserting: Inserts.

Selecting

Let's check that the data we have put into users table is actually there. The procedure is analogous to the insert example above, except you now call the select() method off the users table:

>>> s = users_table.select()
>>> print s
SELECT users.user_id, users.user_name, users.password 
FROM users
>>> r = s.execute()
SELECT users.user_id, users.user_name, users.password 
FROM users
[]

This time, we won't ignore the return value of execute(). Its an instance of ResultProxy, which is a result-holding object that behaves very similarly to the cursor object one deals with directly with a database API:

>>> r 
<sqlalchemy.engine.base.ResultProxy object at 0x...>
>>> r.fetchone()
(1, u'Mary', u'secure')
>>> r.fetchall()
[(2, u'Tom', None), (3, u'Fred', None), (4, u'Harry', None)]

Query criterion for the select is specified using Python expressions, using the Column objects in the Table as a base. All expressions constructed from Column objects are themselves instances of ClauseElements, just like the Select, Insert, and Table objects themselves.

>>> r = users_table.select(users_table.c.user_name=='Harry').execute()
SELECT users.user_id, users.user_name, users.password 
FROM users 
WHERE users.user_name = ?
['Harry']
>>> row = r.fetchone()
>>> print row
(4, u'Harry', None)

Pretty much the full range of standard SQL operations are supported as constructed Python expressions, including joins, ordering, grouping, functions, correlated subqueries, unions, etc. Documentation on selecting: Simple Select.

Working with Rows

You can see that when we print out the rows returned by an execution result, it prints the rows as tuples. These rows in fact support both the list and dictionary interfaces. The dictionary interface allows the addressing of columns by string column name, or even the original Column object:

>>> row.keys()
['user_id', 'user_name', 'password']
>>> row['user_id'], row[1], row[users_table.c.password] 
(4, u'Harry', None)

Addressing the columns in a row based on the original Column object is especially handy, as it eliminates the need to work with literal column names altogether.

Result sets also support iteration. We'll show this with a slightly different form of select that allows you to specify the specific columns to be selected:

>>> for row in select([users_table.c.user_id, users_table.c.user_name]).execute(): 
...     print row
SELECT users.user_id, users.user_name
FROM users
[]
(1, u'Mary')
(2, u'Tom')
(3, u'Fred')
(4, u'Harry')

Table Relationships

Lets create a second table, email_addresses, which references the users table. To define the relationship between the two tables, we will use the ForeignKey construct. We will also issue the CREATE statement for the table:

>>> email_addresses_table = Table('email_addresses', metadata,
...     Column('address_id', Integer, primary_key=True),
...     Column('email_address', String(100), nullable=False),
...     Column('user_id', Integer, ForeignKey('users.user_id')))
>>> email_addresses_table.create() 
CREATE TABLE email_addresses (
    address_id INTEGER NOT NULL,
    email_address VARCHAR(100) NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (address_id),
    FOREIGN KEY(user_id) REFERENCES users (user_id)
)
...

Above, the email_addresses table is related to the users table via the ForeignKey('users.user_id'). The ForeignKey constructor can take a Column object or a string representing the table and column name. When using the string argument, the referenced table must exist within the same MetaData object; thats where it looks for the other table!

Next, lets put a few rows in:

>>> email_addresses_table.insert().execute(
...     {'email_address':'[email protected]', 'user_id':2},
...     {'email_address':'[email protected]', 'user_id':1}) 
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
[['[email protected]', 2], ['[email protected]', 1]]
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>

With two related tables, we can now construct a join amongst them using the join method:

>>> r = users_table.join(email_addresses_table).select().execute()
SELECT users.user_id, users.user_name, users.password, email_addresses.address_id, email_addresses.email_address, email_addresses.user_id 
FROM users JOIN email_addresses ON users.user_id = email_addresses.user_id
[]
>>> print [row for row in r]
[(1, u'Mary', u'secure', 2, u'[email protected]', 1), (2, u'Tom', None, 1, u'[email protected]', 2)]

The join method is also a standalone function in the sqlalchemy namespace. The join condition is figured out from the foreign keys of the Table objects given. The condition (also called the "ON clause") can be specified explicitly, such as in this example where we locate all users that used their email address as their password:

>>> print join(users_table, email_addresses_table, 
...     and_(users_table.c.user_id==email_addresses_table.c.user_id, 
...     users_table.c.password==email_addresses_table.c.email_address)
...     )
users JOIN email_addresses ON users.user_id = email_addresses.user_id AND users.password = email_addresses.email_address

Creating a Mapper

A Mapper is usually created once per Python class, and at its core primarily means to say, "objects of this class are to be stored as rows in this table". Lets create a class called User, which will represent a user object that is stored in our users table:

>>> class User(object):
...     def __repr__(self):
...        return "%s(%r,%r)" % (
...            self.__class__.__name__, self.user_name, self.password)

The class is a new style class (i.e. it extends object) and does not require a constructor (although one may be provided if desired). We just have one __repr__ method on it which will display basic information about the User. Note that the __repr__ method references the instance variables user_name and password which otherwise aren't defined. While we are free to explicitly define these attributes and treat them normally, this is optional; as SQLAlchemy's Mapper construct will manage them for us, since their names correspond to the names of columns in the users table. Lets create a mapper, and observe that these attributes are now defined:

>>> mapper(User, users_table) 
<sqlalchemy.orm.mapper.Mapper object at 0x...>
>>> u1 = User()
>>> print u1.user_name
None
>>> print u1.password
None

The mapper function returns a new instance of Mapper. As it is the first Mapper we have created for the User class, it is known as the classes' primary mapper. We generally don't need to hold onto the return value of the mapper function; SA can automatically locate this Mapper as needed when it deals with the User class.

Obtaining a Session

After you create a Mapper, all operations with that Mapper require the usage of an important object called a Session. All objects loaded or saved by the Mapper must be attached to a Session object, which represents a kind of "workspace" of objects that are loaded into memory. A particular object instance can only be attached to one Session at a time (but of course can be moved around or detached altogether).

By default, you have to create a Session object explicitly before you can load or save objects. Theres several ways to manage sessions, but the most straightforward is to just create one, which we will do by saying, create_session():

>>> session = create_session()
>>> session 
<sqlalchemy.orm.session.Session object at 0x...>

The Query Object

The Session has all kinds of methods on it to manage and inspect its collection of objects. The Session also provides an easy interface which can be used to query the database, by giving you an instance to a Query object corresponding to a particular Python class:

>>> query = session.query(User)
>>> print query.select_by(user_name='Harry')
SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id 
FROM users 
WHERE users.user_name = ? ORDER BY users.oid
['Harry']
[User(u'Harry',None)]

All querying for objects is performed via an instance of Query. The various select methods on an instance of Mapper also use an underlying Query object to perform the operation. A Query is always bound to a specific Session.

Lets turn off the database echoing for a moment, and try out a few methods on Query. Methods that end with the suffix _by primarily take keyword arguments which correspond to properties on the object. Other methods take ClauseElement objects, which are constructed by using Column objects inside of Python expressions, in the same way as we did with our SQL select example in the previous section of this tutorial. Using ClauseElement structures to query objects is more verbose but more flexible:

>>> metadata.engine.echo = False
>>> print query.select(User.c.user_id==3)
[User(u'Fred',None)]
>>> print query.get(2)
User(u'Tom',None)
>>> print query.get_by(user_name='Mary')
User(u'Mary',u'secure')
>>> print query.selectfirst(User.c.password==None)
User(u'Tom',None)
>>> print query.count()
4

Notice that our User class has a special attribute c attached to it. This 'c' represents the columns on the User's mapper's Table object. Saying User.c.user_name is synonymous with saying users_table.c.user_name, recalling that User is the Python class and users is our Table object.

Making Changes

With a little experience in loading objects, lets see what its like to make changes. First, lets create a new user "Ed". We do this by just constructing the new object. Then, we just add it to the session:

>>> ed = User()
>>> ed.user_name = 'Ed'
>>> ed.password = 'edspassword'
>>> session.save(ed)
>>> ed in session
True

Lets also make a few changes on some of the objects in the database. We will load them with our Query object, and then change some things.

>>> mary = query.get_by(user_name='Mary')
>>> harry = query.get_by(user_name='Harry')
>>> mary.password = 'marysnewpassword'
>>> harry.password = 'harrysnewpassword'

At the moment, nothing has been saved to the database; all of our changes are in memory only. What happens if some other part of the application also tries to load 'Mary' from the database and make some changes before we had a chance to save it ? Assuming that the same Session is used, loading 'Mary' from the database a second time will issue a second query in order locate the primary key of 'Mary', but will return the same object instance as the one already loaded. This behavior is due to an important property of the Session known as the identity map:

>>> mary2 = query.get_by(user_name='Mary')
>>> mary is mary2
True

With the identity map, a single Session can be relied upon to keep all loaded instances straight.

As far as the issue of the same object being modified in two different Sessions, that's an issue of concurrency detection; SQLAlchemy does some basic concurrency checks when saving objects, with the option for a stronger check using version ids. See advdatamapping_arguments for more details.

Saving

With a new user "ed" and some changes made on "Mary" and "Harry", lets also mark "Fred" as deleted:

>>> fred = query.get_by(user_name='Fred')
>>> session.delete(fred)

Then to send all of our changes to the database, we flush() the Session. Lets turn echo back on to see this happen!:

>>> metadata.engine.echo = True
>>> session.flush()
BEGIN
UPDATE users SET password=? WHERE users.user_id = ?
['marysnewpassword', 1]
UPDATE users SET password=? WHERE users.user_id = ?
['harrysnewpassword', 4]
INSERT INTO users (user_name, password) VALUES (?, ?)
['Ed', 'edspassword']
DELETE FROM users WHERE users.user_id = ?
[3]
COMMIT

Relationships

When our User object contains relationships to other kinds of information, such as a list of email addresses, we can indicate this by using a function when creating the Mapper called relation(). While there is a lot you can do with relations, we'll cover a simple one here. First, recall that our users table has a foreign key relationship to another table called email_addresses. A single row in email_addresses has a column user_id that references a row in the users table; since many rows in the email_addresses table can reference a single row in users, this is called a one to many relationship.

To illustrate this relationship, we will start with a new mapper configuration. Since our User class has a mapper assigned to it, we want to discard it and start over again. So we issue the clear_mappers() function first, which removes all mapping associations from classes:

>>> clear_mappers()

When removing mappers, it is usually best to remove all mappings at the same time, since mappers usually have relationships to each other which will become invalid if only part of the mapper collection is removed. In practice, a particular mapping setup will usually remain throughout the lifetime of an application. Clearing out the mappers and making new ones is a practice that is generally limited to writing mapper unit tests and experimenting from the console.

Next, we want to create a class/mapping that corresponds to the email_addresses table. We will create a new class Address which represents a single row in the email_addresses table, and a corresponding Mapper which will associate the Address class with the email_addresses table:

>>> class Address(object):
...     def __init__(self, email_address):
...         self.email_address = email_address
...     def __repr__(self):
...         return "%s(%r)" % (
...            self.__class__.__name__, self.email_address)    
>>> mapper(Address, email_addresses_table) 
<sqlalchemy.orm.mapper.Mapper object at 0x...>

We then create a mapper for the User class which contains a relationship to the Address class using the relation() function:

>>> mapper(User, users_table, properties={ 
...    'addresses':relation(Address)
... })
<sqlalchemy.orm.mapper.Mapper object at 0x...>

The relation() function takes either a class or a Mapper as its first argument, and has many options to further control its behavior. When this mapping relationship is used, each new User instance will contain an attribute called addresses. SQLAlchemy will automatically determine that this relationship is a one-to-many relationship, and will subsequently create addresses as a list. When a new User is created, this list will begin as empty.

The order in which the mapping definitions for User and Address is created is not significant. When the mapper() function is called, it creates an uncompiled mapping record corresponding to the given class/table combination. When the mappers are first used, the entire collection of mappers created up until that point will be compiled, which involves the establishment of class instrumentation as well as the resolution of all mapping relationships.

Lets try out this new mapping configuration, and see what we get for the email addresses already in the database. Since we have made a new mapping configuration, its best that we clear out our Session, which is currently holding onto every User object we have already loaded:

>>> session.clear()

We can then treat the addresses attribute on each User object like a regular list:

>>> mary = query.get_by(user_name='Mary') 
SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id 
FROM users 
WHERE users.user_name = ? ORDER BY users.oid 
LIMIT 1 OFFSET 0
['Mary']
>>> print [a for a in mary.addresses]
SELECT email_addresses.user_id AS email_addresses_user_id, email_addresses.address_id AS email_addresses_address_id, email_addresses.email_address AS email_addresses_email_address 
FROM email_addresses 
WHERE ? = email_addresses.user_id ORDER BY email_addresses.oid
[1]
[Address(u'[email protected]')]

Adding to the list is just as easy. New Address objects will be detected and saved when we flush the Session:

>>> mary.addresses.append(Address('[email protected]'))
>>> session.flush() 
BEGIN
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['[email protected]', 1]
COMMIT

Main documentation for using mappers: Data Mapping

Transactions

You may have noticed from the example above that when we say session.flush(), SQLAlchemy indicates the names BEGIN and COMMIT to indicate a transaction with the database. The flush() method, since it may execute many statements in a row, will automatically use a transaction in order to execute these instructions. But what if we want to use flush() inside of a larger transaction? This is performed via the SessionTransaction object, which we can establish using session.create_transaction(). Below, we will perform a more complicated SELECT statement, make several changes to our collection of users and email addresess, and then create a new user with two email addresses, within the context of a transaction. We will perform a flush() in the middle of it to write the changes we have so far, and then allow the remaining changes to be written when we finally commit() the transaction. We enclose our operations within a try/except block to ensure that resources are properly freed:

>>> transaction = session.create_transaction()
>>> try: 
...     (ed, harry, mary) = session.query(User).select(
...         User.c.user_name.in_('Ed', 'Harry', 'Mary'), order_by=User.c.user_name
...     )
...     del mary.addresses[1]
...     harry.addresses.append(Address('[email protected]'))
...     session.flush()
...     print "***flushed the session***"
...     fred = User()
...     fred.user_name = 'fred_again'
...     fred.addresses.append(Address('[email protected]'))
...     fred.addresses.append(Address('[email protected]'))
...     session.save(fred)
...     transaction.commit()
... except:
...     transaction.rollback()
...     raise
BEGIN
SELECT users.user_name AS users_user_name, users.password AS users_password, users.user_id AS users_user_id 
FROM users 
WHERE users.user_name IN (?, ?, ?) ORDER BY users.user_name
['Ed', 'Harry', 'Mary']
SELECT email_addresses.user_id AS email_addresses_user_id, email_addresses.address_id AS email_addresses_address_id, email_addresses.email_address AS email_addresses_email_address 
FROM email_addresses 
WHERE ? = email_addresses.user_id ORDER BY email_addresses.oid
[4]
UPDATE email_addresses SET user_id=? WHERE email_addresses.address_id = ?
[None, 3]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['[email protected]', 4]
***flushed the session***    
INSERT INTO users (user_name, password) VALUES (?, ?)
['fred_again', None]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['[email protected]', 6]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['[email protected]', 6]
COMMIT

Main documentation: Session / Unit of Work

Custom DBAPI keyword arguments

Custom arguments can be passed to the underlying DBAPI in three ways. String-based arguments can be passed directly from the URL string as query arguments:

db = create_engine('postgres://scott:tiger@localhost/test?argument1=foo&argument2=bar')

If SQLAlchemy's database connector is aware of a particular query argument, it may convert its type from string to its proper type.

create_engine also takes an argument connect_args which is an additional dictionary that will be passed to connect(). This can be used when arguments of a type other than string are required, and SQLAlchemy's database connector has no type conversion logic present for that parameter:

db = create_engine('postgres://scott:tiger@localhost/test', connect_args = {'argument1':17, 'argument2':'bar'})

The most customizable connection method of all is to pass a creator argument, which specifies a callable that returns a DBAPI connection:

def connect():
    return psycopg.connect(user='scott', host='localhost')

db = create_engine('postgres://', creator=connect)

Implicit Execution Strategies

The internal behavior of engine during implicit execution can be affected by the strategy keyword argument to create_engine(). Generally this setting can be left at its default value of plain. However, for the advanced user, the threadlocal option can provide the service of managing connections against the current thread in which they were pulled from the connection pool, where the same underlying DBAPI connection as well as a single database-level transaction can then be shared by many operations without explicitly passing a Connection or Transaction object around. It also may reduce the number of connections checked out from the connection pool at a given time.

Note that this setting does not affect the fact that Connection and Transaction objects are not threadsafe. The "threadlocal" strategy affects the selection of DBAPI connections which are pulled from the connection pool when a Connection object is created, but does not synchronize method access to the Connection or Transaction instances themselves, which are only proxy objects. It is instead intended that many Connection instances would share access to a single "connection" object that is referenced in relation to the current thread.

When strategy is set to plain, each implicit execution requests a unique connection from the connection pool, which is returned to the pool when the resulting ResultProxy falls out of scope (i.e. __del__() is called) or its close() method is called. If a second implicit execution occurs while the ResultProxy from the previous execution is still open, then a second connection is pulled from the pool.

When strategy is set to threadlocal, the Engine still checks out a connection which is closeable in the same manner via the ResultProxy, except the connection it checks out will be the same connection as one which is already checked out, assuming the operation is in the same thread. When all ResultProxy objects are closed in a particular thread, the connection is returned to the pool normally.

An additional feature of the threadlocal selection is that Transaction objects can be managed implicitly as well, by calling the begin(),commit() and rollback() methods off of the Engine, or by using Transaction objects from the thread-local connection.

It is crucial to note that the plain and threadlocal contexts do not impact the connect() method on the Engine. connect() always returns a unique connection. Implicit connections use a different method off of Engine for their operations called contextual_connect().

By default, every call to execute pulls a dedicated DBAPI connection from the connection pool:

db = create_engine('mysql://localhost/test', strategy='plain')

# execute one statement and receive results.  r1 now references a DBAPI connection resource.
r1 = db.execute("select * from table1")

# execute a second statement and receive results.  r2 now references a *second* DBAPI connection resource.
r2 = db.execute("select * from table2")
for row in r1:
    ...
for row in r2:
    ...
# release connection 1
r1.close()

# release connection 2
r2.close()

Using the "threadlocal" strategy, all calls to execute within the same thread will be guaranteed to use the same underlying DBAPI connection, which is only returned to the connection pool when all ResultProxy instances have been closed.

db = create_engine('mysql://localhost/test', strategy='threadlocal')

# execute one statement and receive results.  r1 now references a DBAPI connection resource.
r1 = db.execute("select * from table1")

# execute a second statement and receive results.  r2 now references the *same* resource as r1
r2 = db.execute("select * from table2")

for row in r1:
    ...
for row in r2:
    ...
# dereference r1.  the connection is still held by r2.
r1 = None

# dereference r2.  with no more references to the underlying connection resources, they
# are returned to the pool.
r2 = None

To get at the actual Connection object which is used by implicit executions, call the contextual_connection() method on Engine:

# threadlocal strategy
db = create_engine('mysql://localhost/test', strategy='threadlocal')

conn1 = db.contextual_connection()
conn2 = db.contextual_connection()

>>> conn1.connection is conn2.connection
True

When the plain strategy is used, the contextual_connection() method is synonymous with the connect() method; both return a distinct connection from the pool.

One programming pattern that the threadlocal strategy supports is transparent connection and transaction sharing.

db = create_engine('mysql://localhost/test', strategy='threadlocal')

def dosomethingimplicit():
    table1.execute("some sql")
    table1.execute("some other sql")

def dosomethingelse():
    table2.execute("some sql")
    conn = db.contextual_connection()
    # do stuff with conn
    conn.execute("some other sql")
    conn.close()

def dosomethingtransactional():
    conn = db.contextual_connection()
    trans = conn.begin()
     # do stuff
    trans.commit()

db.create_transaction()
try:
    dosomethingimplicit()
    dosomethingelse()
    dosomethingtransactional()
    db.commit()
except:
    db.rollback()

In the above example, the program calls three functions dosomethingimplicit(), dosomethingelse() and dosomethingtransactional(). In all three functions, either implicit execution is used, or an explicit Connection is used via the contextual_connection() method. This indicates that they all will share the same underlying dbapi connection as well as the same parent Transaction instance, which is created in the main body of the program via the call to db.create_transaction(). So while there are several calls that return "new" Transaction or Connection objects, in reality only one "real" connection is ever used, and there is only one transaction (i.e. one begin/commit pair) executed.

Binding MetaData to an Engine

A MetaData object can be associated with one or more Engine instances. This allows the MetaData and the elements within it to perform operations automatically, using the connection resources of that Engine. This includes being able to "reflect" the columns of tables, as well as to perform create and drop operations without needing to pass an Engine or Connection around. It also allows SQL constructs to be created which know how to execute themselves (called "implicit execution").

To bind MetaData to a single Engine, use BoundMetaData:

engine = create_engine('sqlite://', **kwargs)

# create BoundMetaData from an Engine
meta = BoundMetaData(engine)

# create the Engine and MetaData in one step
meta = BoundMetaData('postgres://db/', **kwargs)

Another form of MetaData exists which allows connecting to any number of engines, within the context of the current thread. This is DynamicMetaData:

meta = DynamicMetaData()

meta.connect(engine)    # connect to an existing Engine

meta.connect('mysql://user@host/dsn')   # create a new Engine and connect

DynamicMetaData is ideal for applications that need to use the same set of Tables for many different database connections in the same process, such as a CherryPy web application which handles multiple application instances in one process.

Using the global Metadata object

Some users prefer to create Table objects without specifying a MetaData object, having Tables scoped on an application-wide basis. For them the default_metadata object and the global_connect() function is supplied. default_metadata is simply an instance of DynamicMetaData that exists within the sqlalchemy namespace, and global_connect() is a synonym for default_metadata.connect(). Defining a Table that has no MetaData argument will automatically use this default metadata as follows:

from sqlalchemy import *

# a Table with just a name and its Columns
mytable = Table('mytable', 
    Column('col1', Integer, primary_key=True),
    Column('col2', String(40))
    )

# connect all the "anonymous" tables to a postgres uri in the current thread    
global_connect('postgres://foo:bar@lala/test')

# create all tables in the default metadata
default_metadata.create_all()

# the table is bound
mytable.insert().execute(col1=5, col2='some value')

Reflecting Tables

Once you have a BoundMetaData or a connected DynamicMetaData, you can create Table objects without specifying their columns, just their names, using autoload=True:

>>> messages = Table('messages', meta, autoload = True)
>>> [c.name for c in messages.columns]
['message_id', 'message_name', 'date']

At the moment the Table is constructed, it will query the database for the columns and constraints of the messages table.

Note that if a reflected table has a foreign key referencing another table, then the metadata for the related table will be loaded as well, even if it has not been defined by the application:

>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload = True)
>>> print shopping_cart_items.c.cart_id.table.name
shopping_carts

To get direct access to 'shopping_carts', simply instantiate it via the Table constructor. You'll get the same instance of the shopping cart Table as the one that is attached to shopping_cart_items:

>>> shopping_carts = Table('shopping_carts', meta)
>>> shopping_carts is shopping_cart_items.c.cart_id.table
True

This works because when the Table constructor is called for a particular name and MetaData object, if the table has already been created then the instance returned will be the same as the original. This is a singleton constructor:

>>> news_articles = Table('news', meta, 
... Column('article_id', Integer, primary_key = True),
... Column('url', String(250), nullable = False)
... )
>>> othertable = Table('news', meta)
>>> othertable is news_articles
True

>>> mytable = Table('mytable', meta,
... Column('id', Integer, primary_key=True),   # override reflected 'id' to have primary key
... Column('mydata', Unicode(50)),    # override reflected 'mydata' to be Unicode
... autoload=True)

Specifying the Schema Name

Some databases support the concept of multiple schemas. A Table can reference this by specifying the schema keyword argument:

financial_info = Table('financial_info', meta,
    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
    schema='remote_banks'
)

Within the MetaData collection, this table will be identified by the combination of financial_info and remote_banks. If another table called financial_info is referenced without the remote_banks schema, it will refer to a different Table. ForeignKey objects can reference columns in this table using the form remote_banks.financial_info.id.

ON UPDATE and ON DELETE

ON UPDATE and ON DELETE clauses to a table create are specified within the ForeignKeyConstraint object, using the onupdate and ondelete keyword arguments:

foobar = Table('foobar', meta,
    Column('id', Integer, primary_key=True),
    Column('lala', String(40)),
    ForeignKeyConstraint(['lala'],['hoho.lala'], onupdate="CASCADE", ondelete="CASCADE"))

Note that these clauses are not supported on SQLite, and require InnoDB tables when used with MySQL. They may also not be supported on other databases.

Enabling Table / Column Quoting

Feature Status: Alpha Implementation

Many table, schema, or column names require quoting to be enabled. Reasons for this include names that are the same as a database reserved word, or for identifiers that use MixedCase, where the database would normally "fold" the case convention into lower or uppercase (such as Postgres). SQLAlchemy will attempt to automatically determine when quoting should be used. It will determine a value for every identifier name called case_sensitive, which defaults to False if the identifer name uses no uppercase letters, or True otherwise. This flag may be explicitly set on any schema item as well (schema items include Table, Column, MetaData, Sequence, etc.) to override this default setting, where objects will inherit the setting from an enclosing object if not explicitly overridden.

When case_sensitive is True, the dialect will do what it has to in order for the database to recognize the casing. For Postgres and Oracle, this means using quoted identifiers.

Identifiers that match known SQL reserved words (such as "asc", "union", etc.) will also be quoted according to the dialect's quoting convention regardless of the case_sensitive setting.

To force quoting for an identifier, set the "quote=True" flag on Column or Table, as well as the quote_schema=True flag for Table.

table2 = Table('WorstCase2', metadata,
    # desc is a reserved word, which will be quoted.
    Column('desc', Integer, primary_key=True),

    # if using a reserved word which SQLAlchemy doesn't know about,
    # specify quote=True
    Column('some_reserved_word', Integer, quote=True, primary_key=True),

    # MixedCase uses a mixed case convention. 
    # it will be automatically quoted since it is case sensitive
    Column('MixedCase', Integer),

    # Union is both a reserved word and mixed case
    Column('Union', Integer),

    # normal_column doesnt require quoting
    Column('normal_column', String(30)))

# to use tables where case_sensitive is False by default regardless
# of idenfifier casings, set "case_sensitive" to false at any level
# (or true to force case sensitive for lowercase identifiers as well)
lowercase_metadata = MetaData(case_sensitive=False)

Other Options

Tables may support database-specific options, such as MySQL's engine option that can specify "MyISAM", "InnoDB", and other backends for the table:

addresses = Table('engine_email_addresses', meta,
    Column('address_id', Integer, primary_key = True),
    Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
    Column('email_address', String(20)),
    mysql_engine='InnoDB'
)

Pre-Executed Insert Defaults

A basic default is most easily specified by the "default" keyword argument to Column. This defines a value, function, or SQL expression that will be pre-executed to produce the new value, before the row is inserted:

# a function to create primary key ids
i = 0
def mydefault():
    global i
    i += 1
    return i

t = Table("mytable", meta, 
    # function-based default
    Column('id', Integer, primary_key=True, default=mydefault),

    # a scalar default
    Column('key', String(10), default="default")
)

The "default" keyword can also take SQL expressions, including select statements or direct function calls:

t = Table("mytable", meta, 
    Column('id', Integer, primary_key=True),

    # define 'create_date' to default to now()
    Column('create_date', DateTime, default=func.now()),

    # define 'key' to pull its default from the 'keyvalues' table
    Column('key', String(20), default=keyvalues.select(keyvalues.c.type='type1', limit=1))
    )

The "default" keyword argument is shorthand for using a ColumnDefault object in a column definition. This syntax is optional, but is required for other types of defaults, futher described below:

Column('mycolumn', String(30), ColumnDefault(func.get_data()))

Pre-Executed OnUpdate Defaults

Similar to an on-insert default is an on-update default, which is most easily specified by the "onupdate" keyword to Column, which also can be a constant, plain Python function or SQL expression:

t = Table("mytable", meta, 
    Column('id', Integer, primary_key=True),

    # define 'last_updated' to be populated with current_timestamp (the ANSI-SQL version of now())
    Column('last_updated', DateTime, onupdate=func.current_timestamp()),
)

To use an explicit ColumnDefault object to specify an on-update, use the "for_update" keyword argument:

Column('mycolumn', String(30), ColumnDefault(func.get_data(), for_update=True))

Inline Default Execution: PassiveDefault

A PassiveDefault indicates an column default that is executed upon INSERT by the database. This construct is used to specify a SQL function that will be specified as "DEFAULT" when creating tables.

t = Table('test', meta, 
    Column('mycolumn', DateTime, PassiveDefault(text("sysdate")))
)

A create call for the above table will produce:

CREATE TABLE test (
    mycolumn datetime default sysdate
)

PassiveDefault also sends a message to the Engine that data is available after an insert. The object-relational mapper system uses this information to post-fetch rows after the insert, so that instances can be refreshed with the new data. Below is a simplified version:

# table with passive defaults
mytable = Table('mytable', engine, 
    Column('my_id', Integer, primary_key=True),

    # an on-insert database-side default
    Column('data1', Integer, PassiveDefault(text("d1_func()"))),
)
# insert a row
r = mytable.insert().execute(name='fred')

# check the result: were there defaults fired off on that row ?
if r.lastrow_has_defaults():
    # postfetch the row based on primary key.
    # this only works for a table with primary key columns defined
    primary_key = r.last_inserted_ids()
    row = table.select(table.c.id == primary_key[0])

When Tables are reflected from the database using autoload=True, any DEFAULT values set on the columns will be reflected in the Table object as PassiveDefault instances.

Defining Sequences

A table with a sequence looks like:

table = Table("cartitems", meta, 
    Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
    Column("description", String(40)),
    Column("createdate", DateTime())
)

The Sequence is used with Postgres or Oracle to indicate the name of a database sequence that will be used to create default values for a column. When a table with a Sequence on a column is created in the database by SQLAlchemy, the database sequence object is also created. Similarly, the database sequence is dropped when the table is dropped. Sequences are typically used with primary key columns. When using Postgres, if an integer primary key column defines no explicit Sequence or other default method, SQLAlchemy will create the column with the SERIAL keyword, and will pre-execute a sequence named "tablename_columnname_seq" in order to retrieve new primary key values, if they were not otherwise explicitly stated. Oracle, which has no "auto-increment" keyword, requires that a Sequence be created for a table if automatic primary key generation is desired.

A Sequence object can be defined on a Table that is then used for a non-sequence-supporting database. In that case, the Sequence object is simply ignored. Note that a Sequence object is entirely optional for all databases except Oracle, as other databases offer options for auto-creating primary key values, such as AUTOINCREMENT, SERIAL, etc. SQLAlchemy will use these default methods for creating primary key values if no Sequence is present on the table metadata.

A sequence can also be specified with optional=True which indicates the Sequence should only be used on a database that requires an explicit sequence, and not those that supply some other method of providing integer values. At the moment, it essentially means "use this sequence only with Oracle and not Postgres".

UNIQUE Constraint

Unique constraints can be created anonymously on a single column using the unique keyword on Column. Explicitly named unique constraints and/or those with multiple columns are created via the UniqueConstraint table-level construct.

meta = MetaData()
mytable = Table('mytable', meta,

    # per-column anonymous unique constraint
    Column('col1', Integer, unique=True),

    Column('col2', Integer),
    Column('col3', Integer),

    # explicit/composite unique constraint.  'name' is optional.
    UniqueConstraint('col2', 'col3', name='uix_1')
    )

CHECK Constraint

Check constraints can be named or unnamed and can be created at the Column or Table level, using the CheckConstraint construct. The text of the check constraint is passed directly through to the database, so there is limited "database independent" behavior. Column level check constraints generally should only refer to the column to which they are placed, while table level constraints can refer to any columns in the table.

Note that some databases do not actively support check constraints such as MySQL and sqlite.

meta = MetaData()
mytable = Table('mytable', meta,

    # per-column CHECK constraint
    Column('col1', Integer, CheckConstraint('col1>5')),

    Column('col2', Integer),
    Column('col3', Integer),

    # table level CHECK constraint.  'name' is optional.
    CheckConstraint('col2 > col3 + 5', name='check1')
    )

Indexes

Indexes can be created anonymously (using an auto-generated name "ix_") for a single column using the inline index keyword on Column, which also modifies the usage of unique to apply the uniqueness to the index itself, instead of adding a separate UNIQUE constraint. For indexes with specific names or which encompass more than one column, use the Index construct, which requires a name.

Note that the Index construct is created externally to the table which it corresponds, using Column objects and not strings.

meta = MetaData()
mytable = Table('mytable', meta,
    # an indexed column, with index "ix_mytable_col1"
    Column('col1', Integer, index=True),

    # a uniquely indexed column with index "ix_mytable_col2"
    Column('col2', Integer, index=True, unique=True),

    Column('col3', Integer),
    Column('col4', Integer),

    Column('col5', Integer),
    Column('col6', Integer),
    )

# place an index on col3, col4
Index('idx_col34', mytable.c.col3, mytable.c.col4)

# place a unique index on col5, col6
Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)

The Index objects will be created along with the CREATE statements for the table itself. An index can also be created on its own independently of the table:

# create a table
sometable.create()

# define an index
i = Index('someindex', sometable.c.col5)

# create the index, will use the table's connectable, or specify the connectable keyword argument
i.create()

Explicit Execution

As mentioned above, ClauseElement structures can also be executed with a Connection object explicitly:

engine = create_engine('sqlite:///myfile.db')
conn = engine.connect()

s = users.select()
sqlresult = conn.execute(s)

conn.close()

Binding ClauseElements to Engines

For queries that don't contain any tables, ClauseElements that represent a fully executeable statement support an engine keyword parameter which can bind the object to an Engine, thereby allowing implicit execution:

# select a literal
sqlselect(["current_time"], engine=myengine).execute()

# select a function
sqlselect([func.now()], engine=db).execute()

Getting Results

The object returned by execute() is a sqlalchemy.engine.ResultProxy object, which acts much like a DBAPI cursor object in the context of a result set, except that the rows returned can address their columns by ordinal position, column name, or even column object:

# select rows, get resulting ResultProxy object
sqlresult = users.select().execute()

# get one row
row = result.fetchone()

# get the 'user_id' column via integer index:
user_id = row[0]

# or column name
user_name = row['user_name']

# or column object
password = row[users.c.password]

# or column accessor
password = row.password

# ResultProxy object also supports fetchall()
rows = result.fetchall()

# or get the underlying DBAPI cursor object
cursor = result.cursor

# close the result.  If the statement was implicitly executed 
# (i.e. without an explicit Connection), this will
# return the underlying connection resources back to 
# the connection pool.  de-referencing the result
# will also have the same effect.  if an explicit Connection was 
# used, then close() just closes the underlying cursor object.
result.close()

Using Column Labels

A common need when writing statements that reference multiple tables is to create labels for columns, thereby separating columns from different tables with the same name. The Select construct supports automatic generation of column labels via the use_labels=True parameter:

sqlc = select([users, addresses], 
users.c.user_id==addresses.c.address_id, 
use_labels=True).execute()

The table name part of the label is affected if you use a construct such as a table alias:

person = users.alias('person')
sqlc = select([person, addresses], 
    person.c.user_id==addresses.c.address_id, 
    use_labels=True).execute()

Labels are also generated in such a way as to never go beyond 30 characters. Most databases support a limit on the length of symbols, such as Postgres, and particularly Oracle which has a rather short limit of 30:

long_named_table = users.alias('this_is_the_person_table')
sqlc = select([long_named_table], use_labels=True).execute()