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