9

I use python every day with a heavy emphasis on database work.

Using pyodbc my standard start is something like 

connection_hostname = pyodbc.connect('connection_string')
cursor_hostname = connection_hostname.cursor()
command_hostname = 'select * from everything_forever;'
cursor_hostname.execute('command_hostname')

and if i need want to reuse the cursor for another query instead of creating a new cursor, I can store the result set from the first query like so:

results_from_query = cursor_hostname.fetchall()

And move on after that.

This approach has worked well for me so far.

Recently, I changed jobs, and some of my new coworkers who typically use GUIs to work with their DB's started panicking when I demonstrated the above technique. What set them off was the cursor keyword. I understand cursors are a big no-no with DBs because they indicate logic not founded in set theory, tend to push the host into low/zero levels of parallelization, and RBAR type operations, but I don't believe the ODBC cursor I'm declaring above is the same as a cursor we think of when we have our SQL Server engineering and administration hats on.

Can someone explain the difference between these ODBC cursors and SQL Server type cursors (assuming I'm correct that they are different)?

If I am incorrect, please enlighten me and tell me how i can more efficiently interface with my DBs.

Why cant you just execute directly from a connection like 

connection_hostname.execute(command_hostname)

I feel like having ODBC cursor structures as they are has something to do with allowing multiple cursors through the same connection to reduce connection cost and such. Way off base?

zentechinc
  • 369
  • 1
  • 5
  • 15
  • I also have this question. I kind of see the need of a cursor when fetching rows. But what about when executing an INSERT or UPDATE? Do we really need a pyodbc cursor and why? – Marcos Oct 26 '21 at 23:14

1 Answers1

6

Database Cursors are reviled and mistrusted by DBA's, usually for good reason. They're often a source of performance problems, and a set-based approach is almost always better.

http://www.databasejournal.com/features/mssql/article.php/3896206/What-Every-DBA-Ought-to-Know-About-SQL-Server-Cursors-and-Their-Alternatives.htm for example says:

"At my work place, cursors are banned in our SQL Server standards. In order to use a cursor, we have to prove that the performance of the cursor is better than processing the rows another way. "

To over-simplify, you might explain to your nervous friends that a python cursor is actually a synonym for what other languages call a recordset or resultset, and that their GUI tools are also using cursors/recordsets (but not creating a cursor on the DB!).

difference between cursor and connection objects

Community
  • 1
  • 1
Ian McGowan
  • 3,461
  • 3
  • 18
  • 23
  • Good answer. Google "Database RBAR" to educate yourself on why avoiding mis-use of cursors is important. If dealing with small enough tables, you will not see a difference. Once you get to say 10k or more, be warned! Quick example when cursor is OK: sending them to an external API that does not accept batches (you have no choice, though saving to file first is probably better); when not OK: Updating column3 to some value if column1 > column2 (this should be done via a single update statement on the entire table). – spioter Oct 01 '20 at 21:11