difference between cursor and connection objects

Question

I am confused about why python needs cursor object. I know jdbc and there the database connection is quite intuitive but in python I am confused with cursor object. Also I am doubtful about what is the difference between cursor.close() and connection.close() function in terms of resource release.

score 34 · Accepted Answer · answered May 18 '12 at 22:37

34

The cursor paradigm is not specific to Python but are a frequent data structure in databases themselves.

Depending on the underlying implementation it may be possible to generate several cursors sharing the same connection to a database. Closing the cursor should free resources associated to the query, including any results never fetched from the DB (or fetched but not used) but would not eliminate the connection to the database itself so you would be able to get a new cursor on the same database without the need to authenticate again.

answered May 18 '12 at 22:37

Toote

3,323
2
19
25

1

can we have two cursors at the same time. If yes do mention about concurrency issues – Aman Deep Gautam May 18 '12 at 22:43
2

all those details are hidden in "depending on the underlying implementation". You'll have to read the documentation of the corresponding DB driver – Toote May 18 '12 at 22:49
According to wikipedia: "In computer science, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. ". The way Python implements cursors does not align well with that description - if they were mere iterators they wouldn't be responsible for executing SQL statements. Isn't that something python specific? And something that deserves an explanation? – skyking Apr 20 '17 at 07:51
@skyking: the last sentence of that quote answers your question, cursors are not just for traversal, it facilitates operations that actually require query execution: retrieval, addition and removal. And that is why the cursor itself is used for both executing the query as well as working with its results instead of having separate objects or abstractions. – Toote May 02 '17 at 16:25
@Toote I don't think the wikipedia is very clear on that it's "standard" behavior of a cursor to be able to use it to issue a new query rather than just process the result of it. The next sentence in the article compares it to an iterator. Also the MySQL and postgres link very much looks like it's just a method for process the result rather than issue queries. Also the Android API doesn't seem to include issuing new queries from a cursor, neither do the Berkley DB API. To me it looks like that the way python does it differs, making it somewhat python specific. – skyking May 03 '17 at 08:48
@skyking Python has a standard interface for databases that implement cursors that way. My understanding is that such interface adheres to the definition you provided of "database cursor" from wikipedia, while other interfaces do or don't - or implement a different interpretation of the definition - is up to whoever implemented the interface – Toote May 29 '17 at 21:12
Will anything be different in a threaded application? Eg. when you're trying to run multiple queries in parallell? Do you need connection pooling then - with a connection for each thread? – Hans Henrik Eriksen Nov 30 '21 at 23:19

score 9 · Answer 2 · edited Jun 20 '20 at 09:12

As others mention, a Connection() is the network connection to the database, and it's only real use is to return cursors. PEP-249, where DBApi 2.0 is specified, does not clearly define what exactly a connection or cursor is, nor what the close() method on each must do; only that <module>.connect() must return an instance of <module>.Connection , that <module>.Connection.cursor() must return an instance of <module>.Cursor , and <module>.Cursor.execute() should invoke the statement provided and return the resulting rows. In particular, it does not define a <module>.Connection.execute() , although specific implementations are free to implement them as extensions.

Depending on those extensions is probably unwise, though, since it means you won't have as portable code. DBApi makes this two-level requirement because having an execute on the connection without an intermediate object can be difficult on some databases.

score 7 · Answer 3 · answered May 18 '12 at 22:33

7

Connection object is your connection to the database, close that when you're done talking to the database all together. Cursor object is an iterator over a result set from a query. Close those when you're done with that result set.

answered May 18 '12 at 22:33

John Gaines Jr.

11,174
1
25
25

9

i doubt that it is an iterator because there is a command as execute(sql_string) which executes a sql string. It does not make sense for any iterator for result set to have such function – Aman Deep Gautam May 18 '12 at 22:41
2

You are right, but the cursor is not only a structure on the client but also on the server. It helps utilize prepared SQL statements, execute them and, after they are executed, iterate over its results. – Toote May 18 '12 at 22:50

difference between cursor and connection objects

3 Answers3

Linked