SQLAlchemy 0.3 Documentation

Multiple Pages | One Page

Version: 0.3.10 Last Updated: 07/29/07 00:15:33

Table of Contents

(view full table)

Table of Contents: Full

(view brief table)

Tutorial

Installation

Installing SQLAlchemy

Installing a Database API

Getting Started

Checking the Version

Imports

Connecting to the Database

SQLAlchemy is Two Libraries in One

Working with Database Objects

Defining Metadata, Binding to Engines

Creating a Table

Inserting Rows

Selecting

Working with Rows

Table Relationships

Working with Object Mappers

Creating a Mapper

Obtaining a Session

The Query Object

Making Changes

Saving

Relationships

Transactions

Next Steps

Database Engines

Supported Databases

create_engine() URL Arguments

Custom DBAPI connect() arguments

Database Engine Options

More On Connections

Using Transactions with Connection

Understanding Autocommit

Connectionless Execution, Implicit Execution

Using the Threadlocal Execution Strategy

Configuring Logging

Database Meta Data

Describing Databases with MetaData

Binding MetaData to an Engine or Connection

Reflecting Tables

Overriding Reflected Columns

Specifying the Schema Name

ON UPDATE and ON DELETE

Other Options

Creating and Dropping Database Tables

Column Defaults and OnUpdates

Pre-Executed Insert Defaults

Pre-Executed OnUpdate Defaults

Inline Default Execution: PassiveDefault

The Catch: Postgres Primary Key Defaults always Pre-Execute

Defining Sequences

Defining Constraints and Indexes

UNIQUE Constraint

CHECK Constraint

Indexes

Adapting Tables to Alternate Metadata

Constructing SQL Queries via Python Expressions

Simple Select

Explicit Execution

Binding ClauseElements to Engines

Getting Results

Using Column Labels

Table/Column Specification

WHERE Clause

Operators

Functions

Literals

Order By

DISTINCT, LIMIT and OFFSET

Inner and Outer Joins

Table Aliases

Subqueries

Scalar Column Subqueries

Correlated Subqueries

EXISTS Clauses

Unions

Custom Bind Parameters

Precompiling a Query

Literal Text Blocks

Using Bind Parameters in Text Blocks

Building Select Objects

Inserts

Updates

Correlated Updates

Deletes

Data Mapping

Basic Data Mapping

Synopsis

The Query Object

Loading by Primary Key

Column Objects Available via their Mapped Class

Saving Objects

Defining and Using Relationships

One to Many

Lifecycle Relations

Backreferences

Querying with Joins

Loading Relationships

Eager Loading

Using Options to Change the Loading Strategy

More Relationships

One to One/Many to One

Many to Many

Association Object

Session / Unit of Work

Overview

Object States

Acquiring a Session

Introduction to the Identity Map

Whats Changed ?

The Session API

query()

get()

load()

save()

flush()

Notes on Flush

close()

delete()

clear()

refresh() / expire()

expunge()

bind_mapper() / bind_table()

update()

save_or_update()

merge()

Cascade rules

SessionTransaction

Using SQL with SessionTransaction

Using Engine-level Transactions with Sessions

Advanced Data Mapping

More On Mapper Properties

Overriding Column Names

Overriding Properties

Custom List Classes

Custom Join Conditions

Lazy/Eager Joins Multiple Times to One Table

Deferred Column Loading

Working with Large Collections

Relation Options

Controlling Ordering

Limiting Rows Combined with Eager Loads

Mapping a Class with Table Inheritance

Single Table Inheritance

Concrete Table Inheritance

Joined Table Inheritance

Mapping a Class against Multiple Tables

Mapping a Class against Arbitrary Selects

Multiple Mappers for One Class

Self Referential Mappers

Statement and Result-Set ORM Queries

Combining Eager Loads with Statement/Result Set Queries

Mapper Keyword Arguments

Extending Mapper

The Types System

Built-in Types

Dialect Specific Types

Creating your Own Types

Connection Pooling

Establishing a Transparent Connection Pool

Connection Pool Configuration

Custom Pool Construction

Plugins

SessionContext

Using SessionContextExt

SelectResults

assignmapper

associationproxy

orderinglist

threadlocal

ActiveMapper

SqlSoup

ProxyEngine

Generated Documentation

module sqlalchemy.sql

Module Functions

alias()

and_()

asc()

between()

between_()

bindparam()

case()

cast()

column()

delete()

desc()

distinct()

except_()

except_all()

exists()

extract()

insert()

intersect()

intersect_all()

is_column()

join()

label()

literal()

literal_column()

not_()

null()

or_()

outerjoin()

select()

subquery()

table()

text()

union()

union_all()

update()

class AbstractDialect(object)

class Alias(FromClause)

