From df15546cb8903e0eef0182faa0eda1691bf6bac8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 13 Feb 2012 20:07:44 -0500 Subject: [PATCH] - [feature] Added "no_parameters=True" execution option for connections. If no parameters are present, will pass the statement as cursor.execute(statement), thereby invoking the DBAPIs behavior when no parameter collection is present; for psycopg2 and mysql-python, this means not interpreting % signs in the string. This only occurs with this option, and not just if the param list is blank, as otherwise this would produce inconsistent behavior of SQL expressions that normally escape percent signs (and while compiling, can't know ahead of time if parameters will be present in some cases). [ticket:2407] --- CHANGES | 15 +++++++++++++ lib/sqlalchemy/engine/base.py | 37 +++++++++++++++++++++++++++----- lib/sqlalchemy/engine/default.py | 7 ++++++ test/engine/test_execute.py | 10 +++++++++ 4 files changed, 64 insertions(+), 5 deletions(-) diff --git a/CHANGES b/CHANGES index 29e8c2ba0f..a25c5f45a9 100644 --- a/CHANGES +++ b/CHANGES @@ -71,6 +71,21 @@ CHANGES with no columns at all. [ticket:2380] - engine + - [feature] Added "no_parameters=True" execution + option for connections. If no parameters + are present, will pass the statement + as cursor.execute(statement), thereby invoking + the DBAPIs behavior when no parameter collection + is present; for psycopg2 and mysql-python, this + means not interpreting % signs in the string. + This only occurs with this option, and not + just if the param list is blank, as otherwise + this would produce inconsistent behavior + of SQL expressions that normally escape percent + signs (and while compiling, can't know ahead of + time if parameters will be present in + some cases). [ticket:2407] + - [feature] Added pool_reset_on_return argument to create_engine, allows control over "connection return" behavior. Also added diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py index 0224d08433..7cc5522c41 100644 --- a/lib/sqlalchemy/engine/base.py +++ b/lib/sqlalchemy/engine/base.py @@ -491,14 +491,23 @@ class Dialect(object): raise NotImplementedError() def do_executemany(self, cursor, statement, parameters, context=None): - """Provide an implementation of *cursor.executemany(statement, - parameters)*.""" + """Provide an implementation of ``cursor.executemany(statement, + parameters)``.""" raise NotImplementedError() def do_execute(self, cursor, statement, parameters, context=None): - """Provide an implementation of *cursor.execute(statement, - parameters)*.""" + """Provide an implementation of ``cursor.execute(statement, + parameters)``.""" + + raise NotImplementedError() + + def do_execute_no_params(self, cursor, statement, parameters, context=None): + """Provide an implementation of ``cursor.execute(statement)``. + + The parameter collection should not be sent. + + """ raise NotImplementedError() @@ -976,6 +985,18 @@ class Connection(Connectable): is returned to the connection pool, i.e. the :meth:`.Connection.close` method is called. + :param no_parameters: When ``True``, if the final parameter + list or dictionary is totally empty, will invoke the + statement on the cursor as ``cursor.execute(statement)``, + not passing the parameter collection at all. + Some DBAPIs such as psycopg2 and mysql-python consider + percent signs as significant only when parameters are + present; this option allows code to generate SQL + containing percent signs (and possibly other characters) + that is neutral regarding whether it's executed by the DBAPI + or piped into a script that's later invoked by + command line tools. New in 0.7.6. + :param stream_results: Available on: Connection, statement. Indicate to the dialect that results should be "streamed" and not pre-buffered, if possible. This is a limitation @@ -1647,7 +1668,8 @@ class Connection(Connectable): if self._echo: self.engine.logger.info(statement) - self.engine.logger.info("%r", sql_util._repr_params(parameters, batches=10)) + self.engine.logger.info("%r", + sql_util._repr_params(parameters, batches=10)) try: if context.executemany: self.dialect.do_executemany( @@ -1655,6 +1677,11 @@ class Connection(Connectable): statement, parameters, context) + elif not parameters and context.no_parameters: + self.dialect.do_execute_no_params( + cursor, + statement, + context) else: self.dialect.do_execute( cursor, diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index a1e5a57991..5c2d98146d 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -334,6 +334,9 @@ class DefaultDialect(base.Dialect): def do_execute(self, cursor, statement, parameters, context=None): cursor.execute(statement, parameters) + def do_execute_no_params(self, cursor, statement, context=None): + cursor.execute(statement) + def is_disconnect(self, e, connection, cursor): return False @@ -537,6 +540,10 @@ class DefaultExecutionContext(base.ExecutionContext): self.cursor = self.create_cursor() return self + @util.memoized_property + def no_parameters(self): + return self.execution_options.get("no_parameters", False) + @util.memoized_property def is_crud(self): return self.isinsert or self.isupdate or self.isdelete diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py index f3206e9ccc..dc3e46aab9 100644 --- a/test/engine/test_execute.py +++ b/test/engine/test_execute.py @@ -41,6 +41,16 @@ class ExecuteTest(fixtures.TestBase): def teardown_class(cls): metadata.drop_all() + def test_no_params_option(self): + stmt = "SELECT '%'" + if testing.against('oracle'): + stmt += " FROM DUAL" + conn = testing.db.connect() + result = conn.\ + execution_options(no_parameters=True).\ + scalar(stmt) + eq_(result, '%') + @testing.fails_on_everything_except('firebird', 'maxdb', 'sqlite', '+pyodbc', '+mxodbc', '+zxjdbc', 'mysql+oursql', -- 2.47.2