Ejemplo n.º 1
0
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()
Ejemplo n.º 2
0
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'])
Ejemplo n.º 4
0
    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'])
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
    def setUp(self):
        config = tests.get_mysql_config()
        self.cnx = CMySQLConnection(**config)

        self.pcnx = MySQLConnection(**config)
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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)
Ejemplo n.º 13
0
 def _get_cursor(self, cnx=None):
     if not cnx:
         cnx = CMySQLConnection(**self.config)
     return CMySQLCursor(connection=cnx)
    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)
Ejemplo n.º 16
0
 def _get_cursor(self, cnx=None):
     if not cnx:
         cnx = CMySQLConnection(**self.config)
     self.cnx.buffered = True
     return CMySQLCursorBuffered(connection=cnx)
Ejemplo n.º 17
0
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)
Ejemplo n.º 18
0
 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))
Ejemplo n.º 19
0
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()
Ejemplo n.º 20
0
 def setUp(self):
     self.config = get_mysql_config()
     # Import here allowed
     from mysql.connector.connection_cext import CMySQLConnection
     self.cnx = CMySQLConnection(**self.config)
Ejemplo n.º 21
0
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()