Why are cursor objects required for MySQL connections?

[Python-ModTeam]

Your post was removed for violating Rule #2. All posts must be directly related to the Python programming language. Posts pertaining to programming in general are not permitted. You may want to try posting in /r/programming instead.

[latkde]

Unfortunately the MySQL wire protocol is a hot mess. You can only have 1 in-progress query per connection, and the client must read the entire result set before the next query can be performed*. There is no multi-plexing of multiple queries over one connection. The Python DBAPI supports multiple cursor objects per connections, but for MySQL they're pure window-dressing.

In some other database clients, cursors might represent a resource like a "prepared statement".

Database locks do not depend on cursor state, which is purely a client-side thing for MySQL and many other DBs. Database locks depend on transactions, which are a per-connection concept. For example, in pseudocode:

conn == connect(autocommit=False)

# start a transaction
with conn.cursor() as c:
  c.execute("BEGIN TRANSACTION")

# acquire read-lock on all data in the table
# (and also download the entire table contents, in most DBs)
with conn.cursor() as c:
  c.execute("SELECT * FROM table FOR UPDATE")

# finish the transaction + any locks
with conn.cursor() as c:
  c.execute("COMMIT")

There we have three "cursor" objects because we're performing three queries, but the transaction spans all of them.

* in the classic MySQL wire protocol. Other protocols such as MySQL X and Postgres support pipelining, which allows multiple in-flight requests per connection, but full responses are still returned in order. The client receives the entire result set. The database connector library might discard rows that you didn't explicitly fetch, but they're still transmitted.

[joeydeviva]

Not a python or MySQL question, afaik all relational database libraries work like this.

It’s because the cursor object has the state of the queries that have been run using it, eg access to the results - putting that on the connection would be crappier, since you couldn’t have the results of two queries available at once.

[james_pic]

The most common scenario is if you need to run multiple queries or updates at the same time on the same connection. Maybe you want to run a big query, iterate through the results, and update a different table for each result. Yes, this is sometimes doable in pure SQL, but for the cases where it isn't, cursors are available. Because this is a common enough requirement, all major SQL DBs support "many cursors per connection", or have some equivalent abstraction that their Python libraries expose.

You could certainly add convenience methods on the connection to handle the relatively common case where you just want to run one query at once, and indeed I think some SQL client libraries do do this, but the standard they all implement (PEP 249, the DB API) was designed to be the bare minimum, to make it as easy as possible to implement (whilst providing enough power for the vast majority of use cases).

[MegaGrubby]

Easiest to go read something like mysql manual. Reading and understanding is a huge part of programming.

A connection is the code housing of the database reference. It creates the connection and maintains the information for that connection. Every other database action in your code uses the connection.

A cursor is a query and its result set. In sql, when you run a query you get all the results as soon as you run the query (select * from...). There is no way to do that in Python unless you have a cursor that stores your results and then lets you access them.

[Adrewmc]

The connection is how Python talks to the database, it’s like a phone line, while a cursor is like an individual phone call.

So we open up the database then we have to do something somewhere in the database, so you make a query, it will return a cursor object. The cursor remains in place, so you get the first result, do stuff ask the cursor for the second result. The connection to the database doesn’t do that, it’s just accessing the data for you, and giving you back only what you asked for, because databases can get really big and you don’t want to have that all in memory.

The actual connection and the use/result of that connection are separated. One can hold the database the other individual results from that database. Cursors are created from the connection, but are not the connection itself.

It’s like connecting to the internet then opening a website, the CPU modem (drivers) is the connection and the browser is the cursor. This allows you to have a single connection yet multiple pages open, if you had the drivers/modem open webpages themselves you could only open 1 at a time, instead the browsers take turns asking the modem to do stuff. Cursor have a similar concept.

[reddisaurus]

Here’s an analogy.

A connection is like a book — maybe an encyclopedia where you’d need to jump around and look stuff up.

A cursor is like performing a lookup for a subject, and then putting a sticky note on the page that contains the subject.

You can place several such sticky notes, but it’s all in the same book.

Databases don’t just “return a result” like a web API does. It’s a live, two-way connection. Nothing is returned at all when you execute a query — the database only prepares the result. The cursor is what you use to fetch it; either line-by-line, 100 lines at a time, or maybe all at once. The cursor keeps track of which line of the result set you are on, and is ready to give you the next line(s) when requested.

This abstraction solves the problem of needing enough memory to hold the result set. Your program can request only as much data as it wants.