22.6.7.3. Class connection.MySQLConnection

The MySQLConnection constructor initializes the attributes and when at least one argument is passed, it tries to connect to the MySQL server.

For a complete list or arguments, see Section 22.6.6, "Connector/Python Connection Arguments".

close() is a synonym for disconnect(). See Section 22.6.7.3.19, "Method MySQLConnection.disconnect()".

Configures a MySQLConnection instance after it has been instantiated. For a complete list of possible arguments, see Section 22.6.6, "Connector/Python Connection Arguments".

You could use the config() method to change (for example) the user name, then call reconnect().

cnx = mysql.connector.connect(user='joe', database='test')# Connected as 'joe'cnx.config(user='jane')cnx.reconnect()# Now connected as 'jane'

This method sets up a connection, establishing a session with the MySQL server. If no arguments are given, it uses the already configured or default values. For a complete list of possible arguments, see Section 22.6.6, "Connector/Python Connection Arguments".

This method sends a COMMIT statement to the MySQL server, committing the current transaction. Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))>>> cnx.commit()

To roll back instead and discard modifications, see the rollback() method.

This method returns a MySQLCursor() object, or a subclass of it depending on the passed arguments.

When buffered is True, the cursor fetches all rows after the operation is executed. This is useful when queries return small result sets. Setting raw skips the conversion from MySQL data types to Python types when fetching rows. Raw is usually used when you want to get better performance or you want to do the conversion yourself.

The cursor_class argument can be used to pass a class to use for instantiating a new cursor. It must be a subclass of cursor.CursorBase.

The returned object depends on the combination of the buffered and raw arguments.

Returns a CursorBase instance.

Changes the user using username and password. It also causes the specified database to become the default (current) database. It is also possible to change the character set using the charset argument.

Returns a dictionary containing the OK packet information.

Instructs the server to write debugging information to the error log. The connected user must have the SUPER privilege.

Returns a dictionary containing the OK packet information.

This method makes specified database the default (current) database. In subsequent queries, this database is the default for table references that include no explicit database qualifier.

Returns a dictionary containing the OK packet information.

Checks whether the connection to the server is working.

This method is not to be used directly. Use ping() or is_connected() instead.

Returns a dictionary containing the OK packet information.

This method raises the NotSupportedError exception. Instead, use the SHOW PROCESSLIST statement or query the tables found in the database INFORMATION_SCHEMA.

Asks the server to kill the thread specified by mysql_pid. Although still available, it is better to use the KILL SQL statement.

Returns a dictionary containing the OK packet information.

The following two lines have the same effect:

>>> cnx.cmd_process_kill(123)>>> cnx.cmd_query('KILL 123')

This method sends a QUIT command to the MySQL server, closing the current connection. Since there is no response from the MySQL server, the packet that was sent is returned.

This method sends the given statement to the MySQL server and returns a result. To send multiple statements, use the cmd_query_iter() method instead.

The returned dictionary contains information depending on what kind of query was executed. If the query is a SELECT statement, the result contains information about columns. Other statements return a dictionary containing OK or EOF packet information.

Errors received from the MySQL server are raised as exceptions. An InterfaceError is raised when multiple results are found.

Returns a dictionary.

Similar to the cmd_query() method, but returns a generator object to iterate through results. Use cmd_query_iter() when sending multiple statements, and separate the statements with semicolons.

The following example shows how to iterate through the results after sending multiple statements:

statement = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'for result in cnx.cmd_query_iter(statement):  if 'columns' in result:    columns = result['columns']    rows = cnx.get_rows()  else:    # do something useful with INSERT result

Returns a generator object.

This method flushes tables or caches, or resets replication server information. The connected user must have the RELOAD privilege.

The options argument should be a bitmask value constructed using constants from the constants.RefreshOption class.

For a list of options, see Section 22.6.7.10, "Class constants.RefreshOption".

Example:

>>> from mysql.connector import RefreshOption>>> refresh = RefreshOption.LOG | RefreshOption.THREADS>>> cnx.cmd_refresh(refresh)

Asks the database server to shut down. The connected user must have the SHUTDOWN privilege.

Returns a dictionary containing the OK packet information.

Returns a dictionary containing information about the MySQL server including uptime in seconds and the number of running threads, questions, reloads, and open tables.

