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 value of the OUT parameter pProd
>>> cursor.callproc('multiply', args)
('5', '5', 25L)

This method closes 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 are 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 preceding example, the datetime.date() instance is converted to '2012-03-23'.

When multi is set to True, execute() is able to execute multiple statements. It returns 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 is 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 are available through the MySQLCursor.fetchwarnings() method.

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 preceding 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 raises 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.

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 that provides 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 were executed, it shows the actual statement.

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

Returns a string.

This read-only property returns 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))