def test_where_DAY(self): from datetime import date # non DateCol try: sqlgen.select(Foo, Foo.q.i1.DAY(12), None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("DAY condition can only be used for DateCol", str(e))
def test_order_by(self): # ASC sql, values = sqlgen.select(Foo, None, Foo.q.foo_id.ASC, sqlgen.DIALECT_SQLITE, "?") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo ORDER BY foo_id ASC", sql) self.assertEquals([], values) # DESC sql, values = sqlgen.select(Foo, None, Foo.q.s1.DESC, sqlgen.DIALECT_MYSQL, "%s") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo ORDER BY s1 DESC", sql) self.assertEquals([], values)
def x(): sql, values = sqlgen.select(Bar, Bar.q.bs == "x", None, sqlgen.DIALECT_SQLITE, "?") self.assertEquals("SELECT bi,bs,bd FROM bar WHERE bs=?", sql) self.assertEquals(["x"], values) sql, values = sqlgen.select( Foo, AND(Foo.q.i1 == 12, Foo.q.d1 == None, Foo.q.s1 == "y"), None, sqlgen.DIALECT_SQLITE, "?" ) self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE i1=? AND d1 is NULL AND s1=?", sql) self.assertEquals([12, "y"], values)
def test_where_gt_ge_lt_le(self): sql, values = sqlgen.select(Foo, Foo.q.foo_id > 1, None, sqlgen.DIALECT_MYSQL, "%s") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE foo_id>%s", sql) self.assertEquals([1], values) sql, values = sqlgen.select(Bar, Bar.q.bs >= "x", None, sqlgen.DIALECT_MYSQL, "%s") self.assertEquals("SELECT bi,bs,bd,bdt1,bb FROM bar WHERE bs>=%s", sql) self.assertEquals(["x"], values) sql, values = sqlgen.select(Foo, AND(Foo.q.i1 < 12, Foo.q.s1 <= "y"), None, sqlgen.DIALECT_SQLITE, "?") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE i1<? AND s1<=?", sql) self.assertEquals([12, "y"], values) # None/NULL def check_no_None(cond, opstr): try: sqlgen.select(Bar, cond, None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("Op '%s' does not support None" % opstr, str(e)) else:
def xselect(self, table, where=None, order_by=None): # gen sql sql, values = sqlgen.select( table, where, order_by, self.dialect, self.paramstr ) # execute sql cursor = self._execute(sql, values) # result iterator i = ResultIterator(cursor, table, self.DbError, where) self._last_ri = i return i
def test_where_eq(self): sql, values = sqlgen.select(Foo, Foo.q.foo_id == 1, None, sqlgen.DIALECT_MYSQL, "%s") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE foo_id=%s", sql) self.assertEquals([1], values) sql, values = sqlgen.select(Bar, Bar.q.bs == "x", None, sqlgen.DIALECT_SQLITE, "?") self.assertEquals("SELECT bi,bs,bd,bdt1,bb FROM bar WHERE bs=?", sql) self.assertEquals(["x"], values) # sql, values = sqlgen.select(Bar, Bar.q.bs.LIKE("x%"), None, sqlgen.DIALECT_MYSQL, "%s") self.assertEquals("SELECT bi,bs,bd,bdt1,bb FROM bar WHERE bs COLLATE utf8_bin LIKE %s", sql) self.assertEquals(["x%"], values) # sql, values = sqlgen.select( Foo, AND(Foo.q.i1 == 12, Foo.q.d1 == None, Foo.q.s1 == "y"), None, sqlgen.DIALECT_SQLITE, "?" ) self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE i1=? AND d1 is NULL AND s1=?", sql) self.assertEquals([12, "y"], values) sql, values = sqlgen.select( Foo, OR(Foo.q.i1 == 12, Foo.q.d1 == None, Foo.q.s1 == "y"), None, sqlgen.DIALECT_SQLITE, "?" ) self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo WHERE i1=? OR d1 is NULL OR s1=?", sql) self.assertEquals([12, "y"], values)
def check_no_None(cond, opstr): try: sqlgen.select(Bar, cond, None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("Op '%s' does not support None" % opstr, str(e))
def test(self): sql, values = sqlgen.select(Foo, None, None, sqlgen.DIALECT_SQLITE, "?") self.assertEquals("SELECT foo_id,i1,s1,d1 FROM foo", sql) self.assertEquals([], values)
# None/NULL def check_no_None(cond, opstr): try: sqlgen.select(Bar, cond, None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("Op '%s' does not support None" % opstr, str(e)) else: self.fail() check_no_None(Bar.q.bi > None, ">") check_no_None(Bar.q.bi >= None, ">=") check_no_None(Bar.q.bi < None, "<") check_no_None(Bar.q.bi <= None, "<=") # BoolCol try: sqlgen.select(Bar, Bar.q.bb > False, None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("Op '>' does not support BoolCol", str(e)) else: self.fail() # DateTimeUTCCol try: sqlgen.select(Bar, Bar.q.bdt1 < datetime.datetime(2007, 5, 23, 0, 42, 12), None, sqlgen.DIALECT_SQLITE, "?") except AssertionError, e: self.assertEquals("Op '<' does not support DateTimeUTCCol", str(e)) else: self.fail() def test_where_YEAR(self): from datetime import date