def test_update_all_rows(self):
conn = connect()
bar1 = Bar.new(bi=2001, bs="abc")
bar2 = Bar.new(bi=2002, bs="abc")
conn.insert(Bar, bar1)
conn.insert(Bar, bar2)
bar4a = Bar.new(bi=2004, bs="ghi")
rowcount = conn.update(Bar, bar4a, None)
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar4a, bar4a], bar_list)
def test_update_RO(self):
conn = connect("test123")
try:
bar1 = Bar.new(bi=2001, bs="abc")
conn.update(Bar, bar1, Bar.q.bi == 2002)
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_no_auto_id_col(self):
bar = Bar.new(
bi=5, bs="abc", bd=datetime.date(2006, 3, 21), bdt1=datetime.datetime(2006, 4, 13, 23, 58, 14), bb=True
)
sql, values, auto_id_used = sqlgen.insert(Bar, bar, sqlgen.DIALECT_POSTGRES, "%s")
self.assertEquals("INSERT INTO bar (bi,bs,bd,bdt1,bb) VALUES (%s,%s,%s,%s,%s)", sql)
self.assertEquals([5, u"abc", "2006-03-21", "2006-04-13T23:58:14Z", 1], values)
self.assertFalse(auto_id_used)
def test_delete_multiple_rows(self):
conn = connect()
bar1 = Bar.new(bi=2001, bs="abc")
bar2 = Bar.new(bi=2002, bs="abc")
bar3 = Bar.new(bi=2004, bs="def", bd=datetime.date(2006, 6, 10))
bar4 = Bar.new(bi=2004, bs="ghi")
conn.insert(Bar, bar1)
conn.insert(Bar, bar2)
conn.insert(Bar, bar3)
conn.insert(Bar, bar4)
rowcount = conn.delete(Bar, Bar.q.bs == "abc")
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar3, bar4], bar_list)
rowcount = conn.delete(Bar, Bar.q.bi == 2004)
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([], bar_list)
def test_delete_gt_ge(self):
conn = connect()
bar1 = Bar.new(bi=2001, bs="abc")
bar2 = Bar.new(bi=2002, bs="abc")
bar3 = Bar.new(bi=2004, bs="def", bd=datetime.date(2006, 6, 10))
bar4 = Bar.new(bi=2004, bs="ghi")
conn.insert(Bar, bar1)
conn.insert(Bar, bar2)
conn.insert(Bar, bar3)
conn.insert(Bar, bar4)
# gt
rowcount = conn.delete(Bar, Bar.q.bs > "abc")
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar2], bar_list)
# ge
rowcount = conn.delete(Bar, Bar.q.bi >= 2001)
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar)
self.assertEquals([], bar_list)
def test_new_parse_defaults(self):
expected = {
"foo_id": None,
"i1": 0,
"s1": "",
"d1": None,
}
actual = Foo.new()
self.assertEquals(expected, actual)
actual = Foo.parse()
self.assertEquals(expected, actual)
expected = {
"bi": None,
"bs": "",
"bd": None,
"bdt1": None,
"bb": False,
}
actual = Bar.new()
self.assertEquals(expected, actual)
actual = Bar.parse()
self.assertEquals(expected, actual)
def test_parse_empty_string(self):
# parse() replaces empty strings with default value
expected = {
"foo_id": None,
"i1": 0,
"s1": "",
"d1": None,
}
actual = Foo.parse(foo_id="", i1="", s1="", d1="")
self.assertEquals(expected, actual)
expected = {
"bi": None,
"bs": "",
"bd": None,
"bdt1": None,
"bb": False,
}
actual = Bar.parse(bi="", bs="", bd="", bdt1="", bb="")
self.assertEquals(expected, actual)
def test_update_multiple_rows(self):
conn = connect()
bar1 = Bar.new(bi=2001, bs="abc")
bar2 = Bar.new(bi=2002, bs="abc")
bar3 = Bar.new(bi=2002, bs="def", bd=datetime.date(2006, 6, 10))
conn.insert(Bar, bar1)
conn.insert(Bar, bar2)
conn.insert(Bar, bar3)
bar4a = Bar.new(bi=2004, bs="ghi")
rowcount = conn.update(Bar, bar4a, Bar.q.bi == 2002)
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar4a, bar4a], bar_list)
bar5a = Bar.new(bi=2005, bs="jkl")
rowcount = conn.update(Bar, bar5a, AND(Bar.q.bi == 2004, Bar.q.bs == "ghi"))
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar5a, bar5a], bar_list)
bar6a = Bar.new(bi=2006, bs="mno", bd=datetime.date(2006, 11, 22))
rowcount = conn.update(Bar, bar6a, Bar.q.bd == None)
self.assertEquals(3, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar6a, bar6a, bar6a], bar_list)
def test_update_gt_ge_lt_le(self):
conn = connect()
bar1 = Bar.new(bi=2001, bs="abc")
bar2 = Bar.new(bi=2002, bs="abc")
bar3 = Bar.new(bi=2003, bs="def", bd=datetime.date(2006, 6, 10))
conn.insert(Bar, bar1)
conn.insert(Bar, bar2)
conn.insert(Bar, bar3)
bar4a = Bar.new(bi=2004, bs="ghi")
# gt
rowcount = conn.update(Bar, bar4a, Bar.q.bi > 2002)
self.assertEquals(1, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar2, bar4a], bar_list)
# ge
rowcount = conn.update(Bar, bar4a, Bar.q.bi >= 2002)
if connect == connect_mysql:
# MySQL doesn't count matching rows where values don't change
# (bar3 already has same values being set by bar4a)
self.assertEquals(1, rowcount)
else:
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar4a, bar4a], bar_list)
# lt
bar5a = Bar.new(bi=2005, bs="jkl")
rowcount = conn.update(Bar, bar5a, AND(Bar.q.bi < 2005, Bar.q.bs == "ghi"))
self.assertEquals(2, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar1, bar5a, bar5a], bar_list)
# le
bar6a = Bar.new(bi=2006, bs="mno", bd=datetime.date(2006, 11, 22))
rowcount = conn.update(Bar, bar6a, Bar.q.bi <= 2005)
self.assertEquals(3, rowcount)
bar_list = conn.select(Bar, order_by=Bar.q.bi.ASC)
self.assertEquals([bar6a, bar6a, bar6a], bar_list)
def test_no_auto_id_col(self):
bar = Bar.new(bi=5, bs="abc", bd=datetime.date(2006, 3, 21))
try:
sqlgen.update_by_id(Bar, bar, "?")
except AssertionError, e:
self.assertEquals("update_by_id(): table 'bar' does not have AutoIdCol", str(e))
