SQLAlchemy 0.3 Documentation - module sqlalchemy.engine

Module Functions

def create_engine(*args, **kwargs)

Create a new Engine instance.

The standard method of specifying the engine is via URL as the first positional argument, to indicate the appropriate database dialect and connection arguments, with additional keyword arguments sent as options to the dialect and resulting Engine.

The URL is in the form dialect://user:password@host/dbname[?key=value..], where dialect is a name such as mysql, oracle, postgres, etc.

**kwargs represents options to be sent to the Engine itself as well as the components of the Engine, including the Dialect, the ConnectionProvider, and the Pool. A list of common options is as follows:

poolclass

a subclass of sqlalchemy.pool.Pool which will be used to instantiate a connection pool.

pool

an instance of sqlalchemy.pool.DBProxy or sqlalchemy.pool.Pool to be used as the underlying source for connections (DBProxy/Pool is described in the previous section). This argument supercedes "poolclass".

echo

Defaults to False: if True, the Engine will log all statements as well as a repr() of their parameter lists to the engines logger, which defaults to sys.stdout. A Engine instances' echo data member can be modified at any time to turn logging on and off. If set to the string 'debug', result rows will be printed to the standard output as well.

logger

Defaults to None: a file-like object where logging output can be sent, if echo is set to True. This defaults to sys.stdout.

encoding

Defaults to 'utf-8': the encoding to be used when encoding/decoding Unicode strings.

convert_unicode

Defaults to False: true if unicode conversion should be applied to all str types.

module

Defaults to None: this is a reference to a DBAPI2 module to be used instead of the engine's default module. For Postgres, the default is psycopg2, or psycopg1 if 2 cannot be found. For Oracle, its cx_Oracle. For mysql, MySQLdb.

strategy

allows alternate Engine implementations to take effect. Current implementations include plain and threadlocal. The default used by this function is plain.

plain provides support for a Connection object which can be used to execute SQL queries with a specific underlying DBAPI connection.

threadlocal is similar to plain except that it adds support for a thread-local connection and transaction context, which allows a group of engine operations to participate using the same underlying connection and transaction without the need for explicitly passing a single Connection.

def engine_descriptors()

Provide a listing of all the database implementations supported.

This data is provided as a list of dictionaries, where each dictionary contains the following key/value pairs:

name: the name of the engine, suitable for use in the create_engine function
description: a plain description of the engine.
arguments: a dictionary describing the name and description of each parameter used to connect to this engine's underlying DBAPI.

This function is meant for usage in automated configuration tools that wish to query the user for database and connection information.

back to section top

class Connectable(object)

Interface for an object that can provide an Engine and a Connection object which correponds to that Engine.

def contextual_connect(self)

Return a Connection object which may be part of an ongoing context.

def create(self, entity, **kwargs)

Create a table or index given an appropriate schema object.

def drop(self, entity, **kwargs)

Drop a table or index given an appropriate schema object.

engine = property()

The Engine which this Connectable is associated with.

def execute(self, object, *multiparams, **params)

back to section top

class Connection(Connectable)

Represent a single DBAPI connection returned from the underlying connection pool.

Provides execution support for string-based SQL statements as well as ClauseElement, Compiled and DefaultGenerator objects. Provides a begin method to return Transaction objects.

The Connection object is not threadsafe.

def __init__(self, engine, connection=None, close_with_result=False)

def begin(self)

def close(self)

def connect(self)

connect() is implemented to return self so that an incoming Engine or Connection object can be treated similarly.

connection = property()

The underlying DBAPI connection managed by this Connection.

def contextual_connect(self, **kwargs)

contextual_connect() is implemented to return self so that an incoming Engine or Connection object can be treated similarly.

def create(self, entity, **kwargs)

Create a table or index given an appropriate schema object.

def default_schema_name(self)

def drop(self, entity, **kwargs)

Drop a table or index given an appropriate schema object.

engine = property()

The Engine with which this Connection is associated (read only)

def execute(self, object, *multiparams, **params)

def execute_clauseelement(self, elem, *multiparams, **params)

