diff options
| -rw-r--r-- | doc/build/changelog/unreleased_12/4140.rst | 7 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/cx_oracle.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/engine/default.py | 2 | ||||
| -rw-r--r-- | test/dialect/oracle/test_compiler.py | 10 | ||||
| -rw-r--r-- | test/requirements.py | 7 | ||||
| -rw-r--r-- | test/sql/test_query.py | 60 |
6 files changed, 90 insertions, 1 deletions
diff --git a/doc/build/changelog/unreleased_12/4140.rst b/doc/build/changelog/unreleased_12/4140.rst new file mode 100644 index 000000000..2b29b2523 --- /dev/null +++ b/doc/build/changelog/unreleased_12/4140.rst @@ -0,0 +1,7 @@ +.. change:: + :tags: bug, sql + :tickets: 4140 + + Fixed bug in new "expanding bind parameter" feature whereby if multiple + params were used in one statement, the regular expression would not + match the parameter name correctly.
\ No newline at end of file diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 56a0425c8..68ecce519 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -376,6 +376,11 @@ class OracleCompiler_cx_oracle(OracleCompiler): quote = getattr(name, 'quote', None) if quote is True or quote is not False and \ self.preparer._bindparam_requires_quotes(name): + if kw.get('expanding', False): + raise exc.CompileError( + "Can't use expanding feature with parameter name " + "%r on Oracle; it requires quoting which is not supported " + "in this context." % name) quoted_name = '"%s"' % name self._quoted_bind_names[name] = quoted_name return OracleCompiler.bindparam_string(self, quoted_name, **kw) diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 4b9aa9493..36344fc38 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -779,7 +779,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext): return replacement_expressions.pop(m.group(1)) self.statement = re.sub( - r"\[EXPANDING_(.+)\]", + r"\[EXPANDING_(\S+)\]", process_expanding, self.statement ) diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index fc310f8f2..3e1ffebb3 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -85,6 +85,16 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t.update().values(plain=5), 'UPDATE s SET "plain"=:"plain"' ) + def test_bindparam_quote_raise_on_expanding(self): + assert_raises_message( + exc.CompileError, + "Can't use expanding feature with parameter name 'uid' on " + "Oracle; it requires quoting which is not supported in this " + "context", + bindparam("uid", expanding=True).compile, + dialect=cx_oracle.dialect() + ) + def test_cte(self): part = table( 'part', diff --git a/test/requirements.py b/test/requirements.py index dac949400..39a78dfa5 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -145,6 +145,13 @@ class DefaultRequirements(SuiteRequirements): return skip_if(["firebird", "mssql+mxodbc"], "not supported by driver") @property + def no_quoting_special_bind_names(self): + """Target database will quote bound paramter names, doesn't support + EXPANDING""" + + return skip_if(["oracle"]) + + @property def identity(self): """Target database must support GENERATED AS IDENTITY or a facsimile. diff --git a/test/sql/test_query.py b/test/sql/test_query.py index afb113748..74efbf9a4 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -471,6 +471,66 @@ class QueryTest(fixtures.TestBase): ), [{"uname": ['fred']}, {"uname": ['ed']}] ) + @testing.requires.no_quoting_special_bind_names + def test_expanding_in_special_chars(self): + testing.db.execute( + users.insert(), + [ + dict(user_id=7, user_name='jack'), + dict(user_id=8, user_name='fred'), + ] + ) + + with testing.db.connect() as conn: + stmt = select([users]).where( + users.c.user_name.in_(bindparam('u35', expanding=True)) + ).where( + users.c.user_id == bindparam("u46") + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, {"u35": ['jack', 'fred'], "u46": 7}).fetchall(), + [(7, 'jack')] + ) + + stmt = select([users]).where( + users.c.user_name.in_(bindparam('u.35', expanding=True)) + ).where( + users.c.user_id == bindparam("u.46") + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, {"u.35": ['jack', 'fred'], "u.46": 7}).fetchall(), + [(7, 'jack')] + ) + + def test_expanding_in_multiple(self): + testing.db.execute( + users.insert(), + [ + dict(user_id=7, user_name='jack'), + dict(user_id=8, user_name='fred'), + dict(user_id=9, user_name='ed') + ] + ) + + with testing.db.connect() as conn: + stmt = select([users]).where( + users.c.user_name.in_(bindparam('uname', expanding=True)) + ).where( + users.c.user_id.in_(bindparam('userid', expanding=True)) + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, + {"uname": ['jack', 'fred', 'ed'], "userid": [8, 9]} + ).fetchall(), + [(8, 'fred'), (9, 'ed')] + ) + @testing.requires.tuple_in def test_expanding_in_composite(self): testing.db.execute( |