Is it correct close db every time?

Question

I need to work on a database with a telegram bot and I write this to manage the db file:

class DBfunction:
    def __init__(self, dbname = 'example.db'):
        self.debname = dbname
        self.conn = sqlite3.connect(dbname)
        self.cur = self.conn.cursor()

    def search_db(self, telegram_id):
        telegram_id = (telegram_id,)
        sql = 'SELECT * FROM user WHERE id = ?;'
        self.cur.execute(sql,telegram_id)
        row = self.cur.fetchone()
        self.conn.close()

        return row

    def newuser_db(self, tele_id, name, nick):
        par = (tele_id, name, nick, 0)
        #print(par)

        sql = 'INSERT INTO user VALUES(?,?,?,?);'

        self.cur.execute(sql, par)
        self.conn.commit()
        self.conn.close()

Is correct use close() in every def of the class or there is a better way?

Answer 1

It really depends on what you are trying to achieve.

What you are doing is closing a connection after a query has run. There is nothing inherently wrong with this.

However, in applications where a lot of queries are constantly made, you may not want to pay the overhead price of trying to open and close connections constantly.

Therefore, you can create a pool of connections (called connection pool) where you can store connections that can be re-used by other queries. This is a pretty common practice and you can read about it on the web.

But again, it really depends on what you are trying to achieve. The overhead of opening and closing connections may not be something you ever have to worry about if your application is not making too many queries and the few milliseconds (if that) you spend on opening and closing a connection doesn't matter to you.

Also, a safer way to open and close connections is to use the with clause. You can read about what it does. The syntax looks something like:

with cur.open() as cur:
    # do your queries

finally:
    # close connection

This ensures your connection is not left open accidentally if there is some error that occurs when you are performing your queries.