Unit Testing with Azure SQL Database

azure_analysis.py

class MyAnalysis:
    '''Class for doing some analysis on the items'''
    def __init__(self, db_server, db_database, db_username, db_password):
        self.logger = logging.getLogger('my.operation')
        self.db_server = db_server
        self.db_database = db_database
        self.db_username = db_username
        self.db_password = db_password
    
    def analysis(self):
        analysis_data = {}
        db_obj = MyDBOperation(self.db_server, self.db_database, self.db_username, self.db_password)
        some_items = db_obj.get_some_valid_items()
        for item in some_items:
            analysis_data['id'] = item['col_a']
            # etc.
            # Some more Logic for analysis
        return analysis_data

azure_sql_db_operation.py

class MyDBOperation:
    '''Class for DB Operations for Azure SQL DB'''
    def __init__(self, db_server, db_database, db_username, db_password):
        self.logger = logging.getLogger('my.dboperation')
        self.db_server = db_server
        self.db_database = db_database
        self.db_username = db_username
        self.db_password = db_password
        self._connect()

    def _connect(self):
        try:
            self.cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
                                       self.db_server + ';DATABASE='+ self.db_database +
                                       ';UID=' + self.db_username + ';PWD=' +
                                       self.db_password)
        except Exception as ex:
            raise SqlConnectException(ex)

    def _reconnect(self):
        if not self.cnxn:
            self._connect()

    def get_some_valid_items(self):
        '''Get Some Items with all data'''
        some_items = []
        self._reconnect()
        try:
            cursor = self.cnxn.cursor()
            cursor.execute(
                "select col_a,col_b,col_c,col_d,col_e from some_table where col_status = 'VALID';")
            columns = [column[0] for column in cursor.description]
            results = []
            for row in cursor.fetchall():
                results.append(dict(zip(columns, row)))
            for item in results:
                # Some Logic to parse and filter the results
                # and accept the items accordingly
                some_items.append(item)
        except Exception as ex:
            raise SqlGetQueryException(ex)
        finally:
            if cursor:
                cursor.close()
            if self.cnxn:
                self.cnxn.close()
        return registered_items