From 73bfc876692afad7c9f3fcb8bc42bbe732738a5c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 25 Jan 2010 21:04:50 +0000 Subject: [PATCH] - Added a tuple_() construct, allows sets of expressions to be compared to another set, typically with IN against composite primary keys or similar. Also accepts an IN with multiple columns. The "scalar select can have only one column" error message is removed - will rely upon the database to report problems with col mismatch. --- CHANGES | 10 +- .../reference/sqlalchemy/expressions.rst | 2 + lib/sqlalchemy/__init__.py | 1 + lib/sqlalchemy/sql/__init__.py | 1 + lib/sqlalchemy/sql/expression.py | 43 ++++- lib/sqlalchemy/sql/functions.py | 8 +- test/sql/test_select.py | 149 ++++++++++-------- 7 files changed, 134 insertions(+), 80 deletions(-) diff --git a/CHANGES b/CHANGES index ce7a67dcb6..f8ebfbb41b 100644 --- a/CHANGES +++ b/CHANGES @@ -293,7 +293,15 @@ CHANGES (or a "bytes" type in Python 3), instead of the built- in "buffer" type. This allows symmetric round trips of binary data. [ticket:1524] - + + - Added a tuple_() construct, allows sets of expressions + to be compared to another set, typically with IN against + composite primary keys or similar. Also accepts an + IN with multiple columns. The "scalar select can + have only one column" error message is removed - will + rely upon the database to report problems with + col mismatch. + - User-defined "default" and "onupdate" callables which accept a context should now call upon "context.current_parameters" to get at the dictionary diff --git a/doc/build/reference/sqlalchemy/expressions.rst b/doc/build/reference/sqlalchemy/expressions.rst index 28cc37f023..1c3c208b46 100644 --- a/doc/build/reference/sqlalchemy/expressions.rst +++ b/doc/build/reference/sqlalchemy/expressions.rst @@ -99,6 +99,8 @@ The expression package uses functions to construct SQL expressions. The return .. autofunction:: text +.. autofunction:: tuple_ + .. autofunction:: union .. autofunction:: union_all diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 8aa293a6cc..dbc0c5dc9b 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -42,6 +42,7 @@ from sqlalchemy.sql import ( select, subquery, text, + tuple_, union, union_all, update, diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 0b347ca386..aa18eac171 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -46,6 +46,7 @@ from sqlalchemy.sql.expression import ( subquery, table, text, + tuple_, union, union_all, update, diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index cf5d98d8f8..6d74fec168 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -47,7 +47,7 @@ __all__ = [ 'modifier', 'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label', 'literal', 'literal_column', 'not_', 'null', 'or_', 'outparam', 'outerjoin', 'select', - 'subquery', 'table', 'text', 'union', 'union_all', 'update', ] + 'subquery', 'table', 'text', 'tuple_', 'union', 'union_all', 'update', ] PARSE_AUTOCOMMIT = util._symbol('PARSE_AUTOCOMMIT') @@ -662,6 +662,18 @@ def literal(value, type_=None): """ return _BindParamClause(None, value, type_=type_, unique=True) +def tuple_(*expr): + """Return a SQL tuple. + + Main usage is to produce a composite IN construct:: + + tuple_(table.c.col1, table.c.col2).in_( + [(1, 2), (5, 12), (10, 19)] + ) + + """ + return _Tuple(*expr) + def label(name, obj): """Return a :class:`_Label` object for the given :class:`ColumnElement`. @@ -955,6 +967,13 @@ def _literal_as_binds(element, name=None, type_=None): else: return element +def _type_from_args(args): + for a in args: + if not isinstance(a.type, sqltypes.NullType): + return a.type + else: + return sqltypes.NullType + def _no_literals(element): if hasattr(element, '__clause_element__'): return element.__clause_element__() @@ -1500,7 +1519,8 @@ class _CompareMixin(ColumnOperators): if not _is_literal(o): if not isinstance( o, _CompareMixin): raise exc.InvalidRequestError( - "in() function accepts either a list of non-selectable values, or a selectable: %r" % o) + "in() function accepts either a list of non-selectable values, " + "or a selectable: %r" % o) else: o = self._bind_param(o) args.append(o) @@ -2360,6 +2380,22 @@ class BooleanClauseList(ClauseList, ColumnElement): def _select_iterable(self): return (self, ) +class _Tuple(ClauseList, ColumnElement): + + def __init__(self, *clauses, **kw): + super(_Tuple, self).__init__(*clauses, **kw) + self.type = _type_from_args(clauses) + + @property + def _select_iterable(self): + return (self, ) + + def _bind_param(self, obj): + return _Tuple(*[ + _BindParamClause(None, o, type_=self.type, unique=True) + for o in obj + ]).self_group() + class _Case(ColumnElement): __visit_name__ = 'case' @@ -3318,9 +3354,6 @@ class _ScalarSelect(_Grouping): def __init__(self, element): self.element = element cols = list(element.c) - if len(cols) != 1: - raise exc.InvalidRequestError("Scalar select can only be created " - "from a Select object that has exactly one column expression.") self.type = cols[0].type @property diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index c6cb938d44..212f81adab 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -1,6 +1,6 @@ from sqlalchemy import types as sqltypes from sqlalchemy.sql.expression import ( - ClauseList, Function, _literal_as_binds, text + ClauseList, Function, _literal_as_binds, text, _type_from_args ) from sqlalchemy.sql import operators from sqlalchemy.sql.visitors import VisitableType @@ -102,9 +102,3 @@ class sysdate(AnsiFunction): class user(AnsiFunction): __return_type__ = sqltypes.String -def _type_from_args(args): - for a in args: - if not isinstance(a.type, sqltypes.NullType): - return a.type - else: - return sqltypes.NullType diff --git a/test/sql/test_select.py b/test/sql/test_select.py index 28317db574..fd7fb9ceb6 100644 --- a/test/sql/test_select.py +++ b/test/sql/test_select.py @@ -367,18 +367,12 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A @testing.uses_deprecated('scalar option') def test_scalar_select(self): - try: - s = select([table1.c.myid, table1.c.name]).as_scalar() - assert False - except exc.InvalidRequestError, err: - assert str(err) == "Scalar select can only be created from a Select object that has exactly one column expression.", str(err) - - try: - # generic function which will look at the type of expression - func.coalesce(select([table1.c.myid])) - assert False - except exc.InvalidRequestError, err: - assert str(err) == "Select objects don't have a type. Call as_scalar() on this Select object to return a 'scalar' version of this Select.", str(err) + assert_raises_message( + exc.InvalidRequestError, + r"Select objects don't have a type\. Call as_scalar\(\) " + "on this Select object to return a 'scalar' version of this Select\.", + func.coalesce, select([table1.c.myid]) + ) s = select([table1.c.myid], correlate=False).as_scalar() self.assert_compile(select([table1, s]), "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid FROM mytable) AS anon_1 FROM mytable") @@ -1356,80 +1350,93 @@ EXISTS (select yay from foo where boo = lar)", assert [str(c) for c in s.c] == ["id", "hoho"] + @testing.emits_warning('.*empty sequence.*') def test_in(self): - self.assert_compile(select([table1], table1.c.myid.in_(['a'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1)") + self.assert_compile(table1.c.myid.in_(['a']), + "mytable.myid IN (:myid_1)") - self.assert_compile(select([table1], ~table1.c.myid.in_(['a'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (:myid_1)") + self.assert_compile(~table1.c.myid.in_(['a']), + "mytable.myid NOT IN (:myid_1)") - self.assert_compile(select([table1], table1.c.myid.in_(['a', 'b'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :myid_2)") + self.assert_compile(table1.c.myid.in_(['a', 'b']), + "mytable.myid IN (:myid_1, :myid_2)") - self.assert_compile(select([table1], table1.c.myid.in_(iter(['a', 'b']))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :myid_2)") + self.assert_compile(table1.c.myid.in_(iter(['a', 'b'])), + "mytable.myid IN (:myid_1, :myid_2)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a')])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1)") + self.assert_compile(table1.c.myid.in_([literal('a')]), + "mytable.myid IN (:param_1)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), 'b'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :myid_1)") + self.assert_compile(table1.c.myid.in_([literal('a'), 'b']), + "mytable.myid IN (:param_1, :myid_1)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), literal('b')])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :param_2)") + self.assert_compile(table1.c.myid.in_([literal('a'), literal('b')]), + "mytable.myid IN (:param_1, :param_2)") - self.assert_compile(select([table1], table1.c.myid.in_(['a', literal('b')])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :param_1)") + self.assert_compile(table1.c.myid.in_(['a', literal('b')]), + "mytable.myid IN (:myid_1, :param_1)") - self.assert_compile(select([table1], table1.c.myid.in_([literal(1) + 'a'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 + :param_2)") + self.assert_compile(table1.c.myid.in_([literal(1) + 'a']), + "mytable.myid IN (:param_1 + :param_2)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a') +'a', 'b'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 || :param_2, :myid_1)") + self.assert_compile(table1.c.myid.in_([literal('a') +'a', 'b']), + "mytable.myid IN (:param_1 || :param_2, :myid_1)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a') + literal('a'), literal('b')])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 || :param_2, :param_3)") + self.assert_compile(table1.c.myid.in_([literal('a') + literal('a'), literal('b')]), + "mytable.myid IN (:param_1 || :param_2, :param_3)") - self.assert_compile(select([table1], table1.c.myid.in_([1, literal(3) + 4])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :param_1 + :param_2)") + self.assert_compile(table1.c.myid.in_([1, literal(3) + 4]), + "mytable.myid IN (:myid_1, :param_1 + :param_2)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a') < 'b'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1 < :param_2)") + self.assert_compile(table1.c.myid.in_([literal('a') < 'b']), + "mytable.myid IN (:param_1 < :param_2)") - self.assert_compile(select([table1], table1.c.myid.in_([table1.c.myid])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (mytable.myid)") + self.assert_compile(table1.c.myid.in_([table1.c.myid]), + "mytable.myid IN (mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_(['a', table1.c.myid])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, mytable.myid)") + self.assert_compile(table1.c.myid.in_(['a', table1.c.myid]), + "mytable.myid IN (:myid_1, mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, mytable.myid)") + self.assert_compile(table1.c.myid.in_([literal('a'), table1.c.myid]), + "mytable.myid IN (:param_1, mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_([literal('a'), table1.c.myid +'a'])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, mytable.myid + :myid_1)") + self.assert_compile(table1.c.myid.in_([literal('a'), table1.c.myid +'a']), + "mytable.myid IN (:param_1, mytable.myid + :myid_1)") - self.assert_compile(select([table1], table1.c.myid.in_([literal(1), 'a' + table1.c.myid])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:param_1, :myid_1 + mytable.myid)") + self.assert_compile(table1.c.myid.in_([literal(1), 'a' + table1.c.myid]), + "mytable.myid IN (:param_1, :myid_1 + mytable.myid)") - self.assert_compile(select([table1], table1.c.myid.in_([1, 2, 3])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:myid_1, :myid_2, :myid_3)") + self.assert_compile(table1.c.myid.in_([1, 2, 3]), + "mytable.myid IN (:myid_1, :myid_2, :myid_3)") - self.assert_compile(select([table1], table1.c.myid.in_(select([table2.c.otherid]))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (SELECT myothertable.otherid FROM myothertable)") + self.assert_compile(table1.c.myid.in_(select([table2.c.otherid])), + "mytable.myid IN (SELECT myothertable.otherid FROM myothertable)") - self.assert_compile(select([table1], ~table1.c.myid.in_(select([table2.c.otherid]))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (SELECT myothertable.otherid FROM myothertable)") + self.assert_compile(~table1.c.myid.in_(select([table2.c.otherid])), + "mytable.myid NOT IN (SELECT myothertable.otherid FROM myothertable)") + + # test empty in clause + self.assert_compile(table1.c.myid.in_([]), + "mytable.myid != mytable.myid") + + self.assert_compile( + select([table1.c.myid.in_(select([table2.c.otherid]))]), + "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable" + ) + self.assert_compile( + select([table1.c.myid.in_(select([table2.c.otherid]).as_scalar())]), + "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable" + ) - self.assert_compile(select([table1], table1.c.myid.in_( + self.assert_compile(table1.c.myid.in_( union( select([table1.c.myid], table1.c.myid == 5), select([table1.c.myid], table1.c.myid == 12), ) - )), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable \ -WHERE mytable.myid IN (\ -SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 \ -UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)") + ), "mytable.myid IN ("\ + "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "\ + "UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)") # test that putting a select in an IN clause does not blow away its ORDER BY clause self.assert_compile( @@ -1444,19 +1451,27 @@ UNION SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_2)") "FROM myothertable ORDER BY myothertable.othername LIMIT 10) ORDER BY mytable.myid" ) - # test empty in clause - self.assert_compile(select([table1], table1.c.myid.in_([])), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid != mytable.myid") + def test_tuple(self): + self.assert_compile(tuple_(table1.c.myid, table1.c.name).in_([(1, 'foo'), (5, 'bar')]), + "(mytable.myid, mytable.name) IN ((:param_1, :param_2), (:param_3, :param_4))" + ) self.assert_compile( - select([table1.c.myid.in_(select([table2.c.otherid]))]), - "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable" + tuple_(table1.c.myid, table1.c.name).in_( + [tuple_(table2.c.otherid, table2.c.othername)] + ), + "(mytable.myid, mytable.name) IN (myothertable.otherid, myothertable.othername)" ) + self.assert_compile( - select([table1.c.myid.in_(select([table2.c.otherid]).as_scalar())]), - "SELECT mytable.myid IN (SELECT myothertable.otherid FROM myothertable) AS anon_1 FROM mytable" + tuple_(table1.c.myid, table1.c.name).in_( + select([table2.c.otherid, table2.c.othername]) + ), + "(mytable.myid, mytable.name) IN (SELECT " + "myothertable.otherid, myothertable.othername FROM myothertable)" ) - + + def test_cast(self): tbl = table('casttest', column('id', Integer), -- 2.47.3