def testDoubleConnect(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DB)
time.sleep(11)
cnn.close()
time.sleep(1)
cnn = umysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DB)
def testMySQLDBAPI(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tbltest")
for i in range(10):
cnn.query(
"insert into tbltest (test_id, test_string) values (%s, 'test%s')",
(
i,
i,
))
rs = cnn.query("select test_id, test_string from tbltest")
self.assertEqual((0, 'test0'), rs.rows[0])
#check that fetchall gets the remainder
self.assertEqual([(1, 'test1'), (2, 'test2'), (3, 'test3'),
(4, 'test4'), (5, 'test5'), (6, 'test6'),
(7, 'test7'), (8, 'test8'), (9, 'test9')],
rs.rows[1:])
#another query on the same cursor should work
rs = cnn.query("select test_id, test_string from tbltest")
#fetch some but not all
self.assertEqual((0, 'test0'), rs.rows[0])
self.assertEqual((1, 'test1'), rs.rows[1])
self.assertEqual((2, 'test2'), rs.rows[2])
#this should not work, cursor was closed
cnn.close()
def testSelectUnicode(self):
s = 'r\xc3\xa4ksm\xc3\xb6rg\xc3\xa5s'
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tbltest")
cnn.query("insert into tbltest (test_id, test_string) values (%s, %s)",
(1, 'piet'))
cnn.query("insert into tbltest (test_id, test_string) values (%s, %s)",
(2, s))
cnn.query("insert into tbltest (test_id, test_string) values (%s, %s)",
(3, s))
rs = cnn.query("select test_id, test_string from tbltest")
result = rs.rows
self.assertEqual([(1, 'piet'), (2, s), (3, s)], 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)])
cnn.query("insert into tbltest (test_id, test_blob) values (%s, %s)",
(4, blob))
rs = cnn.query("select test_blob from tbltest where test_id = %s",
(4, ))
b2 = rs.rows[0][0]
self.assertEqual(str, type(b2))
self.assertEqual(256, len(b2))
self.assertEqual(blob, b2)
cnn.close()
def testMySQLClient(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
rs = cnn.query("select 1")
self.assertEqual([(1, )], rs.rows)
cnn.close()
def testDateTime(self):
# Tests the behaviour of insert/select with mysql/DATETIME <-> python/datetime.datetime
d_date = datetime.datetime(2010, 2, 11, 13, 37, 42)
d_string = "2010-02-11 13:37:42"
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tbldate")
cnn.query(
"create table tbldate (test_id int(11) DEFAULT NULL, test_date datetime DEFAULT NULL, test_date2 datetime DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1"
)
cnn.query(
"insert into tbldate (test_id, test_date, test_date2) values (%s, %s, %s)",
(1, d_string, d_date))
# Make sure our insert was correct
rs = cnn.query(
"select test_id from tbldate where test_date = test_date2")
result = rs.rows
self.assertEqual([(1, )], result)
# Make sure select gets the right value back
rs = cnn.query(
"select test_id, test_date, test_date2 from tbldate where test_date = test_date2"
)
result = rs.rows
self.assertEqual([(1, d_date, d_date)], result)
cnn.close()
def testUnicodeUTF8(self):
peacesign_unicode = '\u262e'
peacesign_utf8 = b'\xe2\x98\xae'
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tblutf")
cnn.query(
"create table tblutf (test_id int(11) DEFAULT NULL, test_string VARCHAR(32) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8"
)
cnn.query("insert into tblutf (test_id, test_string) values (%s, %s)",
(
1,
peacesign_unicode,
)) # This should be encoded in utf8
cnn.query("insert into tblutf (test_id, test_string) values (%s, %s)",
(
2,
peacesign_utf8,
))
rs = cnn.query("select test_id, test_string from tblutf")
result = rs.rows
# We expect unicode strings back
self.assertEqual([(1, peacesign_unicode), (2, peacesign_unicode)],
result)
cnn.close()
def testUtf8mb4(self):
utf8mb4chr = '\U0001f603'
# We expected we can insert utf8mb4 character, than fetch it back
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tblutf8mb4")
cnn.query(
"create table tblutf8mb4 (test_text TEXT DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4"
)
cnn.query("insert into tblutf8mb4 (test_text) values ('" + utf8mb4chr +
"')")
cnn.query("set names utf8mb4")
cnn.query("insert into tblutf8mb4 (test_text) values ('" + utf8mb4chr +
"')")
rs = cnn.query("select test_text from tblutf8mb4;")
result = rs.rows
self.assertNotEqual(result[0][0], utf8mb4chr)
self.assertEqual(result[1][0], utf8mb4chr)
cnn.query("set names utf8")
rs = cnn.query("select test_text from tblutf8mb4;")
result = rs.rows
self.assertNotEqual(result[1][0], utf8mb4chr)
cnn.close()
def testConnectWithWrongDB(self):
cnn = umysql.Connection()
try:
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, "DBNOTFOUND")
except umysql.SQLError as e:
# 1049 = ER_BAD_DB_ERROR
self.assertEqual(e.args[0], 1049)
def testLargePackets(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tbltest")
c = 16 * 1024
blob = '0123456789'
while 1:
cnn.query(
"insert into tbltest (test_id, test_blob) values (%s, %s)", (
len(blob),
blob,
))
if len(blob) > (c * 2): break
blob = blob * 2
rs = cnn.query("select test_id, test_blob from tbltest")
for row in rs.rows:
self.assertEqual(row[0], len(row[1]))
#self.assertEqual(blob[:row[0]], row[1])
#reread, second time, oversize packet is already present
rs = cnn.query("select test_id, test_blob from tbltest")
for row in rs.rows:
self.assertEqual(row[0], len(row[1]))
self.assertEqual(blob[:row[0]], row[1])
cnn.close()
def testMySQLClient2(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tbltest")
for i in range(10):
self.assertEqual(
(1, 0),
cnn.query(
"insert into tbltest (test_id, test_string) values (%s, 'test%s')",
(
i,
i,
)))
rs = cnn.query("select test_id, test_string from tbltest")
#trying to close it now would give an error, e.g. we always need to read
#the result from the database otherwise connection would be in wrong stat
for i, row in enumerate(rs.rows):
self.assertEqual((i, 'test%s' % i), row)
cnn.close()
def testConnectAutoCommitOff(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, "", False)
rs = cnn.query("select 1")
self.assertEqual([(1, )], rs.rows)
cnn.close()
def testConnectDNSFails(self):
cnn = umysql.Connection()
try:
cnn.connect("thisplaceisnowere", 31337, DB_USER, DB_PASSWD, DB_DB)
assert False, "Expected exception"
except (socket.error):
pass
cnn.close()
def testConnectFails(self):
cnn = umysql.Connection()
try:
cnn.connect(DB_HOST, 31337, DB_USER, DB_PASSWD, DB_DB)
assert False, "Expected exception"
except (socket.error):
pass
cnn.close()
def testConnectTwice(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
try:
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
assert False, "Expected exception"
except:
pass
pass
cnn.close()
def testAutoInc(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tblautoincint")
cnn.query("ALTER TABLE tblautoincint AUTO_INCREMENT = 100")
rowcount, lastrowid = cnn.query(
"insert into tblautoincint (test_string) values (%s)", ('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(100, lastrowid)
rowcount, lastrowid = cnn.query(
"insert into tblautoincint (test_string) values (%s)", ('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(101, lastrowid)
cnn.query("ALTER TABLE tblautoincint AUTO_INCREMENT = 4294967294")
rowcount, lastrowid = cnn.query(
"insert into tblautoincint (test_string) values (%s)", ('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(4294967294, lastrowid)
rowcount, lastrowid = cnn.query(
"insert into tblautoincint (test_string) values (%s)", ('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(4294967295, lastrowid)
cnn.query("truncate tblautoincbigint")
cnn.query("ALTER TABLE tblautoincbigint AUTO_INCREMENT = 100")
rowcount, lastrowid = cnn.query(
"insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(100, lastrowid)
rowcount, lastrowid = cnn.query(
"insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(101, lastrowid)
cnn.query(
"ALTER TABLE tblautoincbigint AUTO_INCREMENT = 18446744073709551614"
)
rowcount, lastrowid = cnn.query(
"insert into tblautoincbigint (test_string) values (%s)",
('piet', ))
self.assertEqual(1, rowcount)
self.assertEqual(18446744073709551614, 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)
cnn.close()
def testConnectCloseQuery(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
self.assertEqual(True, cnn.is_connected())
cnn.close()
self.assertEqual(False, cnn.is_connected())
try:
cnn.query("SELECT 1")
assert False, "Expected exception"
except (RuntimeError):
pass
cnn.close()
def testUnsignedInt(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DB)
cnn.query('DROP TABLE IF EXISTS tblunsignedint')
cnn.query('''CREATE TABLE tblunsignedint(
`big` BIGINT UNSIGNED NOT NULL,
`int` INT UNSIGNED NOT NULL,
`medium` MEDIUMINT UNSIGNED NOT NULL,
`short` SMALLINT UNSIGNED NOT NULL,
`tiny` TINYINT UNSIGNED NOT NULL
)''')
values1 = (
0xffffffffffffffff,
0xffffffff,
0xffffff,
0xffff,
0xff,
)
values2 = (
0x8000000000000000,
0x80000000,
0x800000,
0x8000,
0x80,
)
values3 = (
0x8fedcba098765432,
0x8fedcba0,
0x8fedcb,
0x8fed,
0x8f,
)
rc, rid = cnn.query(
'INSERT INTO `tblunsignedint` VALUES(%s, %s, %s, %s, %s)', values1)
self.assertEqual(rc, 1)
rc, rid = cnn.query(
'INSERT INTO `tblunsignedint` VALUES(%s, %s, %s, %s, %s)', values2)
self.assertEqual(rc, 1)
rc, rid = cnn.query(
'INSERT INTO `tblunsignedint` VALUES(%s, %s, %s, %s, %s)', values3)
self.assertEqual(rc, 1)
rs = cnn.query('SELECT * FROM `tblunsignedint`')
self.assertEqual([
values1,
values2,
values3,
], rs.rows)
cnn.close()
def testEscapeArgs(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("truncate tbltest")
cnn.query("insert into tbltest (test_id, test_string) values (%s, %s)",
(1, 'piet'))
cnn.query("insert into tbltest (test_id, test_string) values (%s, %s)",
(2, 'klaas'))
cnn.query("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
rs = cnn.query(
"select test_id, test_string from tbltest where test_string = %s",
("piet' OR 'a' = 'a", ))
self.assertEqual([], rs.rows) #assert no rows are found
#but we should still be able to find the piet with the apostrophe in its name
rs = cnn.query(
"select test_id, test_string from tbltest where test_string = %s",
("pi'et", ))
self.assertEqual([(3, "pi'et")], rs.rows)
#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)])
cnn.query(
"insert into tbltest (test_id, test_string, test_blob) values (%s, %s, %s)",
(4, chars[:80], chars))
#cnn.query("insert into tbltest (test_id, test_blob) values (%s, %s)", (4, chars))
rs = cnn.query(
"select test_blob, test_string from tbltest where test_id = %s",
(4, ))
#self.assertEqual([(chars, chars)], cur.fetchall())
b, s = rs.rows[0]
#test blob
self.assertEqual(256, len(b))
self.assertEqual(chars, b)
self.assertEqual(80, len(s))
self.assertEqual(chars[:80], s)
cnn.close()
def testTimestamp(self):
ts = datetime.datetime(2013, 4, 26, 9, 53, 48)
ts_val = ts.strftime("%Y-%m-%d %H:%M:%S")
drop = 'drop table if exists tbltimestamp'
create = 'create table tbltimestamp (idx int not null, ts timestamp)'
insert = 'insert into tbltimestamp (idx, ts) values (%s, %s)'
select = 'select idx, ts from tbltimestamp where idx = %s'
idx = 1
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query(drop)
cnn.query(create)
cnn.query(insert, (
idx,
ts_val,
))
result = cnn.query(select, (idx, ))
self.assertEqual(result.rows[0], (idx, ts))
def testPercentEscaping(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
rs = cnn.query(
"SELECT * FROM `tblautoincint` WHERE `test_id` LIKE '%%10%%'")
self.assertEqual([(100, 'piet'), (101, 'piet')], rs.rows)
rs = cnn.query(
"SELECT * FROM `tblautoincint` WHERE `test_id` LIKE '%%%s%%'",
[10])
self.assertEqual([(100, 'piet'), (101, 'piet')], rs.rows)
# SqlAlchemy query style
rs = cnn.query(
"SELECT * FROM `tblautoincint` WHERE `test_id` LIKE concat(concat('%%', %s), '%%')",
[10])
self.assertEqual([(100, 'piet'), (101, 'piet')], rs.rows)
cnn.close()
def testUnique(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tblunique")
cnn.query("create table tblunique (name VARCHAR(32) UNIQUE)")
# We insert the same character using two different encodings
cnn.query("insert into tblunique set name=\"kaka\"")
count = 0
try:
cnn.query("insert into tblunique set name=\"kaka\"")
self.fail('expected timeout')
except umysql.SQLError as e:
pass
cnn.query("select * from tblunique")
cnn.close()
def testConnectTimeout(self):
cnn = umysql.Connection()
cnn.settimeout(1)
start = time.clock()
try:
cnn.connect(DB_HOST, 31481, DB_USER, DB_PASSWD, DB_DB)
assert False, "Expected exception"
except (socket.error):
elapsed = time.clock() - start
if (elapsed > 2):
assert False, "Timeout isn't working"
try:
res = cnn.query("select * from kaka")
assert False, "Expected exception"
except (socket.error):
pass
cnn.close()
def testBigInt(self):
#Tests the behaviour of insert/select with bigint/long.
BIGNUM = 112233445566778899
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tblbigint")
cnn.query(
"create table tblbigint (test_id int(11) DEFAULT NULL, test_bigint bigint DEFAULT NULL, test_bigint2 bigint DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1"
)
cnn.query(
"insert into tblbigint (test_id, test_bigint, test_bigint2) values (%s, "
+ str(BIGNUM) + ", %s)", (
1,
BIGNUM,
))
cnn.query(
"insert into tblbigint (test_id, test_bigint, test_bigint2) values (%s, "
+ str(BIGNUM) + ", %s)", (
2,
BIGNUM,
))
# Make sure both our inserts where correct (ie, the big number was not truncated/modified on insert)
rs = cnn.query(
"select test_id from tblbigint where test_bigint = test_bigint2")
result = rs.rows
self.assertEqual([(1, ), (2, )], result)
# Make sure select gets the right values (ie, the big number was not truncated/modified when retrieved)
rs = cnn.query(
"select test_id, test_bigint, test_bigint2 from tblbigint where test_bigint = test_bigint2"
)
result = rs.rows
self.assertEqual([(1, BIGNUM, BIGNUM), (2, BIGNUM, BIGNUM)], result)
cnn.close()
def testBlob(self):
peacesign_binary = b"\xe2\x98\xae"
peacesign_binary2 = b"\xe2\x98\xae" * 1024
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tblblob")
cnn.query(
"create table tblblob (test_id int(11) DEFAULT NULL, test_blob BLOB DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8"
)
cnn.query("insert into tblblob (test_id, test_blob) values (%s, %s)",
(1, peacesign_binary))
cnn.query("insert into tblblob (test_id, test_blob) values (%s, %s)",
(2, peacesign_binary2))
rs = cnn.query("select test_id, test_blob from tblblob")
result = rs.rows
# We expect binary strings back
self.assertEqual([(1, peacesign_binary.decode('utf-8')),
(2, peacesign_binary2.decode('utf-8'))], result)
cnn.close()
def testZeroDates(self):
# Tests the behaviour of zero dates
zero_datetime = "0000-00-00 00:00:00"
zero_date = "0000-00-00"
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
cnn.query("drop table if exists tbldate")
cnn.query(
"create table tbldate (test_id int(11) DEFAULT NULL, test_date date DEFAULT NULL, test_datetime datetime DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1"
)
cnn.query(
"insert into tbldate (test_id, test_date, test_datetime) values (%s, %s, %s)",
(1, zero_date, zero_datetime))
# Make sure we get None-values back
rs = cnn.query(
"select test_id, test_date, test_datetime from tbldate where test_id = 1"
)
result = rs.rows
self.assertEqual([(1, None, None)], result)
cnn.close()
def testCorruption(self):
try:
a.b = umysql.Connection()
except NameError:
pass
def testConnectWrongCredentials(self):
cnn = umysql.Connection()
try:
cnn.connect(DB_HOST, 3306, "UserNotFound", "PasswordYeah", DB_DB)
except umysql.SQLError as e:
self.assertEqual(e.args[0], 1045)
def testConnectClosed(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, DB_DB)
self.assertEqual(True, cnn.is_connected())
cnn.close()
self.assertEqual(False, cnn.is_connected())
def testConnectWithNoDB(self):
cnn = umysql.Connection()
cnn.connect(DB_HOST, 3306, DB_USER, DB_PASSWD, "")
