Documentation Home
MySQL Connector/Python Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb
HTML Download (TGZ) - 82.9Kb
HTML Download (Zip) - 95.5Kb


10.5.4 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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by ciccio pasticcio on November 17, 2017
Just a clarification, so maybe you don't waste one hour trying to solve the issue.

Remember to add

cnx.commit()

after the statement in order to commit the transaction.
  Posted by Gabor Maghera on March 19, 2018
@ciccio pasticcio you've just made my day. I've been struggling for hours with the call to a stored function seemingly succeeding, but not creating the rows it is responsible for. It was due to a missing commit.

Thank you!

BTW, an alternate way is to set

cnx.autocommit = True
Sign Up Login You must be logged in to post a comment.