def close_instance(self, connection: MySQLConnection,
cursor: MySQLConnection) -> None:
if connection is not None and connection.is_connected():
if cursor is not None:
cursor.close()
connection.close()
self._logger.info(
f"MySQL connection is closed. - PID: {os.getpid()}")
else:
self._logger.info(
"Connection has been disconnect or be killed before.")
class MysqlImport(SqlImport):
BATCH_SIZE = 1000
def __init__(self, host, port, user, password, db, log):
self.context = MySQLConnection(host=host,
port=port,
user=user,
password=password,
database=db)
self.cursor = self.context.cursor()
self.log = log
def run(self, parser, limit=0):
lines = []
keys = []
pbar = tqdm(total=parser.get_size())
count = 0
while limit == 0 or count <= limit:
line = parser.parse_line()
if line:
lines.append(tuple(line.values()))
keys = line.keys()
count += 1
if len(lines) >= self.BATCH_SIZE or not line:
try:
self.cursor.executemany(self.build_insert(parser, keys),
lines)
pbar.update(len(lines))
except IntegrityError as e:
self.log.error(str(e))
lines = []
if not line:
break
self.context.commit()
def run_script(self, filepath):
for line in open(filepath):
self.cursor.execute(line)
self.log.info('Ran script', filepath)
def truncate_table(self, table):
self.cursor.execute('TRUNCATE TABLE ' + table)
def close(self):
self.context.commit()
self.cursor.close()
self.context.close()
class MySQLConnector:
def __init__(self):
try:
self.connection = MySQLConnection(**config)
print("Connection created")
self.cursor = self.connection.cursor(dictionary=True)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
def query(self, query, args=None):
if args is None:
self.cursor.execute(query)
else:
self.cursor.execute(query, args)
def fetchone(self):
return self.cursor.fetchone()
def fetchall(self):
return self.cursor.fetchall()
def insert(self, query):
self.query(query)
self.connection.commit()
def update(self, query, args):
self.query(query, args)
self.connection.commit()
def delete(self, query):
self.query(query)
self.connection.commit()
def close(self):
self.cursor.close()
self.connection.close()
print("Connection closed")
class SqlConnector:
def __init__(self, schema_name):
self.schema_name = schema_name
self.cnx = None
self.error_log = []
def __enter__(self, schema_name):
try:
self.cnx = MySQLConnection(**config_local)
except:
self.cnx = MySQLConnection(**config_remote)
self.cnx.database = schema_name
print("Successfully opened MySQLConnection")
def __exit__(self):
if self.cnx is not None:
self.cnx.close()
print("Successfully closed MySQLConnection")
def execute_in_cursor(self, command):
new_cursor = self.cnx.cursor()
try:
new_cursor.execute(command)
result = list(new_cursor)
new_cursor.close()
print(f"Query results for ( {command} ): {result}")
return result
except Exception as e:
print(f"Query error occured: {e}")
self.error_log += [(command, e)]
finally:
new_cursor.close()
def print_error_log(self):
if not self.error_log: return print("No errors logged.\nDatabase update successful!")
print("Errors logged:")
for error in self.error_log:
print(error)
print("Database update unsuccessful!")
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (",".join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (",".join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
"tinyint_signed",
"tinyint_unsigned",
"bool_signed",
"smallint_signed",
"smallint_unsigned",
"mediumint_signed",
"mediumint_unsigned",
"int_signed",
"int_unsigned",
"bigint_signed",
"bigint_unsigned",
]
c.execute(
"""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
"""
% (self.tables["int"])
)
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
),
]
insert = self._get_insert_stmt(self.tables["int"], columns)
select = self._get_select_stmt(self.tables["int"], columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for i, col in enumerate(columns):
compare(col, data[0][i], rows[0][i])
compare(col, data[1][i], rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = ["c8", "c16", "c24", "c32", "c40", "c48", "c56", "c63", "c64"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
"""
% self.tables["bit"]
)
insert = self._get_insert_stmt(self.tables["bit"], columns)
select = self._get_select_stmt(self.tables["bit"], columns)
data = list()
data.append(tuple([0] * len(columns)))
values = list()
for col in columns:
values.append(1 << int(col.replace("c", "")) - 1)
data.append(tuple(values))
values = list()
for col in columns:
values.append((1 << int(col.replace("c", ""))) - 1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = ["float_signed", "float_unsigned", "double_signed", "double_unsigned"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)"""
% (self.tables["float"])
)
insert = self._get_insert_stmt(self.tables["float"], columns)
select = self._get_select_stmt(self.tables["float"], columns)
data = [
(-3.402823466, 0, -1.7976931348623157, 0),
(-1.175494351, 3.402823466, 1.7976931348623157, 2.2250738585072014),
(-1.23455678, 2.999999, -1.3999999999999999, 1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in range(0, len(data)):
for i, col in enumerate(columns[0:2]):
compare(col, round(data[j][i], 5), rows[j][i])
for i, col in enumerate(columns[2:2]):
compare(col, round(data[j][i], 10), rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = ["decimal_signed", "decimal_unsigned"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)"""
% (self.tables["decimal"])
)
insert = self._get_insert_stmt(self.tables["decimal"], columns)
select = self._get_select_stmt(self.tables["decimal"], columns)
data = [
(
Decimal("-9999999999999999999999999.999999999999999999999999999999"),
Decimal("+9999999999999999999999999.999999999999999999999999999999"),
),
(Decimal("-1234567.1234"), Decimal("+123456789012345.123456789012345678901")),
(
Decimal("-1234567890123456789012345.123456789012345678901234567890"),
Decimal("+1234567890123456789012345.123456789012345678901234567890"),
),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data, rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = ["t_date", "t_datetime", "t_time", "t_timestamp", "t_year_2", "t_year_4"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_2` YEAR(2),
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)"""
% (self.tables["temporal"])
)
insert = self._get_insert_stmt(self.tables["temporal"], columns)
select = self._get_select_stmt(self.tables["temporal"], columns)
data = [
(
datetime.date(2010, 1, 17),
datetime.datetime(2010, 1, 17, 19, 31, 12),
datetime.timedelta(hours=43, minutes=32, seconds=21),
datetime.datetime(2010, 1, 17, 19, 31, 12),
10,
0,
),
(
datetime.date(1000, 1, 1),
datetime.datetime(1000, 1, 1, 0, 0, 0),
datetime.timedelta(hours=-838, minutes=59, seconds=59),
datetime.datetime(*time.gmtime(1)[:6]),
70,
1901,
),
(
datetime.date(9999, 12, 31),
datetime.datetime(9999, 12, 31, 23, 59, 59),
datetime.timedelta(hours=838, minutes=59, seconds=59),
datetime.datetime(2038, 1, 19, 3, 14, 7),
69,
2155,
),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
from pprint import pprint
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in range(0, len(data)):
for i, col in enumerate(columns):
compare("%s (data[%d])" % (col, j), data[j][i], rows[j][i])
c.close()
class MySqlDB:
def __init__(self, settings):
self.settings = dict(**settings)
self.connection = None
def init_connection(self):
if self.connection is None:
self.connection = MySQLConnection(
host = self.settings.get('DB_HOST', '127.0.0.1'),
port = self.settings.get('DB_PORT', 3306),
user = self.settings.get('DB_USER', 'root'),
password = self.settings.get('DB_PASSWORD', ''),
database = self.settings.get('DB_NAME', 'mysql'),
)
def is_connected(self):
if self.connection is None:
self.init_connection()
return self.connection.is_connected()
def reconnect(self, attempts, delay):
if self.connection is None:
self.init_connection()
return self.connection.reconnect()
def rollback(self):
if self.connection is None:
self.init_connection()
self.connection.rollback()
def commit(self):
if self.connection is None:
self.init_connection()
self.connection.commit()
@contextlib.contextmanager
def cursor(self, buffered=True, rollback_on_error=True, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {}
kwargs.setdefault("dictionary", True)
kwargs.setdefault("buffered", buffered)
cursor = self.connection.cursor(**kwargs)
yield cursor
except MySqlError as error:
if rollback_on_error:
self.connection.rollback()
raise error from None
finally:
if cursor:
cursor.close()
def fetchone(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchone()
return result
finally:
if cursor:
cursor.close()
def fetchall(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchall()
return result
finally:
if cursor:
cursor.close()
def close(self):
if self.connection is not None:
self.connection.close()
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
'tinyint_signed',
'tinyint_unsigned',
'bool_signed',
'smallint_signed',
'smallint_unsigned',
'mediumint_signed',
'mediumint_unsigned',
'int_signed',
'int_unsigned',
'bigint_signed',
'bigint_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
""" % (self.tables['int']))
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
)
]
insert = self._get_insert_stmt(self.tables['int'], columns)
select = self._get_select_stmt(self.tables['int'], columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for i, col in enumerate(columns):
compare(col, data[0][i], rows[0][i])
compare(col, data[1][i], rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = [
'c8', 'c16', 'c24', 'c32', 'c40', 'c48', 'c56', 'c63', 'c64'
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
""" % self.tables['bit'])
insert = self._get_insert_stmt(self.tables['bit'], columns)
select = self._get_select_stmt(self.tables['bit'], columns)
data = list()
data.append(tuple([0] * len(columns)))
values = list()
for col in columns:
values.append(1 << int(col.replace('c', '')) - 1)
data.append(tuple(values))
values = list()
for col in columns:
values.append((1 << int(col.replace('c', ''))) - 1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = [
'float_signed',
'float_unsigned',
'double_signed',
'double_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['float']))
insert = self._get_insert_stmt(self.tables['float'], columns)
select = self._get_select_stmt(self.tables['float'], columns)
data = [
(
-3.402823466,
0,
-1.7976931348623157,
0,
),
(-1.175494351, 3.402823466, 1.7976931348623157,
2.2250738585072014),
(-1.23455678, 2.999999, -1.3999999999999999, 1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in (range(0, len(data))):
for i, col in enumerate(columns[0:2]):
compare(col, round(data[j][i], 5), rows[j][i])
for i, col in enumerate(columns[2:2]):
compare(col, round(data[j][i], 10), rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = [
'decimal_signed',
'decimal_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['decimal']))
insert = self._get_insert_stmt(self.tables['decimal'], columns)
select = self._get_select_stmt(self.tables['decimal'], columns)
data = [
(Decimal(
'-9999999999999999999999999.999999999999999999999999999999'),
Decimal(
'+9999999999999999999999999.999999999999999999999999999999')),
(Decimal('-1234567.1234'),
Decimal('+123456789012345.123456789012345678901')),
(Decimal(
'-1234567890123456789012345.123456789012345678901234567890'),
Decimal(
'+1234567890123456789012345.123456789012345678901234567890')),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data, rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = [
't_date',
't_datetime',
't_time',
't_timestamp',
't_year_4',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)""" % (self.tables['temporal']))
insert = self._get_insert_stmt(self.tables['temporal'], columns)
select = self._get_select_stmt(self.tables['temporal'], columns)
data = [
(datetime.date(2010, 1,
17), datetime.datetime(2010, 1, 17, 19, 31, 12),
datetime.timedelta(hours=43, minutes=32, seconds=21),
datetime.datetime(2010, 1, 17, 19, 31, 12), 0),
(datetime.date(1000, 1, 1), datetime.datetime(1000, 1, 1, 0, 0, 0),
datetime.timedelta(hours=-838, minutes=59, seconds=59),
datetime.datetime(*time.gmtime(1)[:6]), 1901),
(datetime.date(9999, 12,
31), datetime.datetime(9999, 12, 31, 23, 59, 59),
datetime.timedelta(hours=838, minutes=59, seconds=59),
datetime.datetime(2038, 1, 19, 3, 14, 7), 2155),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in (range(0, len(data))):
for i, col in enumerate(columns):
compare("%s (data[%d])" % (col, j), data[j][i], rows[j][i])
# Testing YEAR(2), which is now obsolete since MySQL 5.6.6
tblname = self.tables['temporal_year']
c.execute("CREATE TABLE %s ("
"`id` int NOT NULL AUTO_INCREMENT KEY, "
"`t_year_2` YEAR(2))" % tblname)
c.execute(self._get_insert_stmt(tblname, ['t_year_2']), (10, ))
c.execute(self._get_select_stmt(tblname, ['t_year_2']))
row = c.fetchone()
if tests.MYSQL_VERSION >= (5, 6, 6):
self.assertEqual(2010, row[0])
else:
self.assertEqual(10, row[0])
c.close()
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
'tinyint_signed',
'tinyint_unsigned',
'bool_signed',
'smallint_signed',
'smallint_unsigned',
'mediumint_signed',
'mediumint_unsigned',
'int_signed',
'int_unsigned',
'bigint_signed',
'bigint_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
""" % (self.tables['int']))
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
)
]
insert = self._get_insert_stmt(self.tables['int'],columns)
select = self._get_select_stmt(self.tables['int'],columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for i,col in enumerate(columns):
compare(col,data[0][i],rows[0][i])
compare(col,data[1][i],rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = [
'c8','c16','c24','c32',
'c40','c48','c56','c63',
'c64']
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
""" % self.tables['bit'])
insert = self._get_insert_stmt(self.tables['bit'],columns)
select = self._get_select_stmt(self.tables['bit'],columns)
data = list()
data.append(tuple([0]*len(columns)))
values = list()
for col in columns:
values.append( 1 << int(col.replace('c',''))-1)
data.append(tuple(values))
values = list()
for col in columns:
values.append( (1 << int(col.replace('c',''))) -1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = [
'float_signed',
'float_unsigned',
'double_signed',
'double_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['float']))
insert = self._get_insert_stmt(self.tables['float'],columns)
select = self._get_select_stmt(self.tables['float'],columns)
data = [
(-3.402823466,0,-1.7976931348623157,0,),
(-1.175494351,3.402823466,1.7976931348623157,2.2250738585072014),
(-1.23455678,2.999999,-1.3999999999999999,1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for j in (range(0,len(data))):
for i,col in enumerate(columns[0:2]):
compare(col,round(data[j][i],5),rows[j][i])
for i,col in enumerate(columns[2:2]):
compare(col,round(data[j][i],10),rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = [
'decimal_signed',
'decimal_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['decimal']))
insert = self._get_insert_stmt(self.tables['decimal'],columns)
select = self._get_select_stmt(self.tables['decimal'],columns)
data = [
(Decimal('-9999999999999999999999999.999999999999999999999999999999'),
Decimal('+9999999999999999999999999.999999999999999999999999999999')),
(Decimal('-1234567.1234'),
Decimal('+123456789012345.123456789012345678901')),
(Decimal('-1234567890123456789012345.123456789012345678901234567890'),
Decimal('+1234567890123456789012345.123456789012345678901234567890')),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data,rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = [
't_date',
't_datetime',
't_time',
't_timestamp',
't_year_4',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)""" % (self.tables['temporal']))
insert = self._get_insert_stmt(self.tables['temporal'],columns)
select = self._get_select_stmt(self.tables['temporal'],columns)
data = [
(datetime.date(2010,1,17),
datetime.datetime(2010,1,17,19,31,12),
datetime.timedelta(hours=43,minutes=32,seconds=21),
datetime.datetime(2010,1,17,19,31,12),
0),
(datetime.date(1000,1,1),
datetime.datetime(1000,1,1,0,0,0),
datetime.timedelta(hours=-838,minutes=59,seconds=59),
datetime.datetime(*time.gmtime(1)[:6]),
1901),
(datetime.date(9999,12,31),
datetime.datetime(9999,12,31,23,59,59),
datetime.timedelta(hours=838,minutes=59,seconds=59),
datetime.datetime(2038,1,19,3,14,7),
2155),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for j in (range(0,len(data))):
for i,col in enumerate(columns):
compare("%s (data[%d])" % (col,j),data[j][i],rows[j][i])
# Testing YEAR(2), which is now obsolete since MySQL 5.6.6
tblname = self.tables['temporal_year']
c.execute("CREATE TABLE %s ("
"`id` int NOT NULL AUTO_INCREMENT KEY, "
"`t_year_2` YEAR(2))" % tblname)
c.execute(self._get_insert_stmt(tblname, ['t_year_2']), (10,))
c.execute(self._get_select_stmt(tblname, ['t_year_2']))
row = c.fetchone()
if tests.MYSQL_VERSION >= (5, 6, 6):
self.assertEqual(2010, row[0])
else:
self.assertEqual(10, row[0])
c.close()
class DBDriver:
config_path = 'lib/database.inc'
def __init__(self):
self.__connection = None
self.dirs = None
self.connect()
self.get_dirs()
def __enter__(self):
return self
def read_config(self):
"""
Reads lib/database.inc and extracts the database connection information
This assumes the strings in that file are double-quoted
but won't be bothered if there are special characters in the password or weird whitespace things.
:return: a dictionary containing the connection information
"""
config_dict = {"db_host": None,
"db_user": None,
"db_pass": None,
"db_name": None}
with open(self.config_path, 'r') as config_file:
for line in config_file:
for key in config_dict:
if key in line:
value = line.split('"')[1]
config_dict[key] = value
return config_dict
def connect(self):
"""
Connects to the db using read_db_info()
:return: a connection to the db. Remember to close it
"""
connection_info = self.read_config()
self.__connection = MySQLConnection(user=connection_info["db_user"],
password=connection_info["db_pass"],
host=connection_info["db_host"],
database=connection_info["db_name"])
def get_dirs(self):
"""
Fetches and generates paths to the base, queue, judged, and data directories
"""
curs = self.__connection.cursor() # would use a with block but it's not supported :(
curs.execute('''SELECT BASE_DIRECTORY FROM CONTEST_CONFIG''')
base_dir = curs.fetchone()[0]
self.dirs = {'base': base_dir,
'queue': os.path.join(base_dir, QUEUE_DIR_NAME),
'judged': os.path.join(base_dir, JUDGED_DIR_NAME),
'data': os.path.join(base_dir, DATA_DIR_NAME)}
return self.dirs
curs.close()
def get_forbidden(self, lang_id):
"""
Gets the forbidden words for the language with the given ID
"""
curs = self.__connection.cursor()
curs.execute('''SELECT WORD FROM FORBIDDEN_WORDS WHERE LANGUAGE_ID=%d''' % lang_id)
out = [tupl[0] for tupl in curs.fetchall()]
curs.close()
return out
def get_headers(self, lang_id):
"""
Gets the headers for the language with the given ID
"""
curs = self.__connection.cursor()
curs.execute('''SELECT HEADER FROM HEADERS WHERE LANGUAGE_ID=%d''' % lang_id)
out = [tupl[0] for tupl in curs.fetchall()]
curs.close()
return out
def get_language_info(self, language_name):
"""
:return: A row of the LANGUAGE table specified by language_name
"""
curs = self.__connection.cursor()
curs.execute('''SELECT * FROM LANGUAGE WHERE LANGUAGE_NAME="%s"''' % language_name)
out = curs.fetchone()
curs.close()
return out
def get_queued_submissions(self, queue_id=None, test_compile=False):
"""
:return: Information on all the queued submissions submitted before the contest ends
:param queue_id If specified, only fetch submissions with that ID
:param test_compile True if test compile submissions should only be fetched and false if they should be ignored
"""
curs = self.__connection.cursor()
where_clause = '''WHERE TS < (START_TS + CONTEST_END_DELAY) AND TEST_COMPILE = %s''' % test_compile
if queue_id is not None:
where_clause += ''' AND QUEUE_ID = %d''' % queue_id
curs.execute('''SELECT QUEUE_ID, TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME, TEST_COMPILE
FROM QUEUED_SUBMISSIONS, CONTEST_CONFIG
%s
ORDER BY TS''' % where_clause)
out = curs.fetchall()
curs.close()
return out
def enqueue_submission(self, team_id: int, problem_id: int, timestamp: int, attempt: int, source_name: str):
"""
Inserts a submission into the QUEUED_SUBMISSIONS table
:param team_id: The ID of the team in the database
:param problem_id: the ID of the problem this submission is for
:param timestamp: the timestamp of the submission
:param attempt: how many times has this team attempted to solve this problem?
:param source_name: the name of the source file (not a full path)
"""
curs = self.__connection.cursor()
curs.execute('''INSERT INTO QUEUED_SUBMISSIONS (TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME)
VALUES (%d, %d, %d, %d, '%s')''' % (team_id, problem_id, timestamp, attempt, source_name))
self.__connection.commit()
curs.close()
def empty_judged(self):
"""
Clears the JUDGED_SUBMISSIONS and AUTO_RESPONSES tables
:return: the contents of the JUDGED_SUBMISSIONS table
"""
curs = self.__connection.cursor()
curs.execute('''SELECT TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME FROM JUDGED_SUBMISSIONS''')
out = curs.fetchall()
curs.execute('''TRUNCATE JUDGED_SUBMISSIONS''')
curs.execute('''TRUNCATE AUTO_RESPONSES''')
self.__connection.commit()
curs.close()
return out
def get_ignore_stderr(self):
"""
:return boolean: Whether IGNORE_STDERR is set
"""
curs = self.__connection.cursor()
curs.execute('''SELECT IGNORE_STDERR FROM CONTEST_CONFIG''')
out = curs.fetchone()[0]
curs.close()
return bool(out)
def report_pending(self, one_submission_info):
"""
Reports that the given submission is being judged by moving the row from QUEUED_SUBMISSIONS to JUDGED_SUBMISSIONS
:param one_submission_info: A row from the QUEUED_SUBMISSIONS table
:return: the id of the inserted row
"""
curs = self.__connection.cursor()
one_submission_info = list(one_submission_info) # to make it mutable
for idx, elem in enumerate(one_submission_info):
if elem is None:
one_submission_info[idx] = 'NULL' # SQL syntax
curs.execute('''INSERT INTO JUDGED_SUBMISSIONS (TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME, TEST_COMPILE)
VALUES (%s, %s, %s, %s, '%s', %s)''' % tuple(one_submission_info[1:]))
row_id = curs.lastrowid
curs.execute('''DELETE FROM QUEUED_SUBMISSIONS WHERE QUEUE_ID = %d''' % one_submission_info[0])
self.__connection.commit()
curs.close()
return row_id
def report_auto_judgement(self, judged_id, judgement_code, input_file, output_file, error_no=None):
"""
Inserts a row into the AUTO_JUDGMENT table
:param judged_id: returned by report_pending
:param judgement_code: a code that indicates which judgement was made
:param input_file: The name of the file which served as input for this run of the program (may be None if error)
:param output_file: The name of the file which was outputted by the program. May contain error text.
:param error_no: the error number if applicable
"""
curs = self.__connection.cursor()
columns = ['JUDGED_ID', 'OUTPUT_FILE', 'RESPONSE_ID']
values = [judged_id, output_file, judgement_code]
formats = ['%d', "'%s'", '%d']
if input_file is not None:
columns.append('INPUT_FILE')
values.append(input_file)
formats.append("'%s'")
if error_no is not None:
columns.append('ERROR_NO')
values.append(error_no)
formats.append('%d')
columns = '(%s)' % ', '.join(columns)
formats = '(%s)' % ', '.join(formats)
value_clause = formats % tuple(values)
curs.execute('INSERT INTO AUTO_RESPONSES ' + columns + ' VALUES ' + value_clause)
self.__connection.commit()
curs.close()
def make_judgement(self, judged_id):
"""
Acts like a human judgement and makes the reccomended judgement on the given ID
:param judged_id: an ID from the JUDGED_SUBMISSIONS table
"""
curs = self.__connection.cursor()
curs.execute('''SELECT RESPONSE_ID FROM AUTO_RESPONSES WHERE JUDGED_ID = %d''' % judged_id)
judgement_codes = [row[0] for row in curs.fetchall()]
final_code = min(judgement_codes)
curs.execute('''UPDATE JUDGED_SUBMISSIONS SET RESPONSE_ID=%d, JUDGED=1 WHERE JUDGED_ID=%d''' %
(final_code, judged_id))
self.__connection.commit()
curs.close()
def get_response_codes(self):
"""
:return: A dictionary which associates response keywords with their IDs
"""
curs = self.__connection.cursor()
curs.execute('''SELECT RESPONSE_ID, KEYWORD FROM RESPONSES''')
out = {tupl[1]: tupl[0] for tupl in curs.fetchall()}
curs.close()
return out
def __exit__(self, exc_type, exc_val, exc_tb):
self.__del__()
def __del__(self):
self.__connection.close()
