CODEWITHSUNDEEP
pythonsql
alexis
alexis

Reputation: 50220

Why do sql APIs have separate connection and cursor objects?

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?

Upvotes: 3

Views: 171

Answers (1)

Barmar
Barmar

Reputation: 782488

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.

Upvotes: 1

Related Questions