def test_order_by(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="a")
foo2 = Foo.new(foo_id=2, i1=23, s1="B")
foo3 = Foo.new(foo_id=3, i1=42, s1="c")
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.insert(Foo, foo3)
# order by AutoIdCol
foo_list = list(conn.select(Foo, order_by=Foo.q.foo_id.ASC))
self.assertEquals([foo1, foo2, foo3], foo_list)
foo_list = list(conn.select(Foo, order_by=Foo.q.foo_id.DESC))
self.assertEquals([foo3, foo2, foo1], foo_list)
# order by IntCol
foo_list = list(conn.select(Foo, order_by=Foo.q.i1.ASC))
self.assertEquals([foo2, foo3, foo1], foo_list)
foo_list = list(conn.select(Foo, order_by=Foo.q.i1.DESC))
self.assertEquals([foo1, foo3, foo2], foo_list)
# UnicodeCol - ignore case
foo_list = list(conn.select(Foo, order_by=Foo.q.s1.ASC))
if connect == connect_postgres:
self.assertEquals([foo2, foo1, foo3], foo_list)
else:
self.assertEquals([foo1, foo2, foo3], foo_list)
foo_list = list(conn.select(Foo, order_by=Foo.q.s1.DESC))
if connect == connect_postgres:
self.assertEquals([foo3, foo1, foo2], foo_list)
else:
self.assertEquals([foo3, foo2, foo1], foo_list)
def test_where_LIKE_ILIKE(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="ab pq")
foo2 = Foo.new(foo_id=2, i1=23, s1="AB PQ XY")
foo3 = Foo.new(foo_id=3, i1=42, s1="pq xy")
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.insert(Foo, foo3)
# where trailing %
if connect != connect_sqlite:
foo_list = conn.select(Foo, Foo.q.s1.LIKE('ab%'))
self.assertEquals([foo1], foo_list)
foo_list = conn.select(Foo, Foo.q.s1.ILIKE('ab%'))
self.assertEquals([foo1, foo2], foo_list)
# where trailing % - no matches
if connect != connect_sqlite:
foo_list = conn.select(Foo, Foo.q.s1.LIKE('%z%'))
self.assertEquals([], foo_list)
foo_list = conn.select(Foo, Foo.q.s1.ILIKE('%z%'))
self.assertEquals([], foo_list)
# where leading %
if connect != connect_sqlite:
foo_list = conn.select(Foo, Foo.q.s1.LIKE('%xy'))
self.assertEquals([foo3], foo_list)
foo_list = conn.select(Foo, Foo.q.s1.ILIKE('%xy'))
self.assertEquals([foo2, foo3], foo_list)
# where both sides %
if connect != connect_sqlite:
foo_list = conn.select(Foo, Foo.q.s1.LIKE('%x%'))
self.assertEquals([foo3], foo_list)
foo_list = conn.select(Foo, Foo.q.s1.ILIKE('%x%'))
self.assertEquals([foo2, foo3], foo_list)
def test_roundtrip_check_values(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha", d1=datetime.date(2006, 6, 12))
conn.insert(Foo, foo1)
foo_list = conn.select(Foo)
self.assertEquals([foo1], foo_list)
foo2 = foo_list[0]
Foo.check_values(foo2)
def test_delete_0_rows(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
foo2 = Foo.new(foo_id=2, i1=23, s1="beta")
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
rowcount = conn.delete(Foo, Foo.q.foo_id == 3)
self.assertEquals(0, rowcount)
foo_list = conn.select(Foo, order_by=Foo.q.foo_id.ASC)
self.assertEquals([foo1, foo2], foo_list)
def test_close_insert(self):
"Closing the connection prevents further operations on the connection."
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
conn.insert(Foo, foo1)
conn.close()
foo2 = Foo.new(foo_id=2, s1="beta")
try:
conn.insert(Foo, foo2)
except AssertionError, e:
self.assertEquals("Connection is closed", str(e))
def test_delete_all(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
foo2 = Foo.new(foo_id=2, s1="beta")
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
found = conn.delete(Foo)
self.assertEquals(2, found)
self.assertEquals([], conn.select(Foo))
found = conn.delete(Foo)
self.assertEquals(0, found)
def test_new_parse_some_fields(self):
expected = {
"foo_id": 42,
"i1": 0,
"s1": "alpha",
"d1": None,
}
actual = Foo.new(foo_id=42, s1="alpha")
self.assertEquals(expected, actual)
actual = Foo.parse(foo_id="42", s1="alpha")
self.assertEquals(expected, actual)
def test_parse_error(self):
# parse() gives parse error for bad strings
try:
Foo.parse(i1="2.3", s2=1.1)
except ValueError, e:
self.assert_(
str(e) in [
"invalid literal for int(): 2.3",
"invalid literal for int() with base 10: '2.3'",
]
)
def test_select(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
foo2 = Foo.new(foo_id=2, s1="beta")
conn.insert(Foo, foo1)
foo_list = conn.select(Foo)
self.assertEquals([foo1], foo_list)
conn.insert(Foo, foo2)
foo_list = conn.select(Foo)
# FIXME: depending on db order
self.assertEquals([foo1, foo2], foo_list)
def test_insert_ascii(self):
foo1 = Foo.new(foo_id=1, s1=S_ENGLISH_ASCII)
foo2 = Foo.new(foo_id=2, s1=S_ENGLISH_U)
conn = connect()
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.commit()
conn.close()
conn = connect()
foo_list = conn.select(Foo, order_by=Foo.q.foo_id.ASC)
self.assertEquals([foo1, foo2], foo_list)
conn.close()
def test_insert_unicode(self):
foo1 = Foo.new(foo_id=1, s1=S_SPANISH_U)
foo2 = Foo.new(foo_id=2, s1=S_FRENCH_U)
conn = connect()
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.commit()
conn.close()
conn = connect()
foo_list = conn.select(Foo, order_by=Foo.q.foo_id.ASC)
self.assertEquals([foo1, foo2], foo_list)
conn.close()
def test_delete_by_id_more_than_one_row(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101)
foo2 = Foo.new(foo_id=2, i1=101)
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
Foo2 = Table("foo", AutoIdCol("i1"), IntCol("foo_id"))
try:
conn.delete_by_id(Foo2, 101)
except AssertionError, e:
expected = "delete_by_id(): more than 1 row deleted", ("foo", "i1", 101, 2)
self.assertEquals(expected, get_assert_tuple_args(e))
def test_where_gt_ge_lt_le(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha", d1=datetime.date(2005, 11, 24))
foo2 = Foo.new(foo_id=2, i1=23, s1="beta", d1=datetime.date(2006, 2, 16))
foo3 = Foo.new(foo_id=3, i1=42, s1="alpha", d1=datetime.date(2006, 6, 10))
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.insert(Foo, foo3)
# where __gt__ & AutoIdCol
foo_list = conn.select(Foo, Foo.q.foo_id > 2)
self.assertEquals([foo3], foo_list)
# where __ge__ & UnicodeCol
foo_list = conn.select(Foo, Foo.q.s1 >= 'beta')
self.assertEquals([foo2], foo_list)
# where __lt__ & IntCol
foo_list = conn.select(Foo, Foo.q.i1 < 42)
self.assertEquals([foo2], foo_list)
# where __le__ & IntCol
foo_list = conn.select(Foo, Foo.q.i1 <= 42)
# FIXME: depending on db order
self.assertEquals([foo2, foo3], foo_list)
# where AND, AutoIdCol, gt, IntCol, lt
foo_list = conn.select(Foo, AND(Foo.q.foo_id > 1, Foo.q.i1 < 30))
self.assertEquals([foo2], foo_list)
# where AND, DateCol, gt, lt
foo_list = conn.select(
Foo,
AND(
Foo.q.d1 > datetime.date(2005, 11, 24),
Foo.q.d1 < datetime.date(2006, 6, 10),
)
)
self.assertEquals([foo2], foo_list)
# where AND, DateCol, gt, lt
foo_list = conn.select(
Foo,
AND(
Foo.q.d1 > datetime.date(2005, 11, 24),
Foo.q.d1 < datetime.date(2006, 6, 10),
)
)
self.assertEquals([foo2], foo_list)
# where AND, DateCol, gt, le
foo_list = conn.select(
Foo,
AND(
Foo.q.d1 >= datetime.date(2005, 11, 24),
Foo.q.d1 <= datetime.date(2006, 6, 10),
)
)
# FIXME: depending on db order
self.assertEquals([foo1, foo2, foo3], foo_list)
def test_select_insert(self):
"New operation closes a partly consumed cursor."
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
foo2 = Foo.new(foo_id=2, i1=23, s1="beta")
conn.insert(Foo, foo1)
foo_iter = conn.xselect(Foo)
conn.insert(Foo, foo2)
try:
foo_iter.next()
except conn.DbError, e:
self.assertEquals("Result cursor closed.", str(e))
def test_new_parse_unkown_cols(self):
# DONT copy unknown columns
expected = {
"foo_id": None,
"i1": 16,
"s1": "",
"d1": None,
}
actual = Foo.new(i1=16, s2="beta")
self.assertEquals(expected, actual)
actual = Foo.parse(i1="16", s2="beta")
self.assertEquals(expected, actual)
def test_auto_id(self):
conn = connect()
foo1 = Foo.new(i1=101, s1="alpha")
self.assert_(foo1["foo_id"] is None)
conn.insert(Foo, foo1)
self.assertEquals(1, foo1["foo_id"])
foo2 = Foo.new(s1="beta")
self.assert_(foo2["foo_id"] is None)
conn.insert(Foo, foo2)
self.assertEquals(2, foo2["foo_id"])
foo_list = conn.select(Foo)
# FIXME: depending on db order
self.assertEquals([foo1, foo2], foo_list)
def test_update_1_row(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101, s1="alpha")
foo2 = Foo.new(foo_id=2, i1=23, s1="beta")
foo3 = Foo.new(foo_id=3, i1=42, s1="alpha", d1=datetime.date(2006, 6, 10))
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.insert(Foo, foo3)
foo4a = Foo.new(foo_id=4, i1=102, s1="gamma")
rowcount = conn.update(Foo, foo4a, Foo.q.foo_id == 2)
self.assertEquals(1, rowcount)
foo_list = conn.select(Foo, order_by=Foo.q.foo_id.ASC)
self.assertEquals([foo1, foo3, foo4a], foo_list)
def test_update_by_id_more_than_one_row(self):
conn = connect()
foo1 = Foo.new(foo_id=1, i1=101)
foo2 = Foo.new(foo_id=2, i1=101)
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
Foo2 = Table("foo", AutoIdCol("i1"), UnicodeCol("s1", 10))
try:
foo3 = Foo2.new(i1=101, s1="xyz")
conn.update_by_id(Foo2, foo3)
except AssertionError, e:
expected = "update_by_id(): more than 1 row updated", ("foo", "i1", 101, 2)
self.assertEquals(expected, get_assert_tuple_args(e))
def test_new_parse_all(self):
expected = {
"foo_id": 42,
"i1": 101,
"s1": "alpha",
"d1": date(2006,6,6),
}
actual = Foo.new(foo_id=42, i1=101, s1="alpha", d1=date(2006,6,6))
self.assertEquals(expected, actual)
actual = Foo.parse(foo_id="42", i1="101", s1="alpha", d1="2006-06-06")
self.assertEquals(expected, actual)
# parse some fields str
actual = Foo.parse(foo_id="42", i1=101, s1="alpha", d1=date(2006,6,6))
self.assertEquals(expected, actual)
def test_new_parse_clone(self):
# new() and parse() should return a new dictionary
expected = {
"foo_id": 42,
"i1": 0,
"s1": "alpha",
"d1": None,
}
actual = Foo.new(**expected)
self.assertEquals(expected, actual)
self.assertFalse(actual is expected)
actual = Foo.parse(**expected)
self.assertEquals(expected, actual)
self.assertFalse(actual is expected)
def test_update_by_id_RO(self):
conn = connect("test123")
try:
foo1 = Foo.new(i1=101, s1="xyz")
conn.update_by_id(Foo, foo1)
except Exception, e:
self.assertEquals("Connection is read only: test123", str(e))
def test_bad_values(self):
foo = Foo.new()
foo["i1"] = "xyz"
try:
sqlgen.insert(Foo, foo, sqlgen.DIALECT_SQLITE, "?")
except TypeError, e:
self.assertEquals("IntCol 'i1': int expected, got str", str(e))
def test_RO(self):
conn = connect("test123")
try:
foo = Foo.new(foo_id=1, i1=101, s1="alpha")
conn.insert(Foo, foo)
except Exception, e:
self.assertEquals("Connection is read only: test123", str(e))
def test(self):
foo = Foo.new(foo_id=4, i1=23, s1="pqr", d1=datetime.date(2006, 5, 4))
bar = Bar.new(
bi=5, bs="abc", bd=datetime.date(2006, 3, 21), bdt1=datetime.datetime(2005, 11, 22, 0, 43, 12), bb=True
)
# no where condition
sql, values = sqlgen.update(Foo, foo, None, sqlgen.DIALECT_SQLITE, "?")
self.assertEquals("UPDATE foo SET foo_id=?,i1=?,s1=?,d1=?", sql)
self.assertEquals([4, 23, u"pqr", "2006-05-04"], values)
# AutoIdCol
sql, values = sqlgen.update(Foo, foo, Foo.q.foo_id == 2, sqlgen.DIALECT_MYSQL, "%s")
self.assertEquals("UPDATE foo SET foo_id=%s,i1=%s,s1=%s,d1=%s WHERE foo_id=%s", sql)
self.assertEquals([4, 23, u"pqr", "2006-05-04", 2], values)
# IntCol
sql, values = sqlgen.update(Foo, foo, Foo.q.i1 == 32, sqlgen.DIALECT_SQLITE, "?")
self.assertEquals("UPDATE foo SET foo_id=?,i1=?,s1=?,d1=? WHERE i1=?", sql)
self.assertEquals([4, 23, u"pqr", "2006-05-04", 32], values)
# IntCol AND UnicodeCol
sql, values = sqlgen.update(Foo, foo, AND(Foo.q.i1 == 12, Foo.q.s1 == "aeiou"), sqlgen.DIALECT_MYSQL, "%s")
self.assertEquals("UPDATE foo SET foo_id=%s,i1=%s,s1=%s,d1=%s WHERE i1=%s AND s1=%s", sql)
self.assertEquals([4, 23, u"pqr", "2006-05-04", 12, "aeiou"], values)
# IntCol AND DateCol / NULL
sql, values = sqlgen.update(Bar, bar, AND(Bar.q.bi == 12, Bar.q.bd == None), sqlgen.DIALECT_SQLITE, "?")
self.assertEquals("UPDATE bar SET bi=?,bs=?,bd=?,bdt1=?,bb=? WHERE bi=? AND bd is NULL", sql)
self.assertEquals([5, u"abc", "2006-03-21", "2005-11-22T00:43:12Z", 1, 12], values)
# IntCol OR UnicodeCol
sql, values = sqlgen.update(Foo, foo, OR(Foo.q.i1 == 12, Foo.q.s1 == "aeiou"), sqlgen.DIALECT_MYSQL, "%s")
self.assertEquals("UPDATE foo SET foo_id=%s,i1=%s,s1=%s,d1=%s WHERE i1=%s OR s1=%s", sql)
self.assertEquals([4, 23, u"pqr", "2006-05-04", 12, "aeiou"], values)
# IntCol OR DateCol / NULL
sql, values = sqlgen.update(Bar, bar, OR(Bar.q.bi == 12, Bar.q.bd == None), sqlgen.DIALECT_SQLITE, "?")
self.assertEquals("UPDATE bar SET bi=?,bs=?,bd=?,bdt1=?,bb=? WHERE bi=? OR bd is NULL", sql)
self.assertEquals([5, u"abc", "2006-03-21", "2005-11-22T00:43:12Z", 1, 12], values)
def test_intcol_roundtrip(self):
import sys
conn = connect()
foo = Foo.new(foo_id=1, i1=sys.maxint)
conn.insert(Foo, foo)
foo_list = conn.select(Foo)
self.assertEquals([foo], foo_list)
def test_update_by_id(self):
foo1 = Foo.new(foo_id=1, s1=S_SPANISH_U)
foo2 = Foo.new(foo_id=2, s1=S_FRENCH_U)
conn = connect()
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.commit()
conn.close()
conn = connect()
foo2["s1"] = S_GERMAN_U
conn.update_by_id(Foo, foo2)
conn.commit()
conn.close()
conn = connect()
foo2b = conn.get(Foo, 2)
self.assertEquals(foo2, foo2b)
conn.close()
def test_bad_values(self):
foo = Foo.new()
foo["foo_id"] = 6
foo["i1"] = "xyz"
try:
sqlgen.update_by_id(Foo, foo, "?")
except TypeError, e:
self.assertEquals("IntCol 'i1': int expected, got str", str(e))
def test_auto_id(self):
foo = Foo.new(foo_id=None, i1=25, s1="xyz")
sql, values, auto_id_used = sqlgen.insert(Foo, foo, sqlgen.DIALECT_SQLITE, "?")
self.assertEquals("INSERT INTO foo (i1,s1,d1) VALUES (?,?,NULL)", sql)
self.assertEquals([25, "xyz"], values)
sql, values, auto_id_used = sqlgen.insert(Foo, foo, sqlgen.DIALECT_POSTGRES, "?")
self.assertEquals("INSERT INTO foo (i1,s1,d1) VALUES (?,?,NULL) RETURNING foo_id", sql)
self.assertEquals([25, "xyz"], values)
self.assert_(auto_id_used)
def test_insert_nonascii(self):
foo1 = Foo.new(foo_id=1, s1=S_GERMAN_U)
foo2 = Foo.new(foo_id=2, s1=S_GERMAN_UTF8)
conn = connect()
conn.insert(Foo, foo1)
try:
conn.insert(Foo, foo2)
except UnicodeDecodeError, e:
self.assertEquals(
(
'ascii',
S_GERMAN_UTF8,
1,
2,
'ordinal not in range(128)'
),
e.args
)
def test_update_where(self):
foo1 = Foo.new(foo_id=1, s1=S_SPANISH_U)
foo2 = Foo.new(foo_id=2, s1=S_FRENCH_U)
conn = connect()
conn.insert(Foo, foo1)
conn.insert(Foo, foo2)
conn.commit()
conn.close()
conn = connect()
foo3 = Foo.new(foo_id=3, s1=S_ENGLISH_ASCII)
rc = conn.update(Foo, foo3, Foo.q.s1 == S_FRENCH_U)
self.assertEquals(1, rc)
conn.commit()
conn.close()
conn = connect()
foo_list = conn.select(Foo, order_by=Foo.q.foo_id.ASC)
self.assertEquals([foo1, foo3], foo_list)
conn.close()