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?
The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures. You can create Cursor object using the cursor () method of the Connection object/class.
pyODBC uses the Microsoft ODBC driver for SQL Server. If your version of the ODBC driver is 17.1 or later, you can use the AAD interactive mode of the ODBC driver through pyODBC. This AAD interactive option works if Python and pyODBC allow the ODBC driver to pop up the dialog.
People who worked with PostgreSQL and Python might have already worked with an object called `cursor` in their code. There is much more a cursor can do apart from merely holding data from a SQL query result.
You can create Cursor object using the cursor () method of the Connection object/class. Following are the various methods provided by the Cursor class/object. This method is used to call existing procedures MySQL database.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With