Podon pyodbc cursor versus database cursor

Question

I use python every day with great attention to working with the database.

Using pyodbc, my standard startup 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 to reuse the cursor for another query instead of creating a new cursor, I can save the result set from the first query as follows:

 results_from_query = cursor_hostname.fetchall() 

And move on.

This approach has worked for me so far.

I recently changed jobs, and some of my new employees, who usually use graphical interfaces to work with their database, began to panic when I demonstrated the above technique. What was set was the cursor keyword. I understand that cursors are big no-no with a DB because they point to set-not logic that aims to direct the host to low / zero parallelization levels and operations like RBAR, but I don’t believe that I’m an ODBC cursor The m declaring above matches the cursor we think of when we have our SQL Server engineering and administrative hats.

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

If I'm wrong, please enlighten me and tell me how I can interact more effectively with my databases.

Why can't you just execute directly from the connection, for example

 connection_hostname.execute(command_hostname) 

It seems to me that ODBC cursor structures, since they have something to do with allowing multiple cursors through the same connection to reduce connection costs and the like. Indent from the base?

Answer 1

Database cursors are reproved and mistrusted by database administrators, usually for a good reason. They are often the source of performance issues, 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, He speaks:

"At my workplace, cursors are not allowed in our SQL Server standards. To use a cursor, we must prove that cursor performance is better than row processing in a different way."

To oversimplify, you can explain to your nervous friends that the python cursor is actually a synonym for what other languages ​​call a recordset or result set, and that their GUI tools also use cursors / records (but don't create a cursor on the DB!) .

difference between cursor and connection objects