python: best way to keep db open / or reconect

Question

i have a python script that its always on runing on background

nohup python script.py &

so what script does is: it check on mqtt if any message there and if true it insert it on Database

on_message(client, userdata, msg):
    SQL.execute("INSERT INTO logs(deviceID) VALUES (msg)")

i use this way to connect to db but after few hours connection is closed and script is running but its not able to insert to db

mydb = mysql.connector.connect(host="localhost",  user="xxxx",  passwd="xxxx",  database="xxx")
SQL = mydb.cursor()

Questions:

do i need to open new connection every time before SQL.execute() or better to keep it open?
can u add code that u think is better to use

Does this answer your question? [single database connection throughout the python application (following singleton pattern)](https://stackoverflow.com/questions/40525545/single-database-connection-throughout-the-python-application-following-singleto) — Kir Chou, Mar 29 '20 at 14:10
i think my question can be answered by someone just with 2 wordsand what code should i use — wuqn yqow, Mar 29 '20 at 16:08

JL Peyret · Answer 1 · 2020-03-29T21:40:12.380

0

How frequent are your messages? If they aren’t too frequent, I’d just try open the connection, insert, then close it in an else. Connection are system resources, you should not waste them.

Also, some database engines, no idea about mysql, will periodically garbage-collect long-idle connections and your connection will, unexpectedly, be closed when you attempt using it.


on_message(client, userdata, msg):

    try:
        mydb = mysql.connector.connect(host="localhost",  user="xxxx",  passwd="xxxx",  database="xxx")
        SQL = mydb.cursor()
        #☝️ use mysqls parametrized queries
        SQL.execute("INSERT INTO logs(deviceID) VALUES (%s)", (msg,))
    except Exception as e:
        raise
    else:
        SQL.close()

edited Mar 29 '20 at 21:40

answered Mar 29 '20 at 16:20

JL Peyret

10,917
2
54
73

sometimes 1 hour no msg , but sometimes 1 msg per second. but python script should run in background all the time(1 year :P) – wuqn yqow Mar 29 '20 at 16:27
I’d put the connection open code in a function elsewhere though, esp because of password. Disclaimer: on tablet, so typos are possible on my end. – JL Peyret Mar 29 '20 at 16:33
If a stand alone script w 1 yr uptime reqs I’d get the script working first, then make it into a daemon/service with by launching with a specialized program that keeps it up always : systemd, runit or monit might be good choices on a Linux system for example. runit runs on almost any POSIX. They’re a pain to deal with but steady long uptimes require them. https://en.wikipedia.org/wiki/Runit – JL Peyret Mar 29 '20 at 16:38
Also, your db could be down for a little while - say maintenance - so on intermittent connection errors I’d append the msg into a list and try writing it next time. Or drop it if that’s OK. – JL Peyret Mar 29 '20 at 16:56

score 0 · Answer 2 · answered Mar 29 '20 at 16:27

It's best to open the connection when you need it and not simply leave it open as that will waste a ton of resources.

You can add a decorator to any db handler function like this to reconnect MySQL db when exception 'MySQL server has gone away' raised. Code taken from here

class DB:
    """Database interface"""

    def retry(func):
        def call(self, *args, **kwargs):
            lock.acquire()
            try:
                return func(self, *args, **kwargs)
            except MySQLdb.Error, e:
                if 'MySQL server has gone away' in str(e):
                    # reconnect MySQL
                    self.connect_mysql()
                else:
                    # No need to retry for other reasons
                    pass
            finally:
                lock.release()
        return call

    def __init__(self):
        pass

    def connect_mysql(self):
        # create connection here

    @retry
    def execute(self):
        # use the decorator to get conenction and do you SQL.execute() here

python: best way to keep db open / or reconect

2 Answers2