115

If I use MySQLdb to connect to MySQL-Server through Python. I create a connection and a cursor like this:

connection = MySQLdb.connect(...)
cursor = connection.cursor()
# process

When the MySQL-processing is done one should close the connection. Now I was wondering: Is it sufficient to close the connection by doing:

connection.close()

or do I have to close the cursor first and then the connection? Like this:

cursor.close()
connection.close()
Aufwind
  • 25,310
  • 38
  • 109
  • 154

4 Answers4

109

I will re-iterate the best practice at everyone who comes across the sql connection using MySQLdb or any other package to connect python2/3 needs to know this

(Following mock run assumes that you have a table named tablename in your sql database. It has got 4 columns/fields with names field1,field2,field3,field4). If your connection is local (same machine) then it is 127.0.0.1 also known as "localhost".

The process is to be simple 7 steps

  1. Create connection
  2. Create cursor
  3. Create Query string
  4. Execute the query
  5. Commit to the query
  6. Close the cursor
  7. Close the connection

Here is a simple step by stem mock run

mydb = MySQLdb.connect(host=host, user=user, passwd=passwd, db=database, charset="utf8")
cursor = mydb.cursor()
query = "INSERT INTO tablename (text_for_field1, text_for_field2, text_for_field3, text_for_field4) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (field1, field2, field3, field4))
mydb.commit()
cursor.close()
mydb.close()

Connection and cursor are different. connection is at the SQL level while cursor can be considered as a data element. You can have multiple cursors on the same data within single connection. It is an unusual occurrence to have multiple connections to same data from the same computer.

More has been described here "The cursor paradigm is not specific to Python but are a frequent data structure in databases themselves.

Depending on the underlying implementation it may be possible to generate several cursors sharing the same connection to a database. Closing the cursor should free resources associated to the query, including any results never fetched from the DB (or fetched but not used) but would not eliminate the connection to the database itself so you would be able to get a new cursor on the same database without the need to authenticate again."

Mandar
  • 1,659
  • 1
  • 10
  • 14
  • 2
    Is it compulsory to close the it? What if we don't close it? Could it be affect anything? – curiouscheese Jan 21 '22 at 10:55
  • Not really. But if you are going to have a loop running multiple queries yes! In the production environment please close the cursor. – Mandar Dec 04 '22 at 15:59
19

Closing the cursor as soon as you are done with it is probably the best bet, since you have no use for it anymore. However, I haven't seen anything where it's harmful to close it after the db connection. But since you can set it as:

cursor = conn.cursor()

I recommend closing it before, in case you accidentally assign it again and the DB connection is closed as this would throw an error. So you may want to close it first in order to prevent an accidental reassignment with a closed connection.

(Some don't even close it at all though as it gets collected by the garbage collector (see:In Python with sqlite is it necessary to close a cursor?))

References: When to close cursors using MySQLdb

In Python with sqlite is it necessary to close a cursor?

Community
  • 1
  • 1
lohiaguitar91
  • 522
  • 5
  • 9
3

Closing a connection should be good enough here in this particular context. If you are working with multiple cursors etc. you need to care about proper resource management.

-32

Use with, this tool allows you to create a temporary cursor that will be closed once you return to your previous indentation level.

from contextlib import closing
with closing( connection.cursor() ) as cursor:
    (indented) use the cursor

(non-indented) cursor is closed.
connection.close()
Comte_Zero
  • 252
  • 3
  • 19
S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • 5
    then when should cursor be closed? – accuya Dec 27 '12 at 03:20
  • 96
    Maybe it would be better to explicitly answer the question so people don't just use a tool without knowing what it is doing and why. How about 'think more, but use tools for convenience'? – Bill Rosmus Jun 08 '13 at 14:52
  • Thank you! I was looking for this answer long time due to with connection.cursor() doesn't work. – Roman Podlinov Jun 17 '13 at 18:03
  • 2
    It seems not good for readability: with closing( connection.cursor() ) as cursor: – Walty Yeung Aug 15 '13 at 01:53
  • accuya: the contextlib automatically closes the cursor when the exiting the context (i.e. where it says "cursor closed. Guaranteed") – Klaas van Schelven Jun 16 '14 at 19:59
  • It's better to understand what "with" statement do, this link is explaining it step by step : http://effbot.org/zone/python-with-statement.htm – Abdelali AHBIB Oct 10 '14 at 11:52
  • Interesting ... the accepted answer has (currently) 30 down votes, yet is the accepted answer. Despite the nay-sayers to the pattern, apparently, it is now (2023) the preferred method based on the preponderance of answers/design patterns I've found on (or near) this topic. I believe this does answer the question, albeit indirectly, but is not worse than other "non-answers" I've seen. – user3481644 Mar 05 '23 at 14:04