How to use MySQLCursor to run MySQL statements with "mysql.connector" module?

"mysql.connector" module provides you 2 ways to run MySQL statements:

1. Use con.cmd_query() - If you don't want to receive any data back from the MySQL server, you can use the MySQLConnection.cmd_query() method to run MySQL statements.

2. Use cur.execute() - If you want to receive data back from the MySQL server, you need create a MySQLCursor object by calling cur=con.cursor(), and use the MySQLCursor.execute() method to run MySQL statements. The MySQLCursor object provides you methods to access received data.

Here are some commonly used methods and properties related MySQLCursor objects.

• cur = con.cursor(): Create a new MySQLCursor object.
• cur.execute(): Run a MySQL statement and capture data received.
• cur.fetchone(): Return the next row as a tuple from the received data.
• cur.fetchall(): Run remaining rows as a list of tuples from the received data.
• for row in cur: Loop through remaining rows to process the received data.
• cur.rowcount: Number of rows affected by the statement.
• cur.lastrowid: The last ID generated by the AUTO_INCREMENT column from an INSERT statement.
• cur.close(): Close this cursor to free up resource.

Here is an example Python script, cursor_execute.py, that uses con.cursor() method to create a MySQLCursor object, which is then used to run MySQL statements and capture received data from the MySQL server.

# cursor_execute.py
# Copyright (c) FYIcenter.com 

from mysql.connector import connect, Error
from random import *

con = connect(host="127.0.0.1", port=3306, \
    user="guest", password="retneciyf")
con.cmd_init_db("test")

try:
    id = str(randint(100, 900))
    sql = "INSERT INTO fyi_links (id, url, title) \
        VALUES ("+id+", 'dev.fyicenter.com', 'Developer FYI')"
    res = con.cmd_query(sql)

    print("1. res: ", res)
    con.commit()
except Error as err:
    print(err)
    print("Failed to insert data...")

try:
    cur = con.cursor()
    id = str(randint(100, 900))
    sql = "INSERT INTO fyi_links (id, url, title) \
        VALUES ("+id+", 'dev.fyicenter.com', 'Developer FYI')"
    res = cur.execute(sql)

    print("2. res: ", res)
    print("2. cur: ", cur)
    print("Records inserted: ", cur.rowcount)

    cur.close()
    con.commit()
except Error as err:
    print(err)
    print("Failed to insert data...")

try:
    cur = con.cursor()
    sql = "SELECT id, url, title FROM fyi_links"
    res = cur.execute(sql)

    print("3. res: ", res)
    print("3. cur: ", cur)
    for (id, url, title) in cur:
        print(id, url, title)

    cur.close()
except Error as err:
    print(err)
    print("Failed to query data...")

con.close()

If you run the above script, you will the following output:

fyicenter> python3 cursor_execute.py 

1. res:  {'field_count': 0, 'affected_rows': 1, 'insert_id': 0, ...
2. res:  None
2. cur:  MySQLCursor: INSERT INTO fyi_links (id, url, title)  ..
Records inserted:  1
3. res:  None
3. cur:  MySQLCursor: SELECT id, url, title FROM fyi_links
101 dev.fyicenter.com Developer FYI
108 dev.fyicenter.com Developer FYI
111 dev.fyicenter.com Developer FYI
...

⇒ PyMySQL Package by pypi.org

⇐ Handle Exceptions with "mysql.connector"

⇑ Python Modules for MySQL Database

⇑⇑ Python Tutorials