This method tries to send a QUIT command and close the socket. It raises no exceptions.

MySQLConnection.close() is a synonymous method name and more commonly used.

This method retrieves all or remaining rows of a query result set, returning a tuple containing the rows as sequences and the EOF packet information. The count argument can be used to obtain a given number of rows. If count is not specified or is None, all rows are retrieved.

The tuple returned by get_rows() consists of:

An InterfaceError is raised when all rows have been retrieved.

MySQLCursor uses the get_rows() method to fetch rows.

Returns a tuple.

This method retrieves the next row of a query result set, returning a tuple.

The tuple returned by get_row() consists of:

The get_row() method is used by MySQLCursor to fetch rows.

This method returns the MySQL server information verbatim as a string, for example '5.6.11-log', or None when not connected.

This method returns the MySQL server version as a tuple, or None when not connected.

Reports whether the connection to MySQL Server is available.

This method checks whether the connection to MySQL is available using the ping() method, but unlike ping(), is_connected() returns True when the connection is available, False otherwise.

This method returns True if the client flag was set, False otherwise.

Check whether the connection to the MySQL server is still available.

When reconnect is set to True, one or more attempts are made to try to reconnect to the MySQL server using the reconnect() method. Use the delay argument (seconds) if you want to wait between each retry.

When the connection is not available, an InterfaceError is raised. Use the is_connected() method to check the connection without raising an error.

Raises InterfaceError on errors.

Attempt to reconnect to the MySQL server.

The argument attempts specifies the number of times a reconnect is tried. The delay argument is the number of seconds to wait between each retry.

You might set the number of attempts higher and use a longer delay when you expect the MySQL server to be down for maintenance, or when you expect the network to be temporarily unavailable.

This method sends a ROLLBACK statement to the MySQL server, undoing all data changes from the current transaction. By default, Connector/Python does not autocommit, so it is possible to cancel transactions when using transactional storage engines such as InnoDB.

>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))>>> cnx.rollback()

To commit modifications, see the commit() method.

This method sets the character set and collation to be used for the current connection. The charset argument can be either the name of a character set, or the numerical equivalent as defined in constants.CharacterSet.

When collation is None, the default collation for the character set is used.

In the following example, we set the character set to latin1 and the collation to latin1_swedish_ci (the default collation for: latin1):

>>> cnx = mysql.connector.connect(user='scott')>>> cnx.set_charset('latin1')

Specify a given collation as follows:

>>> cnx = mysql.connector.connect(user='scott')>>> cnx.set_charset('latin1', 'latin1_general_ci')

This method sets the client flags to use when connecting to the MySQL server, and returns the new value as an integer. The flags argument can be either an integer or a sequence of valid client flag values (see Section 22.6.7.6, "Class constants.ClientFlag").

If flags is a sequence, each item in the sequence sets the flag when the value is positive or unsets it when negative. For example, to unset LONG_FLAG and set the FOUND_ROWS flags:

>>> from mysql.connector.constants import ClientFlag>>> cnx.set_client_flags([ClientFlag.FOUND_ROWS, -ClientFlag.LONG_FLAG])>>> cnx.reconnect()

Note that client flags are only set or used when connecting to the MySQL server. It is therefore necessary to reconnect after making changes.

This method starts a transaction. It accepts accepts arguments indicating whether to use a consistent snapshot and which transaction isolation level to use:

cnx.start_transaction(consistent_snapshot=bool,                      isolation_level=level)

The default consistent_snapshot value is False. The default isolation_level value is None, and permitted values are 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', and 'SERIALIZABLE'.

Invoking start_transaction() raises a ProgrammingError if invoked while a transaction is currently in progress. This differs from executing a START TRANSACTION SQL statement while a transaction is in progress; the statement implicitly commits the current transaction.

To determine whether a transaction is active for the connection, use the the in_transaction property.

start_transaction() was added in MySQL Connector/Python 1.1.0.

This property can be assigned a value of True or False to enable or disable the autocommit feature of MySQL. The property can be invoked to retrieve the current autocommit setting.

Note that autocommit is disabled by default when connecting through Connector/Python. This can be enabled using the autocommit connection parameter.

