3

Before I can get to work with, say, the sqlite3 library in Python, I need to make a connection to the database and a cursor to the connection. 

connection = sqlite3.connect(path)
cursor = connection.cursor()

Queries and result fetching are done with the cursor object, not the connection. From what I've seen this is a standard set-up for Python SQL APIs. (Obviously this is not the usual "cursor" that points to a row in a result set.) In contrast, PHP's mysqli or PDO libraries put a query() method directly on the connection object.

So why the two-step arrangement in Python, and any other library that works this way? What is the use case for maintaining separate connection and cursor objects?

alexis
  • 48,685
  • 16
  • 101
  • 161
  • Did you look at [the docs](https://docs.python.org/2/library/sqlite3.html#connection-objects) to see what is possible with a connection object? – wwii Oct 27 '16 at 22:09
  • [Similar and possibly instructive SO Q&A.](http://stackoverflow.com/q/6318126/2823755). – wwii Oct 27 '16 at 22:16
  • @wwii, I did. I didn't see anything that answers my question. – alexis Oct 27 '16 at 22:27
  • Thanks for the link! My question is not about how it works (I understand that, and I've been using it), but why it was designed that way. The top answer seems to boil down to "there's no good reason for it", doesn't it? – alexis Oct 27 '16 at 22:29
  • ... Trying to find the source to see if there is a *reason* listed - Maybe the developer wanted to separate/encapsulate the db level connection/operations and data retrieval processes.?? I know that with ASP.Net it is the same, create a connectionthen create a recordset (with a a cursor) from the connection object. – wwii Oct 27 '16 at 22:34
  • Think about what happens if you perform multiple concurrenct queries. You need an object that represents each query. In PHP's libraries this is a `result` or `statement` object returned by the `query()` method. In Python, they decided to create this first and call it `cursor`. They're essentially the same thing, just a little different in the order that you perform them. – Barmar Oct 27 '16 at 23:16
  • The choice of why to do it one way or the other is pretty arbitrary, you'd have to ask the Python connector designers why they did it. – Barmar Oct 27 '16 at 23:17
  • Re concurrent queries with separate cursors, it seems that [they are not necessarily supported](http://stackoverflow.com/questions/5573724/can-i-use-multiple-cursors-on-one-connection-with-pyodbc-and-ms-sql-server): Cursors are not independent of each other. I checked before asking this question, and this came up. – alexis Oct 27 '16 at 23:25
  • @alexis It depends on the database. If the database supports multiple queries, you do it using multiple cursors. – Barmar Oct 28 '16 at 00:31

1 Answers1

1

This is most likely just an arbitrary design decision. Most database APIs have some type of object that represents the results of a query, which you can then iterate through to get each of the rows. There are basically two ways you can do this:

  1. Perform a query on the connection object, and it returns a new results object.

  2. Create a results object, and then perform the query on this object to fill it in.

There isn't any significant difference between the two arrangements, and Python has chosen the second method (the results object is called cursor).

Perhaps the first method seems more logical, since most of the cursor methods (e.g. .fetchone()) aren't really useful until after you perform a query. On the other hand, this design separates the object that just represent the database connection from the object that represents all aspects of a specific query. The Python cursor class does have some methods that apply to a specific query and must be called before .execute(): .setinputsizes() and .setoutputsize() to pre-allocate buffers for the query.

Python is hardly unique in this style. Its cursor is not too different from the mysqli_stmt and PDOStatement classes of the modern PHP APIs. In PDO you don't get a statement until you call PDO::prepare(), but with mysqli you have a choice: you can call mysql::prepare() to get a statement, or can use mysqli::stmt_init() to get a fresh statement and then call prepare() and execute() on this object. This is quite similar to the Python style.

Barmar
  • 741,623
  • 53
  • 500
  • 612