CODEWITHSUNDEEP
pythonmysql-python
Siddharth Gupta
Siddharth Gupta

Reputation: 1613

When to close a cursor in MySQLdb?

I have a program which runs a loop and every iteration runs a sql query. I currently have it implemented as.

mysql_connection = MySQLdb.connect(...)
for record in records:
  cursor = mysql_connection.cursor()
  curson.execute(some_query)
  result = None
  for r in cursor:
       result = r
  cursor.close()
  #Do something with result
mysql_connection.close()

This is taking a lot of time as everytime the loop runs, a new cursor is created. Is placing the cursor outside the loop a better approach or I can use something else to increase the performance.

I have the search query already on indexed column thus scope of improvement at mysql side is less.

Upvotes: 1

Views: 210

Answers (2)

McGlothlin
McGlothlin

Reputation: 2099

Try setting c.arraysize to a value that makes sense for your use case (where c is the cursor), and stick the cursor outside of the loop. See this page: http://mysql-python.sourceforge.net/MySQLdb.html#cursor-objects

If you wanted more rows, you could use c.fetchmany(n) or c.fetchall(). These do exactly what you think they do. On c.fetchmany(n), the n is optional and defaults to c.arraysize, which is normally 1. Both of these methods return a sequence of rows, or an empty sequence if there are no more rows. If you use a weird cursor class, the rows themselves might not be tuples.

Upvotes: 0

Eugene Soldatov
Eugene Soldatov

Reputation: 10135

It would be better to create cursor one time. Also pay attention that you do return in cycle, so it seems that you call cursor.execute only one time.

Upvotes: 1

Related Questions