When the autocommit is turned off, you must commit transactions when using transactional storage engines such as InnoDB or NDBCluster.

>>> cnx.autocommitFalse>>> cnx.autocommit = True>>> cnx.autocommitTrue

This property returns a string indicating which character set is used for the connection, whether or not it is connected.

This property returns a string indicating which collation is used for the connection, whether or not it is connected.

This property returns the integer connection ID (thread ID or session ID) for the current connection or None when not connected.

This property sets the current (default) database by executing a USE statement. The property can also be used to retrieve the current database name.

>>> cnx.database = 'test'>>> cnx.database = 'mysql'>>> cnx.databaseu'mysql'

Returns a string.

This property can be assigned a value of True or False to enable or disable whether warnings should be fetched automatically. The default is False (default). The property can be invoked to retrieve the current warnings setting.

Fetching warnings automatically can be useful when debugging queries. Cursors make warnings available through the method MySQLCursor.fetchwarnings().

>>> cnx.get_warnings = True>>> cursor.execute('SELECT "a"+1')>>> cursor.fetchall()[(1.0,)]>>> cursor.fetchwarnings()[(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]

Returns True or False.

This property returns True or False to indicate whether a transaction is active for the connection. The value is True regardless of whether you start a transaction using the start_transaction() API call or by directly executing a SQL statement such as START TRANSACTION or BEGIN.

>>> cnx.start_transaction()>>> cnx.in_transactionTrue>>> cnx.commit()>>> cnx.in_transactionFalse

in_transaction was added in MySQL Connector/Python 1.1.0.

This property can be assigned a value of True or False to enable or disable whether warnings should raise exceptions. The default is False (default). The property can be invoked to retrieve the current exceptions setting.

Setting raise_on_warnings also sets get_warnings because warnings need to be fetched so they can be raised as exceptions.

Note that you might always want to set the SQL mode if you would like to have the MySQL server directly report warnings as errors (see Section 22.6.7.3.42, "Property MySQLConnection.sql_mode"). It is also good to use transactional engines so transactions can be rolled back when catching the exception.

Result sets needs to be fetched completely before any exception can be raised. The following example shows the execution of a query that produces a warning:

>>> cnx.raise_on_warnings = True>>> cursor.execute('SELECT "a"+1')>>> cursor.fetchall()..mysql.connector.errors.DataError: 1292: Truncated incorrect DOUBLE value: 'a'

Returns True or False.

This read-only property returns the host name or IP address used for connecting to the MySQL server.

Returns a string.

This read-only property returns the TCP/IP port used for connecting to the MySQL server.

Returns an integer.

This property is used to retrieve and set the SQL Modes for the current connection. The value should be a list of different modes separated by comma (","), or a sequence of modes, preferably using the constants.SQLMode class.

To unset all modes, pass an empty string or an empty sequence.

>>> cnx.sql_mode = 'TRADITIONAL,NO_ENGINE_SUBSTITUTION'>>> cnx.sql_mode.split(',')[u'STRICT_TRANS_TABLES', u'STRICT_ALL_TABLES', u'NO_ZERO_IN_DATE',u'NO_ZERO_DATE', u'ERROR_FOR_DIVISION_BY_ZERO', u'TRADITIONAL', u'NO_AUTO_CREATE_USER', u'NO_ENGINE_SUBSTITUTION']>>> from mysql.connector.constants import SQLMode>>> cnx.sql_mode = [ SQLMode.NO_ZERO_DATE, SQLMode.REAL_AS_FLOAT]>>> cnx.sql_modeu'REAL_AS_FLOAT,NO_ZERO_DATE'

Returns a string.

This property is used to set or retrieve the time zone session variable for the current connection.

>>> cnx.time_zone = '+00:00'>>> cur.execute('SELECT NOW()') ; cur.fetchone()(datetime.datetime(2012, 6, 15, 11, 24, 36),)>>> cnx.time_zone = '-09:00'>>> cur.execute('SELECT NOW()') ; cur.fetchone()(datetime.datetime(2012, 6, 15, 2, 24, 44),)>>> cnx.time_zoneu'-09:00'

Returns a string.

This read-only property returns the Unix socket file for connecting to the MySQL server.

Returns a string.

This read-only property returns the user name used for connecting to the MySQL server.

Returns a string.