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()
class CMySQLCursorTests(CMySQLConnectorTests): _cleanup_tables = [] def setUp(self): self.config = get_mysql_config() # Import here allowed from mysql.connector.connection_cext import CMySQLConnection self.cnx = CMySQLConnection(**self.config) def tearDown(self): self.cleanup_tables(self.cnx) self.cnx.close() def setup_table(self, cnx, tbl="myconnpy_cursor", engine="InnoDB"): self.cleanup_table(cnx, tbl) stmt_create = ( "CREATE TABLE {table} " "(col1 INT AUTO_INCREMENT, " "col2 VARCHAR(30), " "col3 INT NOT NULL DEFAULT 0, " "PRIMARY KEY (col1))" "ENGINE={engine}").format( table=tbl, engine=engine) try: cnx.cmd_query(stmt_create) except Exception as err: # pylint: disable=W0703 cnx.rollback() self.fail("Failed setting up test table; {0}".format(err)) else: cnx.commit() self._cleanup_tables.append(tbl) def cleanup_table(self, cnx, tbl="myconnpy_cursor"): stmt_drop = "DROP TABLE IF EXISTS {table}".format(table=tbl) # Explicit rollback: uncommited changes could otherwise block cnx.rollback() try: cnx.cmd_query(stmt_drop) except Exception as err: # pylint: disable=W0703 self.fail("Failed cleaning up test table; {0}".format(err)) if tbl in self._cleanup_tables: self._cleanup_tables.remove(tbl) def cleanup_tables(self, cnx): for tbl in self._cleanup_tables: self.cleanup_table(cnx, tbl)
def test_ssl_cipher_in_option_file(self): config = tests.get_mysql_config() config['option_files'] = [OPTION_FILE] cnx = mysql.connector.MySQLConnection() cnx.config(**config) self.assertIn('cipher', cnx._ssl) self.assertEqual("AES256-SHA:CAMELLIA256-SHA", cnx._ssl['cipher']) if CMySQLConnection: cnx = CMySQLConnection() cnx.config(**config) self.assertIn('cipher', cnx._ssl) self.assertEqual("AES256-SHA:CAMELLIA256-SHA", cnx._ssl['cipher'])
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 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_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 setUp(self): config = tests.get_mysql_config() self.cnx = CMySQLConnection(**config) self.pcnx = MySQLConnection(**config)
class CMySQLConnectionTests(tests.MySQLConnectorTests): def setUp(self): config = tests.get_mysql_config() self.cnx = CMySQLConnection(**config) self.pcnx = MySQLConnection(**config) def test__info_query(self): query = "SELECT 1, 'a', 2, 'b'" exp = (1, 'a', 2, 'b') self.assertEqual(exp, self.cnx.info_query(query)) self.assertRaises(errors.InterfaceError, self.cnx.info_query, "SHOW VARIABLES LIKE '%char%'") def test_client_flags(self): defaults = ClientFlag.default set_flags = self.cnx._cmysql.st_client_flag() for flag in defaults: self.assertTrue(flag_is_set(flag, set_flags)) def test_get_rows(self): self.assertRaises(errors.InternalError, self.cnx.get_rows) query = "SHOW STATUS LIKE 'Aborted_c%'" self.cnx.cmd_query(query) self.assertRaises(AttributeError, self.cnx.get_rows, 0) self.assertRaises(AttributeError, self.cnx.get_rows, -10) self.assertEqual(2, len(self.cnx.get_rows())) self.cnx.free_result() self.cnx.cmd_query(query) self.assertEqual(1, len(self.cnx.get_rows(count=1))) self.assertEqual(1, len(self.cnx.get_rows(count=1))) self.assertEqual([], self.cnx.get_rows(count=1)) self.cnx.free_result() def test_cmd_init_db(self): query = "SELECT DATABASE()" self.cnx.cmd_init_db('mysql') self.assertEqual('mysql', self.cnx.info_query(query)[0]) self.cnx.cmd_init_db('myconnpy') self.assertEqual('myconnpy', self.cnx.info_query(query)[0]) def test_cmd_query(self): query = "SHOW STATUS LIKE 'Aborted_c%'" info = self.cnx.cmd_query(query) exp = { 'eof': { 'status_flag': 32, 'warning_count': 0 }, 'columns': [['Variable_name', 253, None, None, None, None, 0, 1], ('Value', 253, None, None, None, None, 1, 0)] } if tests.MYSQL_VERSION >= (5, 7, 10): exp['columns'][0][7] = 4097 exp['eof']['status_flag'] = 16385 exp['columns'][0] = tuple(exp['columns'][0]) self.assertEqual(exp, info) rows = self.cnx.get_rows() vars = [row[0] for row in rows] self.assertEqual(2, len(rows)) vars.sort() exp = ['Aborted_clients', 'Aborted_connects'] self.assertEqual(exp, vars) exp = ['Value', 'Variable_name'] fields = [fld[0] for fld in info['columns']] fields.sort() self.assertEqual(exp, fields) self.cnx.free_result() info = self.cnx.cmd_query("SET @a = 1") exp = { 'warning_count': 0, 'insert_id': 0, 'affected_rows': 0, 'server_status': 0, 'field_count': 0 } self.assertEqual(exp, info)
def cursor(conn: CMySQLConnection): cur = conn.cursor() try: yield cur finally: cur.close()
def setUp(self): self.config = get_mysql_config() # Import here allowed from mysql.connector.connection_cext import CMySQLConnection self.cnx = CMySQLConnection(**self.config)
def _get_cursor(self, cnx=None): if not cnx: cnx = CMySQLConnection(**self.config) return CMySQLCursor(connection=cnx)
class CMySQLConnectionTests(tests.MySQLConnectorTests): def setUp(self): config = tests.get_mysql_config() self.cnx = CMySQLConnection(**config) self.pcnx = MySQLConnection(**config) def test__info_query(self): query = "SELECT 1, 'a', 2, 'b'" exp = (1, 'a', 2, 'b') self.assertEqual(exp, self.cnx.info_query(query)) self.assertRaises(errors.InterfaceError, self.cnx.info_query, "SHOW VARIABLES LIKE '%char%'") def test_client_flags(self): defaults = ClientFlag.default set_flags = self.cnx._cmysql.st_client_flag() for flag in defaults: self.assertTrue(flag_is_set(flag, set_flags)) def test_get_rows(self): self.assertRaises(errors.InternalError, self.cnx.get_rows) query = "SHOW STATUS LIKE 'Aborted_c%'" self.cnx.cmd_query(query) self.assertRaises(AttributeError, self.cnx.get_rows, 0) self.assertRaises(AttributeError, self.cnx.get_rows, -10) self.assertEqual(2, len(self.cnx.get_rows())) self.cnx.free_result() self.cnx.cmd_query(query) self.assertEqual(1, len(self.cnx.get_rows(count=1))) self.assertEqual(1, len(self.cnx.get_rows(count=1))) self.assertEqual([], self.cnx.get_rows(count=1)) self.cnx.free_result() def test_cmd_init_db(self): query = "SELECT DATABASE()" self.cnx.cmd_init_db('mysql') self.assertEqual('mysql', self.cnx.info_query(query)[0]) self.cnx.cmd_init_db('myconnpy') self.assertEqual('myconnpy', self.cnx.info_query(query)[0]) def test_cmd_query(self): query = "SHOW STATUS LIKE 'Aborted_c%'" info = self.cnx.cmd_query(query) exp = { 'eof': {'status_flag': 32, 'warning_count': 0}, 'columns': [ ['Variable_name', 253, None, None, None, None, 0, 1], ('Value', 253, None, None, None, None, 1, 0) ] } if tests.MYSQL_VERSION >= (5, 7, 10): exp['columns'][0][7] = 4097 exp['eof']['status_flag'] = 16385 exp['columns'][0] = tuple(exp['columns'][0]) self.assertEqual(exp, info) rows = self.cnx.get_rows() vars = [ row[0] for row in rows ] self.assertEqual(2, len(rows)) vars.sort() exp = ['Aborted_clients', 'Aborted_connects'] self.assertEqual(exp, vars) exp = ['Value', 'Variable_name'] fields = [fld[0] for fld in info['columns']] fields.sort() self.assertEqual(exp, fields) self.cnx.free_result() info = self.cnx.cmd_query("SET @a = 1") exp = { 'warning_count': 0, 'insert_id': 0, 'affected_rows': 0, 'server_status': 0, 'field_count': 0 } self.assertEqual(exp, info)
def _get_cursor(self, cnx=None): if not cnx: cnx = CMySQLConnection(**self.config) self.cnx.buffered = True return CMySQLCursorBuffered(connection=cnx)
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)
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 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 CMySQLConnectionTests(tests.MySQLConnectorTests): def setUp(self): config = tests.get_mysql_config() self.cnx = CMySQLConnection(**config) self.pcnx = MySQLConnection(**config) def test__info_query(self): query = "SELECT 1, 'a', 2, 'b'" exp = (1, 'a', 2, 'b') self.assertEqual(exp, self.cnx.info_query(query)) self.assertRaises(errors.InterfaceError, self.cnx.info_query, "SHOW VARIABLES LIKE '%char%'") def test_client_flags(self): defaults = ClientFlag.default set_flags = self.cnx._cmysql.st_client_flag() for flag in defaults: self.assertTrue(flag_is_set(flag, set_flags)) def test_get_rows(self): self.assertRaises(errors.InternalError, self.cnx.get_rows) query = "SHOW STATUS LIKE 'Aborted_c%'" self.cnx.cmd_query(query) self.assertRaises(AttributeError, self.cnx.get_rows, 0) self.assertRaises(AttributeError, self.cnx.get_rows, -10) self.assertEqual(2, len(self.cnx.get_rows()[0])) self.cnx.free_result() self.cnx.cmd_query(query) self.assertEqual(1, len(self.cnx.get_rows(count=1)[0])) self.assertEqual(1, len(self.cnx.get_rows(count=1)[0])) self.assertEqual([], self.cnx.get_rows(count=1)[0]) self.cnx.free_result() def test_cmd_init_db(self): query = "SELECT DATABASE()" self.cnx.cmd_init_db('mysql') self.assertEqual('mysql', self.cnx.info_query(query)[0]) self.cnx.cmd_init_db('myconnpy') self.assertEqual('myconnpy', self.cnx.info_query(query)[0]) def test_cmd_query(self): query = "SHOW STATUS LIKE 'Aborted_c%'" info = self.cnx.cmd_query(query) exp = { 'eof': { 'status_flag': 32, 'warning_count': 0 }, 'columns': [['Variable_name', 253, None, None, None, None, 0, 1], ('Value', 253, None, None, None, None, 1, 0)] } if tests.MYSQL_VERSION >= (5, 7, 10): exp['columns'][0][7] = 4097 exp['eof']['status_flag'] = 16385 exp['columns'][0] = tuple(exp['columns'][0]) self.assertEqual(exp, info) rows = self.cnx.get_rows()[0] vars = [row[0] for row in rows] self.assertEqual(2, len(rows)) vars.sort() exp = ['Aborted_clients', 'Aborted_connects'] self.assertEqual(exp, vars) exp = ['Value', 'Variable_name'] fields = [fld[0] for fld in info['columns']] fields.sort() self.assertEqual(exp, fields) self.cnx.free_result() info = self.cnx.cmd_query("SET @a = 1") exp = { 'warning_count': 0, 'insert_id': 0, 'affected_rows': 0, 'server_status': 0, 'field_count': 0 } self.assertEqual(exp, info) @unittest.skipIf(tests.MYSQL_VERSION < (5, 7, 3), "MySQL >= 5.7.3 is required for reset command") def test_cmd_reset_connection(self): """Resets session without re-authenticating""" exp_session_id = self.cnx.connection_id self.cnx.cmd_query("SET @ham = 2") self.cnx.cmd_reset_connection() self.cnx.cmd_query("SELECT @ham") self.assertEqual(exp_session_id, self.cnx.connection_id) exp = ('2', ) if PY2 else (b'2', ) self.assertNotEqual(exp, self.cnx.get_rows()[0][0]) def test_connection_id(self): """MySQL connection ID""" self.assertEqual(self.cnx._cmysql.thread_id(), self.cnx.connection_id) self.cnx.close() self.assertIsNone(self.cnx.connection_id) self.cnx.connect()