what is a mysql buffered cursor w.r.t python mysql connector

Question

Can someone please give an example to understand this?

After executing a query, a MySQLCursorBuffered cursor fetches the entire result set from the server and buffers the rows. For queries executed using a buffered cursor, row-fetching methods such as fetchone() return rows from the set of buffered rows. For nonbuffered cursors, rows are not fetched from the server until a row-fetching method is called. In this case, you must be sure to fetch all rows of the result set before executing any other statements on the same connection, or an InternalError (Unread result found) exception will be raised.

Thanks

Answer 1

I can think of two ways these two types of Cursors are different.

The first way is that if you execute a query using a buffered cursor, you can get the number of rows returned by checking MySQLCursorBuffered.rowcount. However, the rowcount attribute of an unbuffered cursor returns -1 right after the execute method is called. This, basically, means that the entire result set has not yet been fetched from the server. Furthermore, the rowcount attribute of an unbuffered cursor increases as you fetch rows from it, while the rowcount attribute of a buffered cursor remains the same, as you fetch rows from it.

The following snippet code tries to illustrate the points made above:

import mysql.connector


conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

buffered_cursor.execute("select * from people")
print("Row count from a buffer cursor:", buffered_cursor.rowcount)
unbuffered_cursor.execute("select * from people")
print("Row count from an unbuffered cursor:", unbuffered_cursor.rowcount)

print()
print("Fetching rows from a buffered cursor: ")

while True:
    try:
        row = next(buffered_cursor)
        print("Row:", row)
        print("Row count:", buffered_cursor.rowcount)
    except StopIteration:
        break

print()
print("Fetching rows from an unbuffered cursor: ")

while True:
    try:
        row = next(unbuffered_cursor)
        print("Row:", row)
        print("Row count:", unbuffered_cursor.rowcount)
    except StopIteration:
        break

The above snippet should return something like the following:

Row count from a buffered reader:  5
Row count from an unbuffered reader:  -1

Fetching rows from a buffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 5
Row: (2, 'David', 'Bryan')
Row count: 5
Row: (3, 'Tico', 'Torres')
Row count: 5
Row: (4, 'Phil', 'Xenidis')
Row count: 5
Row: (5, 'Hugh', 'McDonald')
Row: 5

Fetching rows from an unbuffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 1
Row: (2, 'David', 'Bryan')
Row count: 2
Row: (3, 'Tico', 'Torres')
Row count: 3
Row: (4, 'Phil', 'Xenidis')
Row count: 4
Row: (5, 'Hugh', 'McDonald')
Row count: 5

As you can see, the rowcount attribute for the unbuffered cursor starts at -1 and increases as we loop through the result it generates. This is not the case with the buffered cursor.

The second way to tell the difference is by paying attention to which of the two (under the same connection) executes first. If you start with executing an unbuffered cursor whose rows have not been fully fetched and then try to execute a query with the buffered cursor, an InternalError exception will be raised, and you will be asked to consume or ditch what is returned by the unbuffered cursor. Below is an illustration:

import mysql.connector


conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

unbuffered_cursor.execute("select * from people")
unbuffered_cursor.fetchone()
buffered_cursor.execute("select * from people")

The snippet above will raise a InternalError exception with a message indicating that there is some unread result. What it is basically saying is that the result returned by the unbuffered cursor needs to be fully consumed before you can execute another query with any cursor under the same connection. If you change unbuffered_cursor.fetchone() with unbuffered_cursor.fetchall(), the error will disappear.

There are other less obvious differences, such as memory consumption. Buffered cursor will likely consume more memory since they fetch the entire result set from the server and buffer the rows.

I hope this proves useful.