connect to mysql in a loop

Question

i have to connect to mysql server and grab some data for ever

so i have two way

1)connect to mysql the grab data in a while

conn = mysql.connector.connect(user='root',password='password',host='localhost',database='db',charset='utf8',autocommit=True)
cursor = conn.cursor(buffered=True)
while True:
  cursor.execute("statments")
  sqlData = cursor.fetchone()
  print(sqlData)
  sleep(0.5)

this working good but if script crashed due to mysql connection problem script goes down

2)connect to mysql in while

while True:
   try:
      conn = mysql.connector.connect(user='root',password='password',host='localhost',database='db',charset='utf8',autocommit=True)
      cursor = conn.cursor(buffered=True)
      cursor.execute("statments")
      sqlData = cursor.fetchone()
      print(sqlData)
      cursor.close()
      conn.close()
      sleep(0.5)
   except:
      print("recoverable error..")

both code working good but my question is which is better?!

Answer 1

Among these two, better way will be to use a single connection but create a new cursor for each statement because creation of new connection takes time but creating a new cursor is fast. You may update the code as:

conn = mysql.connector.connect(user='root',password='password',host='localhost',database='db',charset='utf8',autocommit=True)
while True:
    try:
        cursor = conn.cursor(buffered=True)
        cursor.execute("statments")
        sqlData = cursor.fetchone()
        print(sqlData)
    except Exception: # Catch exception which will be raise in connection loss

        conn = mysql.connector.connect(user='root',password='password',host='localhost',database='db',charset='utf8',autocommit=True)
        cursor = conn.cursor(buffered=True)

    finally:
        cursor.close()

conn.close()    # Close the connection

Also read Defining Clean-up Actions regarding the usage of try:finally block.