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))