How to close database sqlite3 in class?

December 14, 2023

i have recently started exploring DataBases. i cant solve a task: i have a class that makes changes in my DataBase(sqlite3). i dont know how to close correctly db connection. i thought and find a decission. is this optimal and correct?

class DBConnection:
    def __enter__(self):
        self.conn = sqlite3.connect('main.db')
        self.cursor = self.conn.cursor()
        return self.cursor
    def __exit__(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

def db_connection(func):
    def wrapper(*args, **kwargs):
        with DBConnection() as cursor:
            return func(cursor, *args, **kwargs)
        return wrapper 
       
class Database:
    @db_connection
    def some_query(self, cursor, args):
        cursor.execute(query)

>Solution :

this looks okay , but can be improved. i will advise you correct the exit method as the method of the DBConnection class accepts three additonal arguments
(exc_type, exc_val, exc_tb) that are passed by the with statement when an exception occurs.
also note that for ensuring changes are saved before closing connection , you need to add commit before closing connection

here is a modified code based on your example.

import sqlite3

class DBConnection:
    def __enter__(self):
        self.conn = sqlite3.connect('main.db')
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            if exc_type is None:
                self.conn.commit()  # this is to commit if no exception
            self.conn.close()
        return False  

def db_connection(func):
    def wrapper(*args, **kwargs):
        with DBConnection() as cursor:
            return func(cursor, *args, **kwargs)
    return wrapper  

class Database:
    @db_connection
    def some_query(self, cursor, *args):
        query = "YOUR SQL QUERY HERE"
        cursor.execute(query)

here is an Example usage