Telegram API Question posted in
A comprehensive official documentation can be found here.

Is it correct close db every time? – Telegram API

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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You should not be closing the connection in your class methods. Ideally, you should have a close method in the class and the program using this DB class should be responsible for closing the connection after it is done.

    Ideally, the class should be like:

    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()
        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()

     def close(self)
        self.conn.close()
    Login or Signup to reply.
  3. 0

    There is actually no need to do a close at all. When the program terminates, the connection will be closed automatically (and any outstanding transaction will be rolled back). Of course, if you are creating multiple instances of DBFunction, that is another consideration — you do not necessarily want multiple open connections. But a well-designed DBFunction class should obviate the need for multiple instances. You could also add a "destructor" method so that when your DBFunction instance is garbage collected, the connection will be closed then:

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

    # make "private" -- not intended to be called by client
    def _close(self):
        if self.conn: # but check to make sure it has not been called by client!
            self.conn.close()
            self.conn = None

    def __del__(self):
        self._close()

    """ Other methods """
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search