From 93d212a138a4eeb94803821d9af461867f538ed5 Mon Sep 17 00:00:00 2001 From: Jason Kirtland Date: Thu, 19 Apr 2007 19:35:03 +0000 Subject: [PATCH] - merged in the combined patch for #474, #475, #476 (attached to #476) and a new set of tests --- lib/sqlalchemy/sql.py | 46 +++++++++++++++------ test/sql/select.py | 94 ++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 126 insertions(+), 14 deletions(-) diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index c3d0f9de08..280ebd81c7 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -914,25 +914,45 @@ class _CompareMixin(object): """produce an ``IN`` clause.""" if len(other) == 0: return self.__eq__(None) - elif len(other) == 1 and not hasattr(other[0], '_selectable'): - return self.__eq__(other[0]) - elif _is_literal(other[0]): - return self._compare('IN', ClauseList(parens=True, *[self._bind_param(o) for o in other]), negate='NOT IN') - else: - # assume *other is a single select. - # originally, this assumed possibly multiple selects and created a UNION, - # but we are now forcing explictness if a UNION is desired. - if len(other) > 1: - raise exceptions.InvalidRequestException("in() function accepts only multiple literal values, or a single selectable as an argument") - return self._compare('IN', other[0], negate='NOT IN') + elif len(other) == 1: + o = other[0] + if _is_literal(o) or isinstance( o, _CompareMixin): + return self.__eq__( o) #single item -> == + else: + assert hasattr( o, '_selectable') #better check? + return self._compare( 'IN', o, negate='NOT IN') #single selectable + + args = [] + for o in other: + if not _is_literal(o): + if not isinstance( o, _CompareMixin): + raise exceptions.InvalidRequestError( "in() function accepts either non-selectable values, or a single selectable: "+repr(o) ) + else: + o = self._bind_param(o) + args.append(o) + return self._compare( 'IN', ClauseList( parens=True, *args), negate='NOT IN') def startswith(self, other): """produce the clause ``LIKE '%'``""" - return self._compare('LIKE', other + "%") + perc = isinstance(other,(str,unicode)) and '%' or literal('%',type= sqltypes.String) + return self._compare('LIKE', other + perc) def endswith(self, other): """produce the clause ``LIKE '%'``""" - return self._compare('LIKE', "%" + other) + if isinstance(other,(str,unicode)): po = '%' + other + else: + po = literal('%', type= sqltypes.String) + other + po.type = sqltypes.to_instance( sqltypes.String) #force! + return self._compare('LIKE', po) + + def __radd__(self, other): + return self._bind_param(other)._operate('+', self) + def __rsub__(self, other): + return self._bind_param(other)._operate('-', self) + def __rmul__(self, other): + return self._bind_param(other)._operate('*', self) + def __rdiv__(self, other): + return self._bind_param(other)._operate('/', self) def label(self, name): """produce a column label, i.e. `` AS ``""" diff --git a/test/sql/select.py b/test/sql/select.py index 1d0a63e2f6..c10f12c2c6 100644 --- a/test/sql/select.py +++ b/test/sql/select.py @@ -2,7 +2,7 @@ from testbase import PersistTest import testbase from sqlalchemy import * from sqlalchemy.databases import sqlite, postgres, mysql, oracle, firebird, mssql -import unittest, re +import unittest, re, operator # the select test now tests almost completely with TableClause/ColumnClause objects, @@ -246,6 +246,50 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A literal("a") + literal("b") * literal("c"), ":literal + (:literal_1 * :literal_2)" ) + # exercise arithmetic operators + for (py_op, sql_op) in ((operator.add, '+'), (operator.mul, '*'), + (operator.sub, '-'), (operator.div, '/'), + ): + for (lhs, rhs, res) in ( + ('a', table1.c.myid, ':mytable_myid %s mytable.myid'), + ('a', literal('b'), ':literal %s :literal_1'), + (table1.c.myid, 'b', 'mytable.myid %s :mytable_myid'), + (table1.c.myid, literal('b'), 'mytable.myid %s :literal'), + (table1.c.myid, table1.c.myid, 'mytable.myid %s mytable.myid'), + (literal('a'), 'b', ':literal %s :literal_1'), + (literal('a'), table1.c.myid, ':literal %s mytable.myid'), + (literal('a'), literal('b'), ':literal %s :literal_1'), + ): + self.runtest(py_op(lhs, rhs), res % sql_op) + + # exercise comparison operators + for (py_op, fwd_op, rev_op) in ((operator.lt, '<', '>'), + (operator.gt, '>', '<'), + (operator.eq, '=', '='), + (operator.ne, '!=', '!='), + (operator.le, '<=', '>='), + (operator.ge, '>=', '<=')): + for (lhs, rhs, l_sql, r_sql) in ( + ('a', table1.c.myid, ':mytable_myid', 'mytable.myid'), + ('a', literal('b'), ':literal_1', ':literal'), # note swap! + (table1.c.myid, 'b', 'mytable.myid', ':mytable_myid'), + (table1.c.myid, literal('b'), 'mytable.myid', ':literal'), + (table1.c.myid, table1.c.myid, 'mytable.myid', 'mytable.myid'), + (literal('a'), 'b', ':literal', ':literal_1'), + (literal('a'), table1.c.myid, ':literal', 'mytable.myid'), + (literal('a'), literal('b'), ':literal', ':literal_1'), + ): + + # the compiled clause should match either (e.g.): + # 'a' < 'b' -or- 'b' > 'a'. + compiled = str(py_op(lhs, rhs)) + fwd_sql = "%s %s %s" % (l_sql, fwd_op, r_sql) + rev_sql = "%s %s %s" % (r_sql, rev_op, l_sql) + + self.assert_(compiled == fwd_sql or compiled == rev_sql, + "\n'" + compiled + "'\n does not match\n'" + + fwd_sql + "'\n or\n'" + rev_sql + "'") + # test the op() function, also that its results are further usable in expressions self.runtest( table1.select(table1.c.myid.op('hoho')(12)==14), @@ -700,6 +744,54 @@ myothertable.othername != :myothertable_othername AND EXISTS (select yay from fo assert [str(c) for c in s.c] == ["id", "hoho"] def testin(self): + self.runtest(select([table1], table1.c.myid.in_('a')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid") + + self.runtest(select([table1], table1.c.myid.in_('a', 'b')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a'))), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :literal") + + self.runtest(select([table1], table1.c.myid.in_(literal('a'), 'b')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a'), literal('b'))), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :literal_1)") + + self.runtest(select([table1], table1.c.myid.in_('a', literal('b'))), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a') + 'a')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (:literal + :literal_1)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a') +'a', 'b')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal + :literal_1, :mytable_myid)") + + self.runtest(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 (:literal + :literal_1, :literal_2)") + + self.runtest(select([table1], table1.c.myid.in_('a', literal('b') +'b')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :literal + :literal_1)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a') < 'b')), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (:literal < :literal_1)") + + self.runtest(select([table1], table1.c.myid.in_(table1.c.myid)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = mytable.myid") + + self.runtest(select([table1], table1.c.myid.in_('a', table1.c.myid)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, mytable.myid)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a'), table1.c.myid)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, mytable.myid)") + + self.runtest(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 (:literal, mytable.myid + :mytable_myid)") + + self.runtest(select([table1], table1.c.myid.in_(literal('a'), 'a' + table1.c.myid)), + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:literal, :mytable_myid + mytable.myid)") + self.runtest(select([table1], table1.c.myid.in_(1, 2, 3)), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1, :mytable_myid_2)") -- 2.47.2