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()
To disconnect Database connection, use close() method. If the connection to a database is closed by the user with the close() method, any outstanding transactions are rolled back by the DB.
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.
For example, if you provide a database name that is not present in MySQL. The is_connected() is the method of the MySQLConnection class through which we can verify is our Python application connected to MySQL. At last, we are closing the MySQL database connection using a close() method of MySQLConnection class.
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
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."
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