def execute_compiled(self, compiled, *multiparams, **params)

Execute a sql.Compiled object.

def execute_default(self, default, **kwargs)

def execute_function(self, func, *multiparams, **params)

def execute_text(self, statement, *multiparams, **params)

def in_transaction(self)

def proxy(self, statement=None, parameters=None)

Execute the given statement string and parameter object.

The parameter object is expected to be the result of a call to compiled.get_params(). This callable is a generic version of a connection/cursor-specific callable that is produced within the execute_compiled method, and is used for objects that require this style of proxy when outside of an execute_compiled method, primarily the DefaultRunner.

def reflecttable(self, table, **kwargs)

Reflect the columns in the given table from the database.

def run_callable(self, callable_)

def scalar(self, object, *multiparams, **params)

should_close_with_result = property()

Indicates if this Connection should be closed when a corresponding ResultProxy is closed; this is essentially an auto-release mode.

back to section top

class ConnectionProvider(object)

Define an interface that returns raw Connection objects (or compatible).

def dispose(self)

Release all resources corresponding to this ConnectionProvider.

This includes any underlying connection pools.

def get_connection(self)

Return a Connection or compatible object from a DBAPI which also contains a close() method.

It is not defined what context this connection belongs to. It may be newly connected, returned from a pool, part of some other kind of context such as thread-local, or can be a fixed member of this object.

back to section top

class DefaultRunner(SchemaVisitor)

A visitor which accepts ColumnDefault objects, produces the dialect-specific SQL corresponding to their execution, and executes the SQL, returning the result value.

DefaultRunners are used internally by Engines and Dialects. Specific database modules should provide their own subclasses of DefaultRunner to allow database-specific behavior.

def __init__(self, engine, proxy)

def exec_default_sql(self, default)

def get_column_default(self, column)

def get_column_onupdate(self, column)

def visit_column_default(self, default)

def visit_column_onupdate(self, onupdate)

def visit_passive_default(self, default)

Do nothing.

Passive defaults by definition return None on the app side, and are post-fetched to get the DB-side value.

def visit_sequence(self, seq)

Do nothing.

Sequences are not supported by default.

back to section top

class Dialect(AbstractDialect)

Define the behavior of a specific database/DBAPI.

Any aspect of metadata definition, SQL query generation, execution, result-set handling, or anything else which varies between databases is defined under the general category of the Dialect. The Dialect acts as a factory for other database-specific object implementations including ExecutionContext, Compiled, DefaultGenerator, and TypeEngine.

All Dialects implement the following attributes:

positional: True if the paramstyle for this Dialect is positional
paramstyle: The paramstyle to be used (some DBAPIs support multiple paramstyles)
supports_autoclose_results: Usually True; if False, indicates that rows returned by fetchone() might not be just plain tuples, and may be "live" proxy objects which still require the cursor to be open in order to be read (such as pyPgSQL which has active filehandles for BLOBs). In that case, an auto-closing ResultProxy cannot automatically close itself after results are consumed.
convert_unicode: True if unicode conversion should be applied to all str types
encoding: type of encoding to use for unicode, usually defaults to 'utf-8'

def compile(self, clauseelement, parameters=None)

Compile the given ClauseElement using this Dialect.

A convenience method which simply flips around the compile() call on ClauseElement.

def compiler(self, statement, parameters)

Return a sql.ClauseVisitor able to transform a ClauseElement into a string.

The returned object is usually a subclass of ansisql.ANSICompiler, and will produce a string representation of the given ClauseElement and parameters dictionary.

compiler() is called within the context of the compile() method.

def convert_compiled_params(self, parameters)

Build DBAPI execute arguments from a ClauseParameters.

Given a sql.ClauseParameters object, returns an array or dictionary suitable to pass directly to this Dialect's DBAPI's execute method.

def create_connect_args(self, opts)

Build DBAPI compatible connection arguments.

