CODEWITHSUNDEEP
pythonmysqlcursorrolespymysql
MeirG
MeirG

Reputation: 343

What function and role does a cursor play in PyMySQL?

I searched the web and the Stack Overflow site in particular, and I couldn't find any simple explanation as to the role a cursor plays in PyMySQL. Why is it required? what function does it fulfill? Can I have multiple cursors? Can I pass it as an argument to a class or a function?

Looking at tutorials with examples I wrote code that uses cursors and does work. But so far the use of cursors is counter intuitive to me without really understanding their role and function.

Please help...

Upvotes: 0

Views: 1110

Answers (1)

Paul-Beyond
Paul-Beyond

Reputation: 1737

The cursor in MySQL is used in most cases to retrieve rows from your resultset and then perform operations on that data. The cursor enables you to iterate over returned rows from an SQL query.

Here is an example.

1) First we declare a cursor:

DECLARE cursor_name CURSOR FOR SELECT_statement;

2) Let's open the cursor.

OPEN cursor_name;

3) Now we can use the FETCH statement to retrieve the next row in the result set. (Recall the syntax for the FETCH statement: FETCH [ NEXT [ FROM ] ] cursor_name INTO variable_list;. As you can see, cursor is within the syntax, so it is a vital part of the FETCH statement).

FETCH cursor_name INTO variable_list;

4) Summary: Okay, so we have used our cursor_name to FETCH the next row, and we store that in variable_list (a list of variables, comma-separated, where the cursor result should be stored).

This should illustrate the following:

  • FETCH use MySQL cursors to fetch the next row in a resultset.
  • The cursor is a tool to iterate over your rows in a resultset, one row at a time.

The pymysql cursor

PyMySQL is used to "interact" with the database. However, take a look at PEP 249 which defines the Python Database API Specification.

PyMySQL is based on the PEP 249 specification, so the cursor is derived from the PEP 249 specification.

And in PEP 249 we see this: https://www.python.org/dev/peps/pep-0249/#cursor-objects

"Cursor Objects These objects represent a database cursor, which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. Cursors created from different connections can or can not be isolated, depending on how the transaction support is implemented (see also the connection's .rollback() and .commit() methods)."

Upvotes: 2

Related Questions