class _BinaryExpression(ColumnElement)

class _BindParamClause(ClauseElement,_CompareMixin)

class _CalculatedClause(ColumnElement)

class _Cast(ColumnElement)

class ClauseElement(object)

class ClauseList(ClauseElement)

class ClauseParameters(object)

class ClauseVisitor(object)

class _ColumnClause(ColumnElement)

class ColumnCollection(OrderedProperties)

class ColumnElement(Selectable,_CompareMixin)

class ColumnSet(OrderedSet)

class _CompareMixin(object)

class Compiled(ClauseVisitor)

class CompoundSelect(_SelectBaseMixin,FromClause)

class _Delete(_UpdateBase)

class Executor(object)

class _Exists(_UnaryExpression)

class FromClause(Selectable)

class _Function(_CalculatedClause,FromClause)

class _FunctionGenerator(object)

class _Grouping(ColumnElement)

class _Insert(_UpdateBase)

class Join(FromClause)

class _Label(ColumnElement)

class LoggingClauseVisitor(ClauseVisitor)

class NoColumnVisitor(ClauseVisitor)

class _Null(ColumnElement)

class Select(_SelectBaseMixin,FromClause)

class _SelectBaseMixin(object)

class Selectable(ClauseElement)

class TableClause(FromClause)

class _TextClause(ClauseElement)

class _TypeClause(ClauseElement)

class _UnaryExpression(ColumnElement)

class _Update(_UpdateBase)

class _UpdateBase(ClauseElement)

module sqlalchemy.schema

Module Functions

DynamicMetaData()

class BoundMetaData(MetaData)

class CheckConstraint(Constraint)

class Column(SchemaItem,_ColumnClause)

class ColumnDefault(DefaultGenerator)

class Constraint(SchemaItem)

class DefaultGenerator(SchemaItem)

class ForeignKey(SchemaItem)

class ForeignKeyConstraint(Constraint)

class Index(SchemaItem)

class MetaData(SchemaItem)

class PassiveDefault(DefaultGenerator)

class PrimaryKeyConstraint(Constraint)

class SchemaItem(object)

class SchemaVisitor(ClauseVisitor)

class Sequence(DefaultGenerator)

class Table(SchemaItem,TableClause)

class ThreadLocalMetaData(MetaData)

class UniqueConstraint(Constraint)

module sqlalchemy.types

class BLOB(Binary)

class BOOLEAN(Boolean)

class Binary(TypeEngine)

class Boolean(TypeEngine)

class CHAR(String)

class CLOB(TEXT)

class DATE(Date)

class DATETIME(DateTime)

class DECIMAL(Numeric)

class Date(TypeEngine)

class DateTime(TypeEngine)

class FLOAT(Float)

class Float(Numeric)

class INT(Integer)

class Integer(TypeEngine)

class Interval(TypeDecorator)

class NCHAR(Unicode)

class NullTypeEngine(TypeEngine)

class Numeric(TypeEngine)

class PickleType(MutableType,TypeDecorator)

class SMALLINT(SmallInteger)

class SmallInteger(Integer)

class String(TypeEngine)

class TEXT(String)

class TIME(Time)

class TIMESTAMP(DateTime)

class Time(TypeEngine)

class TypeDecorator(AbstractType)

class TypeEngine(AbstractType)

class Unicode(String)

class VARCHAR(String)

module sqlalchemy.engine

Module Functions

create_engine()

engine_descriptors()

class BufferedColumnResultProxy(ResultProxy)

class BufferedRowResultProxy(ResultProxy)

class Connectable(Executor)

class Connection(Connectable)

class ConnectionProvider(object)

class DefaultRunner(SchemaVisitor)

class Dialect(AbstractDialect)

class Engine(Connectable)

class ExecutionContext(object)

class ResultProxy(object)

class RowProxy(object)

class SchemaIterator(SchemaVisitor)

class Transaction(object)

module sqlalchemy.engine.url

Module Functions

make_url()

class URL(object)

module sqlalchemy.engine.strategies

class DefaultEngineStrategy(EngineStrategy)

class EngineStrategy(object)

class MockEngineStrategy(EngineStrategy)

class PlainEngineStrategy(DefaultEngineStrategy)

class ThreadLocalEngineStrategy(DefaultEngineStrategy)

module sqlalchemy.engine.default

class DefaultDialect(Dialect)

class DefaultExecutionContext(ExecutionContext)

class PoolConnectionProvider(ConnectionProvider)

module sqlalchemy.engine.threadlocal

class TLConnection(Connection)

class TLEngine(Engine)

class TLSession(object)

class TLTransaction(Transaction)

class TLocalConnectionProvider(PoolConnectionProvider)

module sqlalchemy.ansisql

class ANSICompiler(Compiled)