Given a dictionary of key-valued connect parameters, returns a tuple consisting of a *args/**kwargs suitable to send directly to the dbapi's connect function. The connect args will have any number of the following keynames: host, hostname, database, dbname, user, username, password, pw, passwd, filename.

def create_cursor(self, connection)

Return a new cursor generated from the given connection.

def create_result_proxy_args(self, connection, cursor)

Return a dictionary of arguments that should be passed to ResultProxy().

def dbapi(self)

Establish a connection to the database.

Subclasses override this method to provide the DBAPI module used to establish connections.

def defaultrunner(self, engine, proxy, **params)

Return a schema.SchemaVisitor instance that can execute defaults.

def do_begin(self, connection)

Provide an implementation of connection.begin().

def do_commit(self, connection)

Provide an implementation of connection.commit()

def do_execute(self, cursor, statement, parameters)

Execute a single SQL statement with given parameters.

def do_executemany(self, cursor, statement, parameters)

Execute a single SQL statement looping over a sequence of parameters.

def do_rollback(self, connection)

Provide an implementation of connection.rollback().

def execution_context(self)

Return a new ExecutionContext object.

def get_default_schema_name(self, connection)

Return the currently selected schema given a connection

def has_sequence(self, connection, sequence_name)

Check the existence of a particular sequence in the database.

Given a Connection object and a sequence_name, return True if the given sequence exists in the database, False otherwise.

def has_table(self, connection, table_name, schema=None)

Check the existence of a particular table in the database.

Given a Connection object and a table_name, return True if the given table (possibly within the specified schema) exists in the database, False otherwise.

def oid_column_name(self, column)

Return the oid column name for this dialect, or None if the dialect cant/wont support OID/ROWID.

The Column instance which represents OID for the query being compiled is passed, so that the dialect can inspect the column and its parent selectable to determine if OID/ROWID is not selected for a particular selectable (i.e. oracle doesnt support ROWID for UNION, GROUP BY, DISTINCT, etc.)

def reflecttable(self, connection, table)

Load table description from the database.

Given a Connection and a Table object, reflect its columns and properties from the database.

def schemadropper(self, engine, proxy, **params)

Return a schema.SchemaVisitor instance that can drop schemas.

schemadropper() is called via the drop() method on Table, Index, and others.

def schemagenerator(self, engine, proxy, **params)

Return a schema.SchemaVisitor instance that can generate schemas.

schemagenerator() is called via the create() method on Table, Index, and others.

def supports_sane_rowcount(self)

Indicate whether the dialect properly implements statements rowcount.

Provided to indicate when MySQL is being used, which does not have standard behavior for the "rowcount" function on a statement handle.

def type_descriptor(self, typeobj)

Trasform the type from generic to database-specific.

Provides a database-specific TypeEngine object, given the generic object which comes from the types module. Subclasses will usually use the adapt_type() method in the types module to make this job easy.

back to section top

class Engine(Executor,Connectable)

Connects a ConnectionProvider, a Dialect and a CompilerFactory together to provide a default implementation of SchemaEngine.

def __init__(self, connection_provider, dialect, echo=None)

def compiler(self, statement, parameters, **kwargs)

def connect(self, **kwargs)

Return a newly allocated Connection object.

def contextual_connect(self, close_with_result=False, **kwargs)

Return a Connection object which may be newly allocated, or may be part of some ongoing context.

This Connection is meant to be used by the various "auto-connecting" operations.

def create(self, entity, connection=None, **kwargs)

Create a table or index within this engine's database connection given a schema.Table object.

def dispose(self)

def drop(self, entity, connection=None, **kwargs)

Drop a table or index within this engine's database connection given a schema.Table object.

engine = property()

def execute(self, statement, *multiparams, **params)

def execute_compiled(self, compiled, *multiparams, **params)

def execute_default(self, default, **kwargs)

func = property()

def has_table(self, table_name, schema=None)

def log(self, msg)

Log a message using this SQLEngine's logger stream.

name = property()

def raw_connection(self)

Return a DBAPI connection.

def reflecttable(self, table, connection=None)

Given a Table object, reflects its columns and properties from the database.

def run_callable(self, callable_, connection=None, *args, **kwargs)

def scalar(self, statement, *multiparams, **params)

def text(self, text, *args, **kwargs)

Return a sql.text() object for performing literal queries.

def transaction(self, callable_, connection=None, *args, **kwargs)

Execute the given function within a transaction boundary.

This is a shortcut for explicitly calling begin() and commit() and optionally rollback() when exceptions are raised. The given *args and **kwargs will be passed to the function, as well as the Connection used in the transaction.

back to section top

class ExecutionContext(object)

A messenger object for a Dialect that corresponds to a single execution.

The Dialect should provide an ExecutionContext via the create_execution_context() method. The pre_exec and post_exec methods will be called for compiled statements, afterwhich it is expected that the various methods last_inserted_ids, last_inserted_params, etc. will contain appropriate values, if applicable.

def get_rowcount(self, cursor)

Return the count of rows updated/deleted for an UPDATE/DELETE statement.

def last_inserted_ids(self)

Return the list of the primary key values for the last insert statement executed.

This does not apply to straight textual clauses; only to sql.Insert objects compiled against a schema.Table object, which are executed via statement.execute(). The order of items in the list is the same as that of the Table's 'primary_key' attribute.

In some cases, this method may invoke a query back to the database to retrieve the data, based on the "lastrowid" value in the cursor.

def last_inserted_params(self)

Return a dictionary of the full parameter dictionary for the last compiled INSERT statement.

Includes any ColumnDefaults or Sequences that were pre-executed.

def last_updated_params(self)

Return a dictionary of the full parameter dictionary for the last compiled UPDATE statement.

Includes any ColumnDefaults that were pre-executed.

def lastrow_has_defaults(self)

Return True if the last row INSERTED via a compiled insert statement contained PassiveDefaults.

The presence of PassiveDefaults indicates that the database inserted data beyond that which we passed to the query programmatically.

def post_exec(self, engine, proxy, compiled, parameters)

Called after the execution of a compiled statement.

proxy is a callable that takes a string statement and a bind parameter list/dictionary.

def pre_exec(self, engine, proxy, compiled, parameters)

Called before an execution of a compiled statement.

proxy is a callable that takes a string statement and a bind parameter list/dictionary.

def supports_sane_rowcount(self)

Indicate if the "rowcount" DBAPI cursor function works properly.

Currently, MySQLDB does not properly implement this function.

back to section top

class PrefetchingResultProxy(ResultProxy)

ResultProxy that loads all columns into memory each time fetchone() is called. If fetchmany() or fetchall() are called, the full grid of results is fetched.

def fetchall(self)

def fetchmany(self, size=None)

def fetchone(self)

back to section top

class ResultProxy(object)

Wraps a DBAPI cursor object to provide easier access to row columns.

Individual columns may be accessed by their integer position, case-insensitive column name, or by schema.Column object. e.g.:

row = fetchone()

col1 = row[0]    # access via integer position

col2 = row['col2']   # access via name

col3 = row[mytable.c.mycol] # access via Column object.

ResultProxy also contains a map of TypeEngine objects and will invoke the appropriate convert_result_value() method before returning columns, as well as the ExecutionContext corresponding to the statement execution. It provides several methods for which to obtain information from the underlying ExecutionContext.

def __init__(self, engine, connection, cursor, executioncontext=None, typemap=None, columns=None, should_prefetch=None)

ResultProxy objects are constructed via the execute() method on SQLEngine.

def close(self)

Close this ResultProxy, and the underlying DBAPI cursor corresponding to the execution.

If this ResultProxy was generated from an implicit execution, the underlying Connection will also be closed (returns the underlying DBAPI connection to the connection pool.)

This method is also called automatically when all result rows are exhausted.

executioncontext = property()

def fetchall(self)

Fetch all rows, just like DBAPI cursor.fetchall().

def fetchmany(self, size=None)

Fetch many rows, just like DBAPI cursor.fetchmany(size=cursor.arraysize).

def fetchone(self)

Fetch one row, just like DBAPI cursor.fetchone().

def last_inserted_ids(self)

Return last_inserted_ids() from the underlying ExecutionContext.