def testDeadlocks(self):
def process(cnn, cur, val):
try:
cur.execute("begin")
cur.execute("insert into tbltest (test_id) values (1)")
cur.execute("select sleep(2)")
cur.execute("update tbltest set test_id=%d" % val)
cur.execute("select sleep(2)")
cur.execute("commit")
return False
except dbapi.Error as e:
return "deadlock" in str(e).lower()
cnn1 = dbapi.connect(host = DB_HOST, user = DB_USER, passwd = DB_PASSWD, db = DB_DB)
cur1 = cnn1.cursor()
cnn2 = dbapi.connect(host = DB_HOST, user = DB_USER, passwd = DB_PASSWD, db = DB_DB)
cur2 = cnn2.cursor()
t1 = Tasklet.new(process)(cnn1, cur1, 2)
t2 = Tasklet.new(process)(cnn2, cur2, 3)
res = Tasklet.join_all([t1, t2])
self.assertTrue(res[0] or res[1],
'At least one of the queries expected to fail due to deadlock (innodb must be used)')
# Both connections must survive after error
cur1.execute("select 1")
cur2.execute("select 2")
cur1.close()
cnn1.close()
cur2.close()
cnn2.close()
def testSelectUnicode(self):
s = u"Céline"
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB,
charset = 'latin-1', use_unicode = True)
cur = cnn.cursor()
cur.execute("truncate tbltest")
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (1, 'piet'))
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (2, s))
cur.execute(u"insert into tbltest (test_id, test_string) values (%s, %s)", (3, s))
cur.execute("select test_id, test_string from tbltest")
result = cur.fetchall()
self.assertEquals([(1, u'piet'), (2, u'C\xe9line'), (3, u'C\xe9line')], result)
#test that we can still cleanly roundtrip a blob, (it should not be encoded if we pass
#it as 'str' argument), eventhough we pass the qry itself as unicode
blob = ''.join([chr(i) for i in range(256)])
cur.execute(u"insert into tbltest (test_id, test_blob) values (%s, %s)", (4, blob))
cur.execute("select test_blob from tbltest where test_id = %s", (4,))
b2 = cur.fetchall()[0][0]
self.assertEquals(str, type(b2))
self.assertEquals(256, len(b2))
self.assertEquals(blob, b2)
def query(s):
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB)
cur = cnn.cursor()
cur.execute("select sleep(%d)" % s)
cur.close()
cnn.close()
def query(s):
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB)
cur = cnn.cursor()
cur.execute("select sleep(%d)" % s)
cur.close()
cnn.close()
def testSelectUnicode(self):
s = u"Céline"
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB,
charset = 'latin-1', use_unicode = True)
cur = cnn.cursor()
cur.execute("truncate tbltest")
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (1, 'piet'))
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (2, s))
cur.execute(u"insert into tbltest (test_id, test_string) values (%s, %s)", (3, s))
cur.execute("select test_id, test_string from tbltest")
result = cur.fetchall()
self.assertEquals([(1, u'piet'), (2, u'C\xe9line'), (3, u'C\xe9line')], result)
#test that we can still cleanly roundtrip a blob, (it should not be encoded if we pass
#it as 'str' argument), eventhough we pass the qry itself as unicode
blob = ''.join([chr(i) for i in range(256)])
cur.execute(u"insert into tbltest (test_id, test_blob) values (%s, %s)", (4, blob))
cur.execute("select test_blob from tbltest where test_id = %s", (4,))
b2 = cur.fetchall()[0][0]
self.assertEquals(str, type(b2))
self.assertEquals(256, len(b2))
self.assertEquals(blob, b2)
def testAutoInc(self):
cnn = dbapi.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASSWD,
db=DB_DB)
cur = cnn.cursor()
cur.execute("truncate tblautoincint")
cur.execute("ALTER TABLE tblautoincint AUTO_INCREMENT = 100")
cur.execute("insert into tblautoincint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(100, cur.lastrowid)
cur.execute("insert into tblautoincint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(101, cur.lastrowid)
cur.execute("ALTER TABLE tblautoincint AUTO_INCREMENT = 4294967294")
cur.execute("insert into tblautoincint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(4294967294, cur.lastrowid)
cur.execute("insert into tblautoincint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(4294967295, cur.lastrowid)
cur.execute("truncate tblautoincbigint")
cur.execute("ALTER TABLE tblautoincbigint AUTO_INCREMENT = 100")
cur.execute("insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(100, cur.lastrowid)
cur.execute("insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(101, cur.lastrowid)
cur.execute(
"ALTER TABLE tblautoincbigint AUTO_INCREMENT = 18446744073709551614"
)
cur.execute("insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, cur.rowcount)
self.assertEqual(18446744073709551614, cur.lastrowid)
#this fails on mysql, but that is a mysql problem
#cur.execute("insert into tblautoincbigint (test_string) values (%s)", ('piet',))
#self.assertEqual(1, cur.rowcount)
#self.assertEqual(18446744073709551615, cur.lastrowid)
cur.close()
cnn.close()
def testEscapeArgs(self):
cnn = dbapi.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASSWD,
db=DB_DB)
cur = cnn.cursor()
cur.execute("truncate tbltest")
cur.execute(
"insert into tbltest (test_id, test_string) values (%s, %s)",
(1, 'piet'))
cur.execute(
"insert into tbltest (test_id, test_string) values (%s, %s)",
(2, 'klaas'))
cur.execute(
"insert into tbltest (test_id, test_string) values (%s, %s)",
(3, "pi'et"))
#classic sql injection, would return all rows if no proper escaping is done
cur.execute(
"select test_id, test_string from tbltest where test_string = %s",
("piet' OR 'a' = 'a", ))
self.assertEquals([], cur.fetchall()) #assert no rows are found
#but we should still be able to find the piet with the apostrophe in its name
cur.execute(
"select test_id, test_string from tbltest where test_string = %s",
("pi'et", ))
self.assertEquals([(3, "pi'et")], cur.fetchall())
#also we should be able to insert and retrieve blob/string with all possible bytes transparently
chars = ''.join([chr(i) for i in range(256)])
#print repr(chars)
cur.execute(
"insert into tbltest (test_id, test_string, test_blob) values (%s, %s, %s)",
(4, chars, chars))
cur.execute(
"select test_string, test_blob from tbltest where test_id = %s",
(4, ))
#self.assertEquals([(chars, chars)], cur.fetchall())
s, b = cur.fetchall()[0]
#test blob
self.assertEquals(256, len(b))
self.assertEquals(chars, b)
#test string
self.assertEquals(256, len(s))
self.assertEquals(chars, s)
cur.close()
cnn.close()
def testDeadlocks(self):
def process(cnn, cur, val):
try:
cur.execute("begin")
cur.execute("insert into tbltest (test_id) values (1)")
cur.execute("select sleep(2)")
cur.execute("update tbltest set test_id=%d" % val)
cur.execute("select sleep(2)")
cur.execute("commit")
return False
except dbapi.Error as e:
return "deadlock" in str(e).lower()
cnn1 = dbapi.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASSWD,
db=DB_DB)
cur1 = cnn1.cursor()
cnn2 = dbapi.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASSWD,
db=DB_DB)
cur2 = cnn2.cursor()
t1 = Tasklet.new(process)(cnn1, cur1, 2)
t2 = Tasklet.new(process)(cnn2, cur2, 3)
res = Tasklet.join_all([t1, t2])
self.assertTrue(
res[0] or res[1],
'At least one of the queries expected to fail due to deadlock (innodb must be used)'
)
# Both connections must survive after error
cur1.execute("select 1")
cur2.execute("select 2")
cur1.close()
cnn1.close()
cur2.close()
cnn2.close()
def testMySQLDBAPI(self):
cnn = dbapi.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASSWD,
db=DB_DB)
cur = cnn.cursor()
cur.execute("truncate tbltest")
for i in range(10):
cur.execute(
"insert into tbltest (test_id, test_string) values (%d, 'test%d')"
% (i, i))
cur.close()
cur = cnn.cursor()
cur.execute("select test_id, test_string from tbltest")
self.assertEquals((0, 'test0'), cur.fetchone())
#check that fetchall gets the remainder
self.assertEquals([(1, 'test1'), (2, 'test2'), (3, 'test3'),
(4, 'test4'), (5, 'test5'), (6, 'test6'),
(7, 'test7'), (8, 'test8'), (9, 'test9')],
cur.fetchall())
#another query on the same cursor should work
cur.execute("select test_id, test_string from tbltest")
#fetch some but not all
self.assertEquals((0, 'test0'), cur.fetchone())
self.assertEquals((1, 'test1'), cur.fetchone())
self.assertEquals((2, 'test2'), cur.fetchone())
#close whould work even with half read resultset
cur.close()
#this should not work, cursor was closed
try:
cur.execute("select * from tbltest")
self.fail("expected exception")
except dbapi.ProgrammingError:
pass
def testAutoInc(self):
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB)
cur = cnn.cursor()
cur.execute("truncate tblautoincint")
cur.execute("ALTER TABLE tblautoincint AUTO_INCREMENT = 100")
cur.execute("insert into tblautoincint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(100, cur.lastrowid)
cur.execute("insert into tblautoincint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(101, cur.lastrowid)
cur.execute("ALTER TABLE tblautoincint AUTO_INCREMENT = 4294967294")
cur.execute("insert into tblautoincint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(4294967294, cur.lastrowid)
cur.execute("insert into tblautoincint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(4294967295, cur.lastrowid)
cur.execute("truncate tblautoincbigint")
cur.execute("ALTER TABLE tblautoincbigint AUTO_INCREMENT = 100")
cur.execute("insert into tblautoincbigint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(100, cur.lastrowid)
cur.execute("insert into tblautoincbigint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(101, cur.lastrowid)
cur.execute("ALTER TABLE tblautoincbigint AUTO_INCREMENT = 18446744073709551614")
cur.execute("insert into tblautoincbigint (test_string) values (%s)", ('piet',))
self.assertEqual(1, cur.rowcount)
self.assertEqual(18446744073709551614, cur.lastrowid)
#this fails on mysql, but that is a mysql problem
#cur.execute("insert into tblautoincbigint (test_string) values (%s)", ('piet',))
#self.assertEqual(1, cur.rowcount)
#self.assertEqual(18446744073709551615, cur.lastrowid)
cur.close()
cnn.close()
def testEscapeArgs(self):
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB)
cur = cnn.cursor()
cur.execute("truncate tbltest")
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (1, 'piet'))
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (2, 'klaas'))
cur.execute("insert into tbltest (test_id, test_string) values (%s, %s)", (3, "pi'et"))
#classic sql injection, would return all rows if no proper escaping is done
cur.execute("select test_id, test_string from tbltest where test_string = %s", ("piet' OR 'a' = 'a",))
self.assertEquals([], cur.fetchall()) #assert no rows are found
#but we should still be able to find the piet with the apostrophe in its name
cur.execute("select test_id, test_string from tbltest where test_string = %s", ("pi'et",))
self.assertEquals([(3, "pi'et")], cur.fetchall())
#also we should be able to insert and retrieve blob/string with all possible bytes transparently
chars = ''.join([chr(i) for i in range(256)])
#print repr(chars)
cur.execute("insert into tbltest (test_id, test_string, test_blob) values (%s, %s, %s)", (4, chars, chars))
cur.execute("select test_string, test_blob from tbltest where test_id = %s", (4,))
#self.assertEquals([(chars, chars)], cur.fetchall())
s, b = cur.fetchall()[0]
#test blob
self.assertEquals(256, len(b))
self.assertEquals(chars, b)
#test string
self.assertEquals(256, len(s))
self.assertEquals(chars, s)
cur.close()
cnn.close()
def testMySQLDBAPI(self):
cnn = dbapi.connect(host = DB_HOST, user = DB_USER,
passwd = DB_PASSWD, db = DB_DB)
cur = cnn.cursor()
cur.execute("truncate tbltest")
for i in range(10):
cur.execute("insert into tbltest (test_id, test_string) values (%d, 'test%d')" % (i, i))
cur.close()
cur = cnn.cursor()
cur.execute("select test_id, test_string from tbltest")
self.assertEquals((0, 'test0'), cur.fetchone())
#check that fetchall gets the remainder
self.assertEquals([(1, 'test1'), (2, 'test2'), (3, 'test3'), (4, 'test4'), (5, 'test5'), (6, 'test6'), (7, 'test7'), (8, 'test8'), (9, 'test9')], cur.fetchall())
#another query on the same cursor should work
cur.execute("select test_id, test_string from tbltest")
#fetch some but not all
self.assertEquals((0, 'test0'), cur.fetchone())
self.assertEquals((1, 'test1'), cur.fetchone())
self.assertEquals((2, 'test2'), cur.fetchone())
#close whould work even with half read resultset
cur.close()
#this should not work, cursor was closed
try:
cur.execute("select * from tbltest")
self.fail("expected exception")
except dbapi.ProgrammingError:
pass