class ANSIDefaultRunner(DefaultRunner)

class ANSIDialect(DefaultDialect)

class ANSIIdentifierPreparer(object)

class ANSISchemaBase(SchemaIterator)

class ANSISchemaDropper(ANSISchemaBase)

class ANSISchemaGenerator(ANSISchemaBase)

module sqlalchemy.orm

Module Functions

backref()

cascade_mappers()

class_mapper()

clear_mapper()

clear_mappers()

column_property()

compile_mappers()

contains_alias()

contains_eager()

defer()

deferred()

eagerload()

extension()

lazyload()

mapper()

noload()

object_mapper()

object_session()

polymorphic_union()

relation()

synonym()

undefer()

class MapperExtension(object)

class Query(object)

class Session(object)

module sqlalchemy.orm.mapper

Module Functions

class_mapper()

object_mapper()

class MapperExtension(object)

class Mapper(object)

module sqlalchemy.orm.interfaces

class LoaderStrategy(object)

class MapperOption(object)

class MapperProperty(object)

class OperationContext(object)

class PropertyOption(MapperOption)

class StrategizedOption(PropertyOption)

class StrategizedProperty(MapperProperty)

class SynonymProperty(MapperProperty)

module sqlalchemy.orm.query

class Query(object)

class QueryContext(OperationContext)

class SelectionContext(OperationContext)

module sqlalchemy.orm.session

Module Functions

class_mapper()

object_mapper()

object_session()

class Session(object)

class SessionTransaction(object)

module sqlalchemy.exceptions

class ArgumentError()

class AssertionError()

class CircularDependencyError()

class CompileError()

class ConcurrentModificationError()

class DBAPIError()

class FlushError()

class InvalidRequestError()

class NoSuchColumnError()

class NoSuchTableError()

class SQLAlchemyError()

class SQLError()

class TimeoutError()

module sqlalchemy.pool

Module Functions

clear_managers()

manage()

class AssertionPool(Pool)

class NullPool(Pool)

class Pool(object)

class QueuePool(Pool)

class SingletonThreadPool(Pool)

class StaticPool(Pool)

module sqlalchemy.ext.sessioncontext

class SessionContext(object)

class SessionContextExt(MapperExtension)

module sqlalchemy.mods.threadlocal

Module Functions

assign_mapper()

class Objectstore(object)

module sqlalchemy.ext.selectresults

class SelectResults(object)

class SelectResultsExt(MapperExtension)

module sqlalchemy.ext.proxy

class AutoConnectEngine(BaseProxyEngine)

class BaseProxyEngine(Executor)

class ProxyEngine(BaseProxyEngine)

module sqlalchemy.ext.orderinglist

Module Functions

ordering_list()

class OrderingList(list)

module sqlalchemy.ext.associationproxy

Module Functions

association_proxy()

class AssociationProxy(object)

module sqlalchemy.databases.sqlite

Module Functions

descriptor()

class DateTimeMixin(object)

class PassiveDefault(DefaultGenerator)

class SLBinary(Binary)

class SLBoolean(Boolean)

class SLChar(CHAR)

class SLDate(DateTimeMixin,Date)

class SLDateTime(DateTimeMixin,DateTime)

class SLInteger(Integer)

class SLNumeric(Numeric)

class SLSmallInteger(SmallInteger)

class SLString(String)

class SLText(TEXT)

class SLTime(DateTimeMixin,Time)

class SQLiteCompiler(ANSICompiler)

class SQLiteDialect(ANSIDialect)

class SQLiteExecutionContext(DefaultExecutionContext)

class SQLiteIdentifierPreparer(ANSIIdentifierPreparer)

class SQLiteSchemaDropper(ANSISchemaDropper)

class SQLiteSchemaGenerator(ANSISchemaGenerator)

module sqlalchemy.databases.postgres

Module Functions

descriptor()

class PG1Date(Date)

class PG1DateTime(DateTime)

class PG1Time(Time)

class PG2Date(Date)

class PG2DateTime(DateTime)

class PG2Time(Time)

class PGBigInteger(PGInteger)

class PGBinary(Binary)

class PGBoolean(Boolean)

class PGChar(CHAR)

class PGCompiler(ANSICompiler)

class PGDefaultRunner(ANSIDefaultRunner)

class PGDialect(ANSIDialect)

class PGExecutionContext(DefaultExecutionContext)

class PGFloat(Float)

class PGIdentifierPreparer(ANSIIdentifierPreparer)

class PGInet(TypeEngine)

class PGInteger(Integer)

class PGInterval(TypeEngine)

class PGNumeric(Numeric)

class PGSchemaDropper(ANSISchemaDropper)

class PGSchemaGenerator(ANSISchemaGenerator)

class PGSmallInteger(SmallInteger)

