def test_run_mysql_executemany_query_execute_error(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.executemany = MagicMock(side_effect=Exception("Boom!"))
cursor.close = MagicMock()
with pytest.raises(Exception):
run_mysql_executemany_query(
mysql_conn=conn,
sql_query=SQL_MLWH_MULTIPLE_INSERT,
values=["test"] # type: ignore
)
# check transaction is not committed
assert conn.commit.called is False
# check connection is closed
assert cursor.close.called is True
assert conn.close.called is True
def test_run_mysql_execute_formatted_query_execute_error(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.execute = MagicMock(side_effect=Exception("Boom!"))
cursor.close = MagicMock()
with pytest.raises(Exception):
run_mysql_execute_formatted_query(
mysql_conn=conn,
formatted_sql_query=
SQL_MLWH_MULTIPLE_FILTERED_POSITIVE_UPDATE_BATCH,
formatting_args=["1", "2"],
query_args=[True, "v2", "2020-01-01", "2020-01-01"],
)
# check transaction is not committed
assert conn.commit.called is False
# check connection is closed
assert cursor.close.called is True
def run_mysql_executemany_query(mysql_conn: CMySQLConnection, sql_query: str, values: List[Dict[str, str]]) -> None:
"""Writes the sample testing information into the MLWH.
Arguments:
mysql_conn {CMySQLConnection} -- a client used to interact with the database server
sql_query {str} -- the SQL query to run (see sql_queries.py)
values {List[Dict[str, str]]} -- array of value hashes representing documents inserted into
the Mongo DB
"""
# fetch the cursor from the DB connection
cursor: CMySQLCursor = mysql_conn.cursor()
try:
# executing the query with values
num_values = len(values)
# BN. If ROWS_PER_QUERY value is too high, you may get '2006 (HY000): MySQL server has
# gone away' error indicating you've exceeded the max_allowed_packet size for MySQL
ROWS_PER_QUERY = 15000
values_index = 0
total_rows_affected = 0
logger.debug(
f"Attempting to insert or update {num_values} rows in the MLWH database in batches of {ROWS_PER_QUERY}"
)
while values_index < num_values:
logger.debug(f"Inserting records between {values_index} and {values_index + ROWS_PER_QUERY}")
cursor.executemany(sql_query, values[values_index : (values_index + ROWS_PER_QUERY)]) # noqa: E203
logger.debug(
f"{cursor.rowcount} rows affected in MLWH. (Note: each updated row increases the "
"count by 2, instead of 1)"
)
total_rows_affected += cursor.rowcount
values_index += ROWS_PER_QUERY
logger.debug("Committing changes to MLWH database.")
mysql_conn.commit()
# number of rows affected using cursor.rowcount - not easy to interpret:
# reports 1 per inserted row,
# 2 per updated existing row,
# and 0 per unchanged existing row
logger.info(
f"A total of {total_rows_affected} rows were affected in MLWH. (Note: each updated row "
"increases the count by 2, instead of 1)"
)
except Exception:
logger.error("MLWH database executemany transaction failed")
raise
finally:
# close the cursor
logger.debug("Closing the cursor.")
cursor.close()
# close the connection
logger.debug("Closing the MLWH database connection.")
mysql_conn.close()
def test_run_mysql_executemany_query_success(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.executemany = MagicMock()
cursor.close = MagicMock()
run_mysql_executemany_query(mysql_conn=conn,
sql_query=SQL_MLWH_MULTIPLE_INSERT,
values=[{}])
# check transaction is committed
assert conn.commit.called is True
# check connection is closed
assert cursor.close.called is True
assert conn.close.called is True
def test_run_mysql_execute_formatted_query_success(config):
conn = CMySQLConnection()
conn.cursor = MagicMock()
conn.commit = MagicMock()
conn.rollback = MagicMock()
conn.close = MagicMock()
cursor = conn.cursor.return_value
cursor.execute = MagicMock()
cursor.close = MagicMock()
run_mysql_execute_formatted_query(
mysql_conn=conn,
formatted_sql_query=SQL_MLWH_MULTIPLE_FILTERED_POSITIVE_UPDATE_BATCH,
formatting_args=["1", "2"],
query_args=[True, "v2", "2020-01-01", "2020-01-01"],
)
# check transaction is committed
assert conn.commit.called is True
# check connection is closed
assert cursor.close.called is True
def cursor(conn: CMySQLConnection):
cur = conn.cursor()
try:
yield cur
finally:
cur.close()
def run_mysql_execute_formatted_query(
mysql_conn: CMySQLConnection, formatted_sql_query: str, formatting_args: List[str], query_args: List[Any]
) -> None:
"""Executes formatted sql query, unwrapping and batching based on number of input arguments
Arguments:
mysql_conn {CMySQLConnection} -- a client used to interact with the database server
formatted_sql_query {str} -- the formatted SQL query to run (unwrapped using % workflow)
formatting_args {List[str]} -- arguments to batch and unwrap the formatted sql query
query_args {List[Any]} -- additional sql query arguments
"""
# fetch the cursor from the DB connection
cursor = mysql_conn.cursor()
try:
# executing the query with values
num_formatting_args = len(formatting_args)
# BN. If FORMATTING_ARGS_PER_QUERY value is too high, you may get '2006 (HY000): MySQL server has
# gone away' error indicating you've exceeded the max_allowed_packet size for MySQL
FORMATTING_ARGS_PER_QUERY = 15000
formatting_args_index = 0
total_rows_affected = 0
logger.debug(
f"Attempting to execute formatted sql on the MLWH database in batches of {FORMATTING_ARGS_PER_QUERY}"
)
while formatting_args_index < num_formatting_args:
logger.debug(
f"Executing sql for formatting args between {formatting_args_index} and \
{formatting_args_index + FORMATTING_ARGS_PER_QUERY}"
)
formatting_args_batch = formatting_args[
formatting_args_index : (formatting_args_index + FORMATTING_ARGS_PER_QUERY) # noqa: E203
]
sql_unwrap_formatted_args = ", ".join(
list(map(lambda x: "%s", formatting_args_batch))
) # e.g. for 3 ids, this would look like "%s,%s,%s"
if len(formatting_args_batch) > 0:
sql_query = (
formatted_sql_query % sql_unwrap_formatted_args
) # formats the query to have the correct number of formatting arguments for the ids
string_args = (
query_args + formatting_args_batch
) # adds the filtered positive arguments to the id arguments
cursor.execute(sql_query, tuple(string_args))
total_rows_affected += cursor.rowcount
logger.debug(f"{cursor.rowcount} rows affected in MLWH.")
formatting_args_index += FORMATTING_ARGS_PER_QUERY
logger.debug("Committing changes to MLWH database.")
mysql_conn.commit()
logger.debug(f"Successfully affected a total of {total_rows_affected} rows in MLWH.")
except Exception:
logger.error("MLWH database execute transaction failed")
raise
finally:
# close the cursor
logger.debug("Closing the cursor.")
cursor.close()
class CMySQLCursorPreparedTests(tests.CMySQLCursorTests):
tbl = "prep_stmt"
create_table_stmt = (
"CREATE TABLE {0} ("
"my_null INT, "
"my_bit BIT(7), "
"my_tinyint TINYINT, "
"my_smallint SMALLINT, "
"my_mediumint MEDIUMINT, "
"my_int INT, "
"my_bigint BIGINT, "
"my_decimal DECIMAL(20,10), "
"my_float FLOAT, "
"my_double DOUBLE, "
"my_date DATE, "
"my_time TIME, "
"my_datetime DATETIME, "
"my_year YEAR, "
"my_char CHAR(100), "
"my_varchar VARCHAR(100), "
"my_enum ENUM('x-small', 'small', 'medium', 'large', 'x-large'), "
"my_geometry POINT, "
"my_blob BLOB)"
)
insert_stmt = (
"INSERT INTO {0} ("
"my_null, "
"my_bit, "
"my_tinyint, "
"my_smallint, "
"my_mediumint, "
"my_int, "
"my_bigint, "
"my_decimal, "
"my_float, "
"my_double, "
"my_date, "
"my_time, "
"my_datetime, "
"my_year, "
"my_char, "
"my_varchar, "
"my_enum, "
"my_geometry, "
"my_blob) "
"VALUES (?, B'1111100', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
"POINT(21.2, 34.2), ?)"
)
data = (
None,
127,
32767,
8388607,
2147483647,
4294967295 if ARCH_64BIT else 2147483647,
decimal.Decimal("1.2"),
3.14,
4.28,
datetime.date(2018, 12, 31),
datetime.time(12, 13, 14),
datetime.datetime(2019, 2, 4, 10, 36, 00),
2019,
"abc",
u"MySQL 🐬",
"x-large",
"random blob data"
)
exp = (
None,
124,
127,
32767,
8388607,
2147483647,
4294967295 if ARCH_64BIT else 2147483647,
decimal.Decimal("1.2000000000"),
3.140000104904175,
4.28000020980835,
datetime.date(2018, 12, 31),
datetime.timedelta(0, 43994),
datetime.datetime(2019, 2, 4, 10, 36), 2019,
"abc",
u"MySQL \U0001f42c",
"x-large",
bytearray(b"\x00\x00\x00\x00\x01\x01\x00\x00\x003333335"
b"@\x9a\x99\x99\x99\x99\x19A@"),
"random blob data"
)
def setUp(self):
config = tests.get_mysql_config()
self.cnx = CMySQLConnection(**config)
self.cur = self.cnx.cursor(prepared=True)
self.cur.execute(self.create_table_stmt.format(self.tbl))
def tearDown(self):
self.cur.execute("DROP TABLE IF EXISTS {0}".format(self.tbl))
self.cur.close()
self.cnx.close()
def test___init__(self):
self.assertIsInstance(self.cur, CMySQLCursorPrepared)
def test_callproc(self):
self.assertRaises(errors.NotSupportedError, self.cur.callproc, None)
def test_close(self):
cur = self.cnx.cursor(prepared=True)
self.assertEqual(None, cur._stmt)
cur.close()
def test_fetchone(self):
self.cur.execute(self.insert_stmt.format(self.tbl), self.data)
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
row = self.cur.fetchone()
self.assertEqual(row, self.exp)
row = self.cur.fetchone()
self.assertIsNone(row)
def test_fetchall(self):
self.cur.execute(self.insert_stmt.format(self.tbl), self.data)
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
rows = self.cur.fetchall()
self.assertEqual(len(rows), 1)
self.assertEqual(rows[0], self.exp)
def test_fetchmany(self):
data = [self.data[:], self.data[:], self.data[:]]
self.cur.executemany(self.insert_stmt.format(self.tbl), data)
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
rows = self.cur.fetchmany(size=2)
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0], self.exp)
self.assertEqual(rows[1], self.exp)
rows = self.cur.fetchmany(1)
self.assertEqual(len(rows), 1)
self.assertEqual(rows[0], self.exp)
def test_executemany(self):
data = [self.data[:], self.data[:]]
self.cur.executemany(self.insert_stmt.format(self.tbl), data)
self.cur.execute("SELECT * FROM {0}".format(self.tbl))
rows = self.cur.fetchall()
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0], self.exp)
self.assertEqual(rows[1], self.exp)
