Python Mysql Connector 2 cursor

Asked

Viewed 261 times

3

I just started with python and I’m trying that:

import mysql.connector

mydb = mysql.connector.connect(
  infos..
)

cursor = mydb.cursor(buffered=True)
cursor2 = mydb.cursor()
query = ("SELECT * FROM table LIMIT 5")
cursor.execute(query)

for row in cursor.fetchall():
    print(row[1])
    var1 = "blabla"
    var2 = "blblbl"
    cursor2.execute("UPDATE table SET col1 = (%s), col2= (%s) WHERE col3= (%s)", (var1, var2, row[2]))

mydb.commit()
cursor.close()
cursor2.close()

But I keep getting the bug:

mysql.connector.errors.Interfaceerror: 2013: Lost Connection to Mysql server During query

I researched and thought that using "buffered=True" would solve the problem, but it didn’t work. I also tried to create a pool, but when I run at the point of reaching the cursor2 error appears:

Referenceerror: Weakly-referenced Object no longer exists

Can’t two cursor be used for the same connection? That’s it?

mysql python python-3.x
  • 1

    I don’t know the specific lib, so I don’t know if the term "cursor" is native to SQL or some peculiarity of lib. I suggest a peek if you have a direct query function to use in updates (actually, in terms of mysql, you wouldn’t even need a cursor for select, just fetch).

  • I tried looking into this and found this clarification about cursors in python: https://stackoverflow.com/a/39438325/3383534

2 answers

1

What happens is you’re creating two cursor and when you close one of them, it automatically closes the other. Because it’s the same connection, differentiating only where you stored the cursor.

I added the dictionary=True, because without it, when to execute a for (for cases fetchall()) will have to inform the position of the value with index and if your table changes the seating columns the index will bring the wrong value by adding the dictionary, simply enter the name of the column, regardless of the position it is in

Take the test:

import mysql.connector

mysql_cnx = mysql.connector.connect(
  infos..
)

mysql_cnx.autocommit = True
mysql_cnx_cursor = mysql_cnx.cursor(buffered=True, dictionary=True)

query = "SELECT * FROM table LIMIT 5"
mysql_cnx_cursor.execute(query)

resultado_do_select = cursor.fetchall()

for row in resultado_do_select :
    print(row["NOME DA COLUNA 1"])
    var1 = "blabla"
    var2 = "blblbl"
    mysql_cnx_cursor.execute("UPDATE table SET col1 = (%s), col2= (%s) WHERE col3= (%s)", (var1, var2, row["NOME DA COLUNA 2"]))

cursor.close()

follows my crud_mysql.py personal

0

Most of the references I found suggest increasing the default limit of some mysql properties so this error doesn’t happen.

  1. net_read_timeout;
  2. max_allowed_packet;
  3. connect_timeout;
  4. wait_timeout
  5. interactive_timeout

As I do not have access to mysql server and nor permission to set directly in python these Infos, I had to use a "workaround" to circumvent this. Before each query execution I have to "drip" the server and reconnect if I have lost the instance.

conect.ping(reconnect=True, attempts=3, delay=2) Source

Browser other questions tagged mysql python python-3.x

You are not signed in. Login or sign up in order to post.