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.
back to section topMetaData
and Table
objects; reading database schemas into your application, creating and dropping tables, constraints, defaults, sequences, indexes.
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
SQLAlchemy is designed to operate with a DB-API implementation built for a particular database, and includes support for the most popular databases:
This documentation covers SQLAlchemy version 0.4. If you're working on a system that already has SQLAlchemy installed, check the version from your Python prompt like this:
>>> import sqlalchemy >>> sqlalchemy.__version__ 0.4.0
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.
All symbols related to the SQLAlchemy Object Relational Mapper, i.e. names like mapper()
, relation()
, backref()
, create_session()
synonym()
, eagerload()
, etc. are now only in the sqlalchemy.orm
package, and not in sqlalchemy
. So if you were previously importing everything on an asterisk:
from sqlalchemy import *
You should now import separately from orm:
from sqlalchemy import * from sqlalchemy.orm import *
Or more commonly, just pull in the names you'll need:
from sqlalchemy import create_engine, MetaData, Table, Column, types from sqlalchemy.orm import mapper, relation, backref, create_session
The BoundMetaData
name is removed. Now, you just use MetaData
. Additionally, the engine
parameter/attribute is now called bind
, and connect()
is deprecated:
# plain metadata meta = MetaData() # metadata bound to an engine meta = MetaData(engine) # bind metadata to an engine later meta.bind = engine
Additionally, DynamicMetaData
is now known as ThreadLocalMetaData
.
The methods correlate()
, order_by()
, and group_by()
on the select()
construct now return a new select object, and do not change the original one. Additionally, the generative methods where()
, column()
, distinct()
, and several others have been added:
s = table.select().order_by(table.c.id).where(table.c.x==7) result = engine.execute(s)
If you've been using the collection_class
option on mapper()
, the requirements for instrumented collections have changed. For an overview, see Alternate Collection Implementations.
This is for create/drop statements, sessions, SQL constructs, metadatas:
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)
This mostly applies to SQL constructs where you pass a type in:
s = select([mytable], mytable.c.x=bindparam(y, type_=DateTime)) func.now(type_=DateTime)