Create connection to MYSQL once or create every time?

Question

Python

Let's say I'm writing a telegram bot in python and I need to do something with the database. The connection must be created once, or each time a message is processed.
I consider that it is necessary to create one connection and to hold. for example

bot = telebot.TeleBot("")
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="root",
    port="3307",
    database="youtube"
)
db.reconnect(attempts = 100, delay=10)

@bot.message_handler()
def route(message):
    cursor = db.cursor();
    sql = "SELECT * users"
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    bot.send_message(message.chat.id, "Hello world!")

However, my friend desperately argues that it is necessary to create a connection every time. like this for example.

bot = telebot.TeleBot("")

@bot.message_handler()
def route(message):
      db = mysql.connector.connect(
          host="localhost",
          user="root",
          passwd="root",
          port="3307",
          database="youtube"
      )
     cursor = db.cursor();
     sql = "SELECT * users"
     cursor.execute(sql)
     result = cursor.fetchall()
     db.commit()
     db.close()
     bot.send_message(message.chat.id, "Hello world!")

Which option is correct? Are there any proofs, documentation for this case?

Answer 1

In general, MySQL automatically breaks the connection in case of downtime, and as far as I know this can be configured, in my project I create a connection every time and at the end of the function I break

Answer 2

The first option won't work. connect() returns a single connection, not a pool

bot = telebot.TeleBot("")
# создаем пулл из 3 соединений например
cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
                                                      pool_size = 3,
                                                      host="localhost",
    user="root",
    passwd="root",
    port="3307",
    database="youtube")

@bot.message_handler()
def route(message):
    # достаем соединение из пулла
    db = cnxpool.get_connection()
    cursor = db.cursor();
    sql = "SELECT * users"
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    # закрываем соединение. оно вернется обратно в пулл
    db.close()
    bot.send_message(message.chat.id, "Hello world!")

A new connection, although established quite quickly, still wastes server resources. Spending them many times when you can one is certainly not worth it. so this one is better in my opinion