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 a string in the form dialect+driver://user:password@host/dbname[?key=value..], where dialect is a database name such as mysql, oracle, postgresql, etc., and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc. Alternatively, the URL can be an instance of URL.
**kwargs takes a wide variety of options which are routed towards their appropriate components. Arguments may be specific to the Engine, the underlying Dialect, as well as the Pool. Specific dialects also accept keyword arguments that are unique to that dialect. Here, we describe the parameters that are common to most create_engine() usage.
| Parameters: |
|
|---|
Create a new Engine instance using a configuration dictionary.
The dictionary is typically produced from a config file where keys are prefixed, such as sqlalchemy.url, sqlalchemy.echo, etc. The ‘prefix’ argument indicates the prefix to be searched for.
A select set of keyword arguments will be “coerced” to their expected type based on string values. In a future release, this functionality will be expanded and include dialect-specific arguments.
Represent the components of a URL used to connect to a database.
This object is suitable to be passed directly to a create_engine() call. The fields of the URL are parsed from a string by the module-level make_url() function. the string format of the URL is an RFC-1738-style string.
All initialization parameters are available as public attributes.
| Parameters: |
|
|---|
Translate url attributes into a dictionary of connection arguments.
Returns attributes of this url (host, database, username, password, port) as a plain dictionary. The attribute names are used as the keys by default. Unset or false attributes are omitted from the final dictionary.
| Parameters: |
|
|---|
Connects a Pool and Dialect together to provide a source of database connectivity and behavior.
An Engine object is instantiated publically using the create_engine() function.
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.
Dispose of the connection pool used by this Engine.
A new connection pool is created immediately after the old one has been disposed. This new pool, like all SQLAlchemy connection pools, does not make any actual connections to the database until one is first requested.
This method has two general use cases:
- When a dropped connection is detected, it is assumed that all connections held by the pool are potentially dropped, and the entire pool is replaced.
- An application may want to use dispose() within a test suite that is creating multiple engines.
It is critical to note that dispose() does not guarantee that the application will release all open database connections - only those connections that are checked into the pool are closed. Connections which remain checked out or have been detached from the engine are not affected.
When True, enable log output for this element.
This has the effect of setting the Python logging level for the namespace of this element’s class and object reference. A value of boolean True indicates that the loglevel logging.INFO will be set for the logger, whereas the string value debug will set the loglevel to logging.DEBUG.
Return a list of all table names available in the database.
| Parameters: |
|
|---|
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.
The connection used is that of contextual_connect().
See also the similar method on Connection itself.
update the execution_options dictionary of this Engine.
For details on execution_options, see Connection.execution_options() as well as sqlalchemy.sql.expression.Executable.execution_options().
Provides high-level functionality for a wrapped DB-API connection.
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 thread-safe.
Construct a new Connection.
Connection objects are typically constructed by an Engine, see the connect() and contextual_connect() methods of Engine.
Begin a transaction and return a Transaction handle.
Repeated calls to begin on the same Connection will create a lightweight, emulated nested transaction. Only the outermost transaction may commit. Calls to commit on inner transactions are ignored. Any transaction in the hierarchy may rollback, however.
Begin a nested transaction and return a Transaction handle.
Nested transactions require SAVEPOINT support in the underlying database. Any transaction in the hierarchy may commit and rollback, however the outermost transaction still controls the overall commit or rollback of the transaction of a whole.
Begin a two-phase or XA transaction and return a Transaction handle.
| Parameter: | xid – the two phase transaction id. If not supplied, a random id will be generated. |
|---|
Returns self.
This Connectable interface method returns self, allowing Connections to be used interchangably with Engines in most situations that require a bind.
Returns self.
This Connectable interface method returns self, allowing Connections to be used interchangably with Engines in most situations that require a bind.
Detach the underlying DB-API connection from its connection pool.
This Connection instance will remain useable. When closed, the DB-API connection will be literally closed and not returned to its pool. The pool will typically lazily create a new connection to replace the detached connection.
This method can be used to insulate the rest of an application from a modified state on a connection (such as a transaction isolation level or similar). Also see PoolListener for a mechanism to modify connection state when connections leave and return to their connection pool.
Set non-SQL options for the connection which take effect during execution.
The method returns a copy of this Connection which references the same underlying DBAPI connection, but also defines the given execution options which will take effect for a call to execute(). As the new Connection references the same underlying resource, it is probably best to ensure that the copies would be discarded immediately, which is implicit if used as in:
result = connection.execution_options(stream_results=True). execute(stmt)The options are the same as those accepted by sqlalchemy.sql.expression.Executable.execution_options().
Invalidate the underlying DBAPI connection associated with this Connection.
The underlying DB-API connection is literally closed (if possible), and is discarded. Its source connection pool will typically lazily create a new connection to replace it.
Upon the next usage, this Connection will attempt to reconnect to the pool with a new connection.
Transactions in progress remain in an “opened” state (even though the actual transaction is gone); these must be explicitly rolled back before a reconnect on this Connection can proceed. This is to prevent applications from accidentally continuing their transactional operations in a non-transactional state.
Executes and returns the first column of the first row.
The underlying result/cursor is closed after execution.
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.
See also transaction() on engine.
Interface for an object which supports execution of SQL constructs.
The two implementations of Connectable are Connection and Engine.
Connectable must also implement the ‘dialect’ member which references a Dialect instance.
Wraps a DB-API 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 handles post-processing of result column data using TypeEngine objects, which are referenced from the originating SQL statement that produced this result set.
Close this ResultProxy.
Closes the underlying DBAPI cursor corresponding to the execution.
Note that any data cached within this ResultProxy is still available. For some types of results, this may include buffered rows.
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 called automatically when:
Fetch many rows, just like DB-API cursor.fetchmany(size=cursor.arraysize).
If rows are present, the cursor remains open after this is called. Else the cursor is automatically closed and an empty list is returned.
Fetch one row, just like DB-API cursor.fetchone().
If a row is present, the cursor remains open after this is called. Else the cursor is automatically closed and None is returned.
Fetch the first row and then close the result set unconditionally.
Returns None if no row is present.
deprecated. use inserted_primary_key.
Use inserted_primary_key
Return last_inserted_params() from the underlying ExecutionContext.
See ExecutionContext for details.
Return last_updated_params() from the underlying ExecutionContext.
See ExecutionContext for details.
Return lastrow_has_defaults() from the underlying ExecutionContext.
See ExecutionContext for details.
return the ‘lastrowid’ accessor on the DBAPI cursor.
This is a DBAPI specific method and is only functional for those backends which support it, for statements where it is appropriate. It’s behavior is not consistent across backends.
Usage of this method is normally unnecessary; the inserted_primary_key method provides a tuple of primary key values for a newly inserted row, regardless of database backend.
Return postfetch_cols() from the underlying ExecutionContext.
See ExecutionContext for details.
Fetch the first column of the first row, and close the result set.
Returns None if no row is present.
Proxy values from a single cursor row.
Mostly follows “ordered dictionary” behavior, mapping result values to the string-based column name, the integer position of the result in the row, as well as Column instances which can be mapped to the original Columns that produced this result set (for results that correspond to constructed SQL expressions).
Represent a Transaction in progress.
The Transaction object is not threadsafe.
Close this transaction.
If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.
This is used to cancel a Transaction without affecting the scope of an enclosing transaction.
Decorator, memoize a function in a connection.info stash.
Only applicable to functions which take no arguments other than a connection. The memo will be stored in connection.info[key].
Define the behavior of a specific database and DB-API combination.
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:
A mapping of DB-API type objects present in this Dialect’s DB-API implementation mapped to TypeEngine implementations used by the dialect.
This is used to apply types to result sets based on the DB-API types present in cursor.description; it only takes effect for result sets against textual statements where no explicit typemap was present.
Build DB-API compatible connection arguments.
Given a URL object, returns a tuple consisting of a *args/**kwargs suitable to send directly to the dbapi’s connect function.
Create a two-phase transaction ID.
This id will be passed to do_begin_twophase(), do_rollback_twophase(), do_commit_twophase(). Its format is unspecified.
convert the given name to a case insensitive identifier for the backend if it is an all-lowercase name.
this method is only used if the dialect defines requires_name_normalize=True.
Return information about columns in table_name.
Given a Connection, a string table_name, and an optional string schema, return column information as a list of dictionaries with these keys:
Additional column attributes may be present.
Return information about foreign_keys in table_name.
Given a Connection, a string table_name, and an optional string schema, return foreign key information as a list of dicts with these keys:
Return information about indexes in table_name.
Given a Connection, a string table_name and an optional string schema, return index information as a list of dictionaries with these keys:
Return information about the primary key constraint on table_name`.
Given a string table_name, and an optional string schema, return primary key information as a dictionary with these keys:
Return information about primary keys in table_name.
Given a Connection, a string table_name, and an optional string schema, return primary key information as a list of column names.
Return view definition.
Given a Connection, a string view_name, and an optional string schema, return the view definition.
Return a list of all view names available in the database.
Check the existence of a particular sequence in the database.
Given a Connection object and a string sequence_name, return True if the given sequence exists in the database, False otherwise.
Check the existence of a particular table in the database.
Given a Connection object and a string table_name, return True if the given table (possibly within the specified schema) exists in the database, False otherwise.
Called during strategized creation of the dialect with a connection.
Allows dialects to configure options based on server version info or other properties.
The connection passed here is a SQLAlchemy Connection object, with full capabilities.
The initalize() method of the base dialect should be called via super().
convert the given name to lowercase if it is detected as case insensitive.
this method is only used if the dialect defines requires_name_normalize=True.
return a callable which sets up a newly created DBAPI connection.
The callable accepts a single argument “conn” which is the DBAPI connection itself. It has no return value.
This is used to set dialect-wide per-connection options such as isolation modes, unicode modes, etc.
If a callable is returned, it will be assembled into a pool listener that receives the direct DBAPI connection, with all wrappers removed.
If None is returned, no listener will be generated.
Load table description from the database.
Given a Connection and a Table object, reflect its columns and properties from the database. If include_columns (a list or set) is specified, limit the autoload to the given column names.
The default implementation uses the Inspector interface to provide the output, building upon the granular table/column/ constraint etc. methods of Dialect.
Transform a generic type to a dialect-specific type.
Dialect classes will usually use the adapt_type() function in the types module to make this job easy.
The returned result is cached per dialect class so can contain no dialect-instance state.
Bases: sqlalchemy.engine.base.Dialect
Default implementation of Dialect
Create a random two-phase transaction ID.
This id will be passed to do_begin_twophase(), do_rollback_twophase(), do_commit_twophase(). Its format is unspecified.
return a callable which sets up a newly created DBAPI connection.
This is used to set dialect-wide per-connection options such as isolation modes, unicode modes, etc.
If a callable is returned, it will be assembled into a pool listener that receives the direct DBAPI connection, with all wrappers removed.
If None is returned, no listener will be generated.
Provide a database-specific TypeEngine object, given the generic object which comes from the types module.
This method looks for a dictionary called colspecs as a class or instance-level variable, and passes on to types.adapt_type().
Bases: sqlalchemy.engine.base.ExecutionContext
return self.cursor.lastrowid, or equivalent, after an INSERT.
This may involve calling special cursor functions, issuing a new SELECT on the cursor (or a new one), or returning a stored value that was calculated within post_exec().
This function will only be called for dialects which support “implicit” primary key generation, keep preexecute_autoincrement_sequences set to False, and when no explicit id value was bound to the statement.
The function is called once, directly after post_exec() and before the transaction is committed or ResultProxy is generated. If the post_exec() method assigns a value to self._lastrowid, the value is used in place of calling get_lastrowid().
Note that this method is not equivalent to the lastrowid method on ResultProxy, which is a direct proxy to the DBAPI lastrowid accessor in all cases.
A messenger object for a Dialect that corresponds to a single execution.
ExecutionContext should have these data members:
Return a new cursor generated from this ExecutionContext’s connection.
Some dialects may wish to change the behavior of connection.cursor(), such as postgresql which may return a PG “server side” cursor.
Return the number of rows produced (by a SELECT query) or affected (by an INSERT/UPDATE/DELETE statement).
Note that this row count may not be properly implemented in some dialects; this is indicated by the supports_sane_rowcount and supports_sane_multi_rowcount dialect attributes.
Return a dictionary of the full parameter dictionary for the last compiled INSERT statement.
Includes any ColumnDefaults or Sequences that were pre-executed.
Return a dictionary of the full parameter dictionary for the last compiled UPDATE statement.
Includes any ColumnDefaults that were pre-executed.
Called after the execution of a compiled statement.
If a compiled statement was passed to this ExecutionContext, the last_insert_ids, last_inserted_params, etc. datamembers should be available after this method completes.
Called before an execution of a compiled statement.
If a compiled statement was passed to this ExecutionContext, the statement and parameters datamembers must be initialized after this statement is complete.
Return a result object corresponding to this ExecutionContext.
Returns a ResultProxy.