Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

should I reuse the cursor in the python MySQLdb module

Tags:

I'm writing a python CGI script that will query a MySQL database. I'm using the MySQLdb module. Since the database will be queryed repeatedly, I wrote this function....

def getDatabaseResult(sqlQuery,connectioninfohere):     # connect to the database     vDatabase = MySQLdb.connect(connectioninfohere)     # create a cursor, execute and SQL statement and get the result as a tuple     cursor = vDatabase.cursor()     try:         cursor.execute(sqlQuery)     except:         cursor.close()         return None     result = cursor.fetchall()     cursor.close()     return result 

My question is... Is this the best practice? Of should I reuse my cursor within my functions? For example. Which is better...

def callsANewCursorAndConnectionEachTime():     result1 = getDatabaseResult(someQuery1)     result2 = getDatabaseResult(someQuery2)     result3 = getDatabaseResult(someQuery3)     result4 = getDatabaseResult(someQuery4) 

or do away with the getDatabaseeResult function all together and do something like..

def reusesTheSameCursor():     vDatabase = MySQLdb.connect(connectionInfohere)     cursor = vDatabase.cursor()      cursor.execute(someQuery1)     result1 = cursor.fetchall()      cursor.execute(someQuery2)     result2 = cursor.fetchall()      cursor.execute(someQuery3)     result3 = cursor.fetchall()      cursor.execute(someQuery4)     result4 = cursor.fetchall() 
like image 563
b10hazard Avatar asked Nov 11 '11 20:11

b10hazard


People also ask

Do we need to close cursor in Python?

close() sounds most logical to me. Maybe you can go by the rule: "Close the cursor if you do not need it anymore." Thus commit() before closing the cursor. In the end, for Connector/Python, it does not make much difference, but or other databases it might.

When should I use cursor close?

Use close() when you are done using a cursor. This method closes the cursor, resets all results, and ensures that the cursor object has no reference to its original connection object.

What does cursor () do in Python?

Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection. cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.

What is cursor in Pymysql?

class pymysql.cursors. Cursor (connection) This is the object used to interact with the database. Do not create an instance of a Cursor yourself. Call connections.


1 Answers

The MySQLdb developer recommends building an application specific API that does the DB access stuff for you so that you don't have to worry about the mysql query strings in the application code. It'll make the code a bit more extendable (link).

As for the cursors my understanding is that the best thing is to create a cursor per operation/transaction. So some check value -> update value -> read value type of transaction could use the same cursor, but for the next one you would create a new one. This is again pointing to the direction of building an internal API for the db access instead of having a generic executeSql method.

Also remember to close your cursors, and commit changes to the connection after the queries are done.

Your getDatabaseResult function doesn't need to have a connect for every separate query though. You can share the connection between the queries as long as you act responsible with the cursors.

like image 64
Matti Lyra Avatar answered Oct 07 '22 15:10

Matti Lyra