10.5.7 MySQLCursor.execute() Method

Syntax:

cursor.execute(operation, params=None, multi=False)
iterator = cursor.execute(operation, params=None, multi=True)

This method executes the given database operation (query or command). The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style). execute() returns an iterator if multi is True.

Note

In Python, a tuple containing a single value must include a comma. For example, ('abc') is evaluated as a scalar while ('abc',) is evaluated as a tuple.

This example inserts information about a new employee, then selects the data for that person. The statements are executed as separate execute() operations:

insert_stmt = (
  "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_stmt, data)

select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, { 'emp_no': 2 })

The data values are converted as necessary from Python objects to something MySQL understands. In the preceding example, the datetime.date() instance is converted to '2012-03-23'.

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement. However, using parameters does not work well in this case, and it is usually a good idea to execute each statement on its own.

The following example selects and inserts data in a single execute() operation and displays the result of each statement:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):
  if result.with_rows:
    print("Rows produced by statement '{}':".format(
      result.statement))
    print(result.fetchall())
  else:
    print("Number of rows affected by statement '{}': {}".format(
      result.statement, result.rowcount))

If the connection is configured to fetch warnings, warnings generated by the operation are available through the MySQLCursor.fetchwarnings() method.