Cursor.execute() with PyMySQL

Q

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

✍: FYIcenter.com

A

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

2021-09-09, 197👍, 0💬