How to use Cursor to run MySQL statements with PyMySQL package?

PyMySQL package provides you 2 ways to run MySQL statements:

1. Use con.query() - If you don't want to receive any data back from the MySQL server, you can use the Connection.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 Cursor object by calling cur=con.cursor(), and use the Cursor.execute() method to run MySQL statements. The Cursor object provides you methods to access received data.

Here are some commonly used methods and properties related Cursor 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, PyMySQL_cursor_execute.py, that uses con.cursor() method to create a Cursor object, which is then used to run MySQL statements and capture received data from the MySQL server.

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

import pymysql
from random import *

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

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

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

try:
    cur = con.cursor()
    id = str(randint(100, 900))
    sql = "INSERT INTO fyi_sites (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 pymysql.Error as err:
    print(err)
    print("Failed to insert data...")

try:
    cur = con.cursor()
    sql = "SELECT id, url, title FROM fyi_sites"
    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 pymysql.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 PyMySQL_cursor_execute.py 

1. res:  1
2. res:  1
2. cur:  <pymysql.cursors.Cursor object at 0x109265fa0>
Records inserted:  1
3. res:  5
3. cur:  <pymysql.cursors.Cursor object at 0x109145610>
101 dev.fyicenter.com Developer FYI
147 dev.fyicenter.com Developer FYI
278 dev.fyicenter.com Developer FYI
473 dev.fyicenter.com Developer FYI
647 dev.fyicenter.com Developer FYI
...

⇒ Python Tutorials

⇐ Change Data with PyMySQL Package

⇑ Python Modules for MySQL Database

⇑⇑ Python Tutorials