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)