class PGString(String)

class PGText(TEXT)

module sqlalchemy.databases.mysql

Module Functions

descriptor()

class MSBigInteger(MSInteger)

class MSBinary(_BinaryType)

class MSBlob(_BinaryType)

class MSBoolean(Boolean)

class MSChar(_StringType,CHAR)

class MSDate(Date)

class MSDateTime(DateTime)

class MSDecimal(MSNumeric)

class MSDouble(MSNumeric)

class MSEnum(MSString)

class MSFloat(Float,_NumericType)

class MSInteger(Integer,_NumericType)

class MSLongBlob(MSBlob)

class MSLongText(MSText)

class MSMediumBlob(MSBlob)

class MSMediumText(MSText)

class MSNChar(_StringType,CHAR)

class MSNVarChar(_StringType,String)

class MSNumeric(Numeric,_NumericType)

class MSSmallInteger(SmallInteger,_NumericType)

class MSString(_StringType,String)

class MSText(_StringType,TEXT)

class MSTime(Time)

class MSTimeStamp(TIMESTAMP)

class MSTinyBlob(MSBlob)

class MSTinyText(MSText)

class MSVarBinary(_BinaryType)

class MSYear(String)

class MySQLCompiler(ANSICompiler)

class MySQLDialect(ANSIDialect)

class MySQLExecutionContext(DefaultExecutionContext)

class MySQLIdentifierPreparer(ANSIIdentifierPreparer)

class MySQLSchemaDropper(ANSISchemaDropper)

class MySQLSchemaGenerator(ANSISchemaGenerator)

class array(object)

module sqlalchemy.databases.oracle

Module Functions

descriptor()

class OracleBinary(Binary)

class OracleBoolean(Boolean)

class OracleChar(CHAR)

class OracleCompiler(ANSICompiler)

class OracleDate(Date)

class OracleDateTime(DateTime)

class OracleDefaultRunner(ANSIDefaultRunner)

class OracleDialect(ANSIDialect)

class OracleExecutionContext(DefaultExecutionContext)

class OracleInteger(Integer)

class OracleNumeric(Numeric)

class OracleRaw(Binary)

class OracleSchemaDropper(ANSISchemaDropper)

class OracleSchemaGenerator(ANSISchemaGenerator)

class OracleSmallInteger(SmallInteger)

class OracleString(String)

class OracleText(TEXT)

class OracleTimestamp(TIMESTAMP)

module sqlalchemy.databases.mssql

Module Functions

descriptor()

class AdoMSNVarchar(MSNVarchar)

class MSBinary(Binary)

class MSBoolean(Boolean)

class MSChar(CHAR)

class MSDate(Date)

class MSDateTime(DateTime)

class MSDateTime_adodbapi(MSDateTime)

class MSDateTime_pyodbc(MSDateTime)

class MSDate_pymssql(MSDate)

class MSDate_pyodbc(MSDate)

class MSFloat(Float)

class MSInteger(Integer)

class MSNChar(NCHAR)

class MSNVarchar(Unicode)

class MSNumeric(Numeric)

class MSSQLCompiler(ANSICompiler)

class MSSQLDefaultRunner(ANSIDefaultRunner)

class MSSQLDialect(ANSIDialect)

class MSSQLDialect_adodbapi(MSSQLDialect)

class MSSQLDialect_pymssql(MSSQLDialect)

class MSSQLDialect_pyodbc(MSSQLDialect)

class MSSQLExecutionContext(DefaultExecutionContext)

class MSSQLExecutionContext_pyodbc(MSSQLExecutionContext)

class MSSQLIdentifierPreparer(ANSIIdentifierPreparer)

class MSSQLSchemaDropper(ANSISchemaDropper)

class MSSQLSchemaGenerator(ANSISchemaGenerator)

class MSSmallInteger(SmallInteger)

class MSString(String)

class MSText(TEXT)

class MSTime(Time)

class MSTimeStamp(TIMESTAMP)

class MSTinyInteger(Integer)

module sqlalchemy.databases.firebird

Module Functions

descriptor()

class FBBinary(Binary)

class FBBoolean(Boolean)

class FBChar(CHAR)

class FBCompiler(ANSICompiler)

class FBDate(DateTime)

class FBDateTime(DateTime)

class FBDefaultRunner(ANSIDefaultRunner)

class FBDialect(ANSIDialect)

class FBExecutionContext(DefaultExecutionContext)

class FBIdentifierPreparer(ANSIIdentifierPreparer)

class FBInteger(Integer)

class FBNumeric(Numeric)

class FBSchemaDropper(ANSISchemaDropper)

class FBSchemaGenerator(ANSISchemaGenerator)

class FBSmallInteger(SmallInteger)

class FBString(String)

class FBText(TEXT)