Python / mysql.connector - reusing a single database connection across multiple cursors

Question

I am trying to find a way to reuse the DB connection across multiple executions. But upon executing the second db.cursor() I get error:

mysql.connector.errors.OperationalError: MySQL Connection not available.

import mysql.connector

db = mysql.connector.connect(host="***", user="***", password="***", database="***")
db.autocommit = True

cursor = db.cursor()
cursor.execute("call procedure1();")
rows1 = cursor.fetchall()

cursor = db.cursor()
cursor.execute("call procedure2()")
rows2 = cursor.fetchall()

What behavior were you looking for? If you want them to not be isolated (so they can. e.g., share transactions), you should just use the same cursor. If you want them to be isolated (so they can, e.g., have independent transactions), you should use two separate connections. If you don't care, you can do either. — abarnert
– abarnert, Commented Aug 2, 2018 at 19:16
Anyway, I can't actually find this in the MySQL/Connector docs, but with the old-school MySQLdb library, the reason you could only create one cursor per connection was because there was no thread-safe way to meet the DB-API2 requirement that cursors on the same connection are never isolated. I don't know if that's true with the modern C API, or if there's some other reason MySQL Connector/Python does this, or if it's just copying MySQLdb's behavior; hopefully someone who knows more can explain. — abarnert
– abarnert, Commented Aug 2, 2018 at 19:18
thanks @abarnert, all i want is to be able to execute multiple of stored procedures using a single connection using mysql.connector. But the connection gets closed right after my first "fetchall()" — enlightenedOne
– enlightenedOne, Commented Aug 2, 2018 at 19:29
The connection gets closed? Are you sure? Just because it won’t let you create another cursor doesn’t mean it’s closed. Is there a reason you can’t just reuse the first cursor? And, for that matter, is there a reason you can’t just create another connection object with the same string? Either one should work fine for what you say you want. — abarnert
– abarnert, Commented Aug 2, 2018 at 19:36
yep, if I do "print(db.is_connected())" right after the first "fetchall()" i get "False" — enlightenedOne
– enlightenedOne, Commented Aug 2, 2018 at 19:43

Omkar · Accepted Answer · 2019-06-21 07:36:49Z

2

You have to open multiple connections. MySQL is thread-safe, You can let two threads share the same connection, Multiple threads cannot send a query to the MySQL server at the same time on the same connection, so each connection will be able to access their respective cursors, queries and result sets without having an effect on the other connections but each thread or process will need its own connection.

answered Jun 21, 2019 at 7:36

Omkar

3,6403 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python / mysql.connector - reusing a single database connection across multiple cursors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related