MySQL Connector/Python

This module contains both MySQL server and client error codes defined as module attributes with the error number as value. Using error codes instead of error numbers could make reading the source code a bit easier.

>>> from mysql.connector import errorcode
>>> errorcode.ER_BAD_TABLE_ERROR
1051

See Server Error Codes and Messages and Client Error Codes and Messages.

This exception is the base class for all other exceptions in the errors module. It can be used to catch all errors in a single except statement.

The following example shows how we could catch syntax errors:

import mysql.connector
try:
  cnx = mysql.connector.connect(user='scott', database='employees')
  cursor = cnx.cursor()
  cursor.execute("SELECT * FORM employees")   # Syntax error in query
  cnx.close()
except mysql.connector.Error as err:
  print("Something went wrong: {}".format(err))

Initializing the exception supports a few optional arguments, namely msg, errno, values and sqlstate. All of them are optional and default to None. errors.Error isinternally used by Connector/Python to raise MySQL client and server errors and should not be used by your application to raise exceptions.

The following examples show the result when using no or a combination of the arguments:

>>> from mysql.connector.errors import Error
>>> str(Error())
'Unknown error'
>>> str(Error("Oops! There was an error."))
'Oops! There was an error.'
>>> str(Error(errno=2006))
'2006: MySQL server has gone away'
>>> str(Error(errno=2002, values=('/tmp/mysql.sock', 2)))
"2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)"
>>> str(Error(errno=1146, sqlstate='42S02', msg="Table 'test.spam' doesn't exist"))
"1146 (42S02): Table 'test.spam' doesn't exist"

The example which uses error number 1146 is used when Connector/Python receives an error packet from the MySQL Server. The information is parsed and passed to the Error exception as shown.

Each exception subclassing from Error can be initialized using the above mentioned arguments. Additionally, each instance has the attributes errno, msg and sqlstate which can be used in your code.

The following example shows how to handle errors when dropping a table which does not exists (when you do not want to use the IF EXISTS clause):

import mysql.connector
from mysql.connector import errorcode
cnx = mysql.connector.connect(user='scott', database='test')
try:
  cur.execute("DROP TABLE spam")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_BAD_TABLE_ERROR:
    print("Creating table spam")
  else:
    raise

errors.Error is a subclass of the Python StandardError.

This exception is used for reporting important warnings, however, Connector/Python does not use it. It is included to be compliant with the Python Database Specification v2.0 (PEP-249).

Consider using either more strict Server SQL Modes or the raise_on_warnings connection argument to make Connector/Python raise errors when your queries produce warnings.

errors.Warning is a subclass of the Python StandardError.

This exception is raised for errors originating from Connector/Python itself, not related to the MySQL server.

errors.InterfaceError is a subclass of errors.Error.

This exception is the default for any MySQL error which does not fit the other exceptions.

errors.DatabaseError is a subclass of errors.Error.

This exception is raised when the MySQL server encounters an internal error, for example, when a deadlock occurred.

errors.InternalError is a subclass of errors.DatabaseError.

This exception is raised for errors which are related to MySQL's operations. For example, to many connections, a hostname could not be resolved, bad handshake, server is shutting down, communication errors, and so on.

errors.OperationalError is a subclass of errors.DatabaseError.

This exception is raised on programming errors, for example when you have a syntax error in your SQL or a table was not found.

The following example shows how to handle syntax errors:

try:
    cursor.execute("CREATE DESK t1 (id int, PRIMARY KEY (id))")
except mysql.connector.ProgrammingError as err:
    if err.errno == errorcode.ER_SYNTAX_ERROR:
      print("Check your syntax!")
    else:
      print("Error: {}".format(err))

errors.ProgrammingError is a subclass of errors.DatabaseError.

This exception is raised when the relational integrity of the data is affected. For example, a duplicate key was inserted or a foreign key constraint would fail.

The following example shows a duplicate key error raised as IntegrityError:

cursor.execute("CREATE TABLE t1 (id int, PRIMARY KEY (id))")
try:
    cursor.execute("INSERT INTO t1 (id) VALUES (1)")
    cursor.execute("INSERT INTO t1 (id) VALUES (1)")
except mysql.connector.IntegrityError as err:
    print("Error: {}".format(err))

errors.IntegrityError is a subclass of errors.DatabaseError.

This exception is raised when there were problems with the data. Examples are a column set to NULL when it can not, out of range values for a column, division by zero, column count does not match value count, and so on.

errors.DataError is a subclass of errors.DatabaseError.

This exception is raised is case some feature was used but not supported by the version of MySQL which returned the error. It is also raised when using functions or statements which are not supported by stored routines.

errors.NotSupportedError is a subclass of errors.DatabaseError.

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

For a complete list or arguments, see Chapter 3, Connector/Python Connection Arguments.

See disconnect().

Returns a tuple.

Allows to configure a MySQLConnection instance after it was instantiated. See Chapter 3, Connector/Python Connection Arguments for a complete list of possible arguments.

You could use the config() method to change, for example, the username and call reconnect().

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

This method sets up the connection to the MySQL server. If no arguments are given, it will use the already configured or default values. See Chapter 3, Connector/Python Connection Arguments for a complete list of possible arguments.

This method sends the COMMIT command to the MySQL server, committing the current transaction. Since by default, Connector/Python does not auto commit, it is important to call this method after every transaction which updates data for tables using transactional storage engines.

See the rollback() method for rolling back transactions.

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

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

When buffered is True, the cursor will fetch all rows after the operation was executed. This is useful when queries return small result setts. Setting raw will skip the conversion from MySQL data types to Python types when fetching rows. Raw is usually used when you want to have more performance and/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 has to 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 some debug information to the log. For this to work, 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 do not include an explicit database specifier.

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 will raise 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's better to use the SQL KILL command.

Returns a dictionary containing the OK packet information.

The following two lines do the same:

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

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

This method sends the given statement to the MySQL server and returns a result. If you need to send multiple statements, you have to use the cmd_query_iter() method.

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

Errors received from the MySQL server will be 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. cmd_query_iter() should be used when sending multiple statements which are separated by 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(statement, iterate=True):
  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 bitwise value using constants from the class constants.RefreshOption.

See Section 4.9, “ Class constants.RefreshOption ” for a list of options.

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 will try to send the QUIT command and close the socket. It does not raise any 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 sequence and the EOF packet information. The count argument can be used to get a given amount of rows. If count is not specified or None, all rows are retrieved.

The tuple returned by get_rows() consists of:

An InterfaceError is raised when all rows have been retrieved.

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

Returns a tuple.

This method retrieves the next row of a query result set, returning 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, for example '5.5.24-log', or None when not connected.

Returns a string or None.

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

Returns a tuple or None.

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.

Returns True or False.

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

Returns True or False.

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 if you just want to check the connection without raising an error.

Raises InterfaceError on errors.

Attempt to reconnect with the MySQL server.

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

You may want to 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 temporary unavailable.

This method sends the ROLLBACK command to the MySQL server, committing the current transaction. Since by default, Connector/Python does not auto commit, it is possible to cancel transactions when using transactional storage engines.

See the commit() method for committing transactions.

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

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 will be looked up and used.

The charset argument then be either:

In the following example, we set the character set to latin1 and the collation will be set to the default latin1_swedish_ci:

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

Specify a specific collation as follows:

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

This method sets the client flags which are used when connecting with the MySQL server and returns the new value. The flags argument can be either an integer or a sequence of valid client flag values (see Section 4.5, “ Class constants.ClientFlag ”).

If flags is a sequence, each item in the sequence will set the flag when the value is positive or unset 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 with the MySQL server. It is therefor necessary to reconnect after making changes.

Returns an integer.

This property is used to toggle the auto commit feature of MySQL and retrieve the current state. When the value evaluates to True, auto commit will be turned, otherwise it is turned off.

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

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

>>> cnx.autocommit
False
>>> cnx.autocommit = True
>>> cnx.autocommit
True

Returns True or False.

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

Returns a string.

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

Returns a string.

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

Returns a integer or None.

This property is used to set current (active) database executing the USE command. The property can also be used to retrieve the current database name.

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

Returns a string.

This property is used to toggle whether warnings should be fetched automatically or not. It accepts True or False (default).

Fetching warnings automatically could be useful when debugging queries. Cursors will 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 is used to toggle whether warnings should raise exceptions or not. It accepts True or False (default).

Toggling raise_on_warnings will also toggle get_warnings since warnings need to be fetched so they can be raised as exceptions.

Note that you might always want to check setting SQL Mode if you would like to have the MySQL server directly report warnings as errors. 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 which 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 hostname or IP address used for connecting with the MySQL server.

Returns a string.

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

Returns a integer.

This property is used to retrieve and set the SQL Modes for the current. The value should be 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_mode
u'REAL_AS_FLOAT,NO_ZERO_DATE'

Returns a string.

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

>>> 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_zone
u'-09:00'

Returns a string.

This read-only property returns the UNIX socket user for connecting with the MySQL server.

Returns a string.

This read-only property returns the username used for connecting with the MySQL server.

Returns a string.

The constructor initializes the instance with the optional connection, which should be an instance of MySQLConnection.

In most cases, the MySQLConnection method cursor() is used to instantiate a MySQLCursor object.

This method calls a stored procedure with the given name. The args sequence of parameters must contain one entry for each argument that the routine expects. The result is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

Result set provided by the stored procedure are automatically fetched and stored as MySQLBufferedCursor instances. See stored_results() for more information.

The following example shows how to execute a stored procedure which takes two parameters, multiplies the values and returns the product:

# Definition of the multiply stored procedure:
# CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
# BEGIN
#  SET pProd := pFac1 * pFac2;
# END
>>> args = (5,5,0) # 0 is to hold pprod
>>> cursor.callproc('multiply', args)
>>> cursor.fetchone())
('5', '5', 25L)

This method will close the MySQL cursor, resetting all results and removing the connection.

Use close() every time you are done using the cursor.

This method prepare the given database operation (query or command). The parameters found in the tuple or dictionary params will be bound to the variables in the operation. Variables are specified using %s markers or named markers %(name)s.

For example, insert information about a new employee and selecting again the data of this person:

insert = (
"INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
"VALUES (%s, %s, %s, %s)")
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert, data)
select = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select, { 'emp_no': 2 })

Note that the data is converted from Python object to something MySQL understand. In the above example, the datetime.date() instance is converted to '2012-03-23' in the above example.

When multi is set to True, execute() will be able to execute multiple statements. It will return an iterator which makes it possible to go through all results for each statement. Note that using parameters is not working well in this case, and it's usually a good idea to execute each statement on its own.

In the following example we select and insert data in one operation and display the result:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation):
if result.with_rows:
  print("Statement '{}' has following rows:".format(
    result.statement))
  print(result.fetchall())
else:
  print("Affected row(s) by query '{}' was {}".format(
        result.statement, result.rowcount))

If the connection was configured to fetch warnings, warnings generated by the operation will be available through the method MySQLCursor.fetchwarnings().

Returns an iterator when multi is True.

This method prepares a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_params.

The executemany() is simply iterating through the sequence of parameters calling the execute() method. Inserting data, however, is optimized by batching them using the multiple rows syntax.

In the following example we are inserting 3 records:

data = [
('Jane', date(2005, 2, 12)),
('Joe', date(2006, 5, 23)),
('John', date(2010, 10, 3)),
]
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
cursor.executemany(stmt, data)

In the above example, the INSERT statement sent to MySQL would be as follows: INSERT INTO employees (first_name, hire_date) VALUES ('Jane', '2005-02-12'), ('Joe', '2006-05-23'), ('John', '2010-10-03').

Note that it is not possible to execute multiple statements using the executemany() method. Doing so will raise an InternalError exception.

The method fetches all or remaining rows of a query result set, returning a list of tuples. An empty list is returned when no rows are (anymore) available.

The following examples shows how to retrieve the first 2 rows of a result set, and then retrieve the remaining rows:

>>> cursor.execute("SELECT * FROM employees ORDER BY emp_no")
>>> head_rows = cursor.fetchmany(size=2)
>>> remaining_rows = cursor.fetchall()

Note that you have to fetch all rows before being able to execute new queries using the same connection.

Returns a list of tuples or empty list when no rows available.

This method fetches the next set of rows of a query results, returning a list of tuples. An empty list is returned when no more rows are available.

The number of rows returned can be specified using the size argument, which defaults to one. Fewer rows might be returned, when there are not more rows available than specified by the argument.

Note that you have to fetch all rows before being able to execute new queries using the same connection.

Returns a list of tuples or empty list when no rows available.

This method retrieves the next row of a query result set, returning a single sequence, or None when no more data is available.The returned tuple consists of data returned by the MySQL server converted to Python objects.

The fetchone() method is used by fetchmany() and fetchall(). It is also used when using the MySQLCursor instance as an iterator.

The following examples show how to iterate through the result of a query using fetchone():

# Using a while-loop
cursor.execute("SELECT * FROM employees")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
# Using the cursor as iterator 
cursor.execute("SELECT * FROM employees")
for row in cursor:
print(row)

Note that you have to fetch all rows before being able to execute new queries using the same connection.

Returns a tuple or None.

This method returns a list of tuples containing warnings generated by previously executed statement. Use the connection's get_warnings property to toggle whether warnings has to be fetched.

The following example shows a SELECT statement which generated a warning:

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

It is also possible to raise errors when warnings are found. See the MySQLConnection property raise_on_warnings.

Returns a list of tuples.

This method returns an list iterator object which can be used to go through result sets provided by stored procedures after calling them using the callproc() method.

In the following example we execute a stored procedure which will provide two result sets. We use stored_results() to retrieve them:

>>> cursor.callproc('sp1')
()
>>> for result in cursor.stored_results():
...     print result.fetchall()
... 
[(1,)]
[(2,)]

Note that the result sets stay available until you executed another operation or call another stored procedure.

Returns a listiterator.

This read-only property returns the column names of a result set as sequence of (unicode) strings.

The following example shows how you can create a dictionary out of a tuple containing data with keys using column_names:

cursor.execute("SELECT last_name, first_name, hire_date "
             "FROM employees WHERE emp_no = %s", (123,))
row = dict(zip(cursor.column_names, cursor.fetchone())
print("{last_name}, {first_name}: {hire_date}".format(row))

Returns a tuple.

This read-only property returns the last executed statement. In case multiple statements where executed, it will show the actual statement.

The statement property might be useful for debugging and showing what was send to the MySQL server.

Returns a string.

This read-only property will return True when the result of the executed operation provides rows.

The with_rows property is useful when executing multiple statements and you need to fetch rows. In the following example we only report the affected rows by the UPDATE statement:

import mysql.connector
cnx = mysql.connector.connect(user='scott', database='test')
cursor = cnx.cursor()
operation = 'SELECT 1; UPDATE t1 SET c1 = 2; SELECT 2'
for result in cursor.execute(operation, multi=True):
  if result.with_rows:
    result.fetchall()
  else:
    print("Updated row(s): {}".format(result.rowcount))