Thread: [Sqlalchemy-commits] sqlalchemy: document autocommit when using the compiler extensio...
Brought to you by:
zzzeek
From: <co...@sq...> - 2011-08-18 17:02:47
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/e16e5ffa99a3 changeset: 7805:e16e5ffa99a3 user: zzzeek date: Thu Aug 18 13:02:30 2011 -0400 description: document autocommit when using the compiler extension, update the "understanding autocommit" section diffstat: doc/build/core/connections.rst | 23 ++++++++++++--- doc/build/core/expression_api.rst | 4 ++ lib/sqlalchemy/dialects/mssql/base.py | 4 ++- lib/sqlalchemy/ext/compiler.py | 49 +++++++++++++++++++++++++++++++++++ lib/sqlalchemy/sql/expression.py | 15 ++++++++++- 5 files changed, 88 insertions(+), 7 deletions(-) diffs (180 lines): diff -r 44e239751af8 -r e16e5ffa99a3 doc/build/core/connections.rst --- a/doc/build/core/connections.rst Wed Aug 17 14:55:21 2011 -0400 +++ b/doc/build/core/connections.rst Thu Aug 18 13:02:30 2011 -0400 @@ -177,6 +177,7 @@ .. index:: single: thread safety; transactions +.. _autocommit: Understanding Autocommit ======================== @@ -184,19 +185,31 @@ The previous transaction example illustrates how to use :class:`.Transaction` so that several executions can take part in the same transaction. What happens when we issue an INSERT, UPDATE or DELETE call without using -:class:`.Transaction`? The answer is **autocommit**. While many DBAPI -implementation provide various special "non-transactional" modes, the current -SQLAlchemy behavior is such that it implements its own "autocommit" which +:class:`.Transaction`? While some DBAPI +implementations provide various special "non-transactional" modes, the core +behavior of DBAPI per PEP-0249 is that a *transaction is always in progress*, +providing only ``rollback()`` and ``commit()`` methods but no ``begin()``. +SQLAlchemy assumes this is the case for any given DBAPI. + +Given this requirement, SQLAlchemy implements its own "autocommit" feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, as well as data definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no -transaction is in progress. The detection is based on compiled statement -attributes, or in the case of a text-only statement via regular expressions:: +transaction is in progress. The detection is based on the presence of the +``autocommit=True`` execution option on the statement. If the statement +is a text-only statement and the flag is not set, a regular expression is used +to detect INSERT, UPDATE, DELETE, as well as a variety of other commands +for a particular backend:: conn = engine.connect() conn.execute("INSERT INTO users VALUES (1, 'john')") # autocommits +The "autocommit" feature is only in effect when no :class:`.Transaction` has +otherwise been declared. This means the feature is not generally used with +the ORM, as the :class:`.Session` object by default always maintains an +ongoing :class:`.Transaction`. + Full control of the "autocommit" behavior is available using the generative :meth:`.Connection.execution_options` method provided on :class:`.Connection`, :class:`.Engine`, :class:`.Executable`, using the "autocommit" flag which will diff -r 44e239751af8 -r e16e5ffa99a3 doc/build/core/expression_api.rst --- a/doc/build/core/expression_api.rst Wed Aug 17 14:55:21 2011 -0400 +++ b/doc/build/core/expression_api.rst Thu Aug 18 13:02:30 2011 -0400 @@ -219,6 +219,10 @@ :members: where, values :show-inheritance: +.. autoclass:: UpdateBase + :members: params, bind, returning + :show-inheritance: + .. autoclass:: ValuesBase :members: :show-inheritance: diff -r 44e239751af8 -r e16e5ffa99a3 lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400 +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:02:30 2011 -0400 @@ -655,7 +655,9 @@ seq_column = tbl._autoincrement_column insert_has_sequence = seq_column is not None - if insert_has_sequence: + if getattr(self.compiled._mssql_requires_identity_insert, False): + self._enable_identity_insert = True + elif insert_has_sequence: self._enable_identity_insert = \ seq_column.key in self.compiled_parameters[0] else: diff -r 44e239751af8 -r e16e5ffa99a3 lib/sqlalchemy/ext/compiler.py --- a/lib/sqlalchemy/ext/compiler.py Wed Aug 17 14:55:21 2011 -0400 +++ b/lib/sqlalchemy/ext/compiler.py Thu Aug 18 13:02:30 2011 -0400 @@ -91,6 +91,11 @@ "INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x > :x_1)" +.. note:: + + The above ``InsertFromSelect`` construct probably wants to have "autocommit" + enabled. See :ref:`enabling_compiled_autocommit` for this step. + Cross Compiling between SQL and DDL compilers --------------------------------------------- @@ -106,6 +111,50 @@ ddlcompiler.sql_compiler.process(constraint.expression) ) +.. _enabling_compiled_autocommit: + +Enabling Autocommit on a Construct +================================== + +Recall from the section :ref:`autocommit` that the :class:`.Engine`, when asked to execute +a construct in the absence of a user-defined transaction, detects if the given +construct represents DML or DDL, that is, a data modification or data definition statement, which +requires (or may require, in the case of DDL) that the transaction generated by the DBAPI be committed +(recall that DBAPI always has a transaction going on regardless of what SQLAlchemy does). Checking +for this is actually accomplished +by checking for the "autocommit" execution option on the construct. When building a construct like +an INSERT derivation, a new DDL type, or perhaps a stored procedure that alters data, the "autocommit" +option needs to be set in order for the statement to function with "connectionless" execution +(as described in :ref:`dbengine_implicit`). + +Currently a quick way to do this is to subclass :class:`.Executable`, then add the "autocommit" flag +to the ``_execution_options`` dictionary (note this is a "frozen" dictionary which supplies a generative +``union()`` method):: + + from sqlalchemy.sql.expression import Executable, ClauseElement + + class MyInsertThing(Executable, ClauseElement): + _execution_options = \\ + Executable._execution_options.union({'autocommit': True}) + +More succinctly, if the construct is truly similar to an INSERT, UPDATE, or DELETE, :class:`.UpdateBase` +can be used, which already is a subclass of :class:`.Executable`, :class:`.ClauseElement` and includes the +``autocommit`` flag:: + + from sqlalchemy.sql.expression import UpdateBase + + class MyInsertThing(UpdateBase): + def __init__(self, ...): + ... + + + + +DDL elements that subclass :class:`.DDLElement` already have the "autocommit" flag turned on. + + + + Changing the default compilation of existing constructs ======================================================= diff -r 44e239751af8 -r e16e5ffa99a3 lib/sqlalchemy/sql/expression.py --- a/lib/sqlalchemy/sql/expression.py Wed Aug 17 14:55:21 2011 -0400 +++ b/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:02:30 2011 -0400 @@ -4782,7 +4782,9 @@ bind = property(bind, _set_bind) class UpdateBase(Executable, ClauseElement): - """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.""" + """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements. + + """ __visit_name__ = 'update_base' @@ -4800,12 +4802,23 @@ return parameters def params(self, *arg, **kw): + """Set the parameters for the statement. + + This method raises ``NotImplementedError`` on the base class, + and is overridden by :class:`.ValuesBase` to provide the + SET/VALUES clause of UPDATE and INSERT. + + """ raise NotImplementedError( "params() is not supported for INSERT/UPDATE/DELETE statements." " To set the values for an INSERT or UPDATE statement, use" " stmt.values(**parameters).") def bind(self): + """Return a 'bind' linked to this :class:`.UpdateBase` + or a :class:`.Table` associated with it. + + """ return self._bind or self.table.bind def _set_bind(self, bind): |