Managing a database connection with a global, global name disappears

Question

class MyAddon(pyxbmct.AddonDialogWindow):
    def __init__(self, title=''):
        super(MyAddon, self).__init__(title)
        self.mysql_connect()
        self.populate()

    def populate(self):
        categories = self.read_data()

    def read_data(self):
        query = ("SELECT category FROM test")
        cursor = connection.cursor()
        categories = cursor.execute(query)
        return categories

    def mysql_connect(self):
        global connection
        try:
            connection = mysql.connector.connect(**config).cursor()
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                xbmc.executebuiltin('Notification(Error!, Bad user name of password)')
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                xbmc.executebuiltin('Notification(Error!, Database does not exist)')
            else:
                xbmc.executebuiltin('Notification(Error!, {0})'.format(err))

I'm developing a Python add-on for Kodi. I get a Global name 'connection' is not defined error when trying to use a global variable for the database connection. I cannot read the global variable connection from the read_data function. I'm sure this is not a forward reference problem because I tested it that way.

The purpose of using a global variable for the connection is to reuse the connection in all the functions without creating a new connection every time.

Answer 1

It may be that Kodi does something funky with namespaces or your instances are pickled; when unpickled the global will be gone. Another problem with a global like this is that the connection might be lost at some point.

I'd restructure the code to have a method that returns a connection, and use that in all methods that require a connection. Make the connection method a classmethod and allow for the connection to be gone:

class MyAddonConnectionFailed(Exception): pass

def read_data(self):
    query = ("SELECT category FROM test")
    try:
        conn = self.connect()
    except MyAddonConnectionFailed:
        # connection failed; error message already displayed
        return []
    cursor = conn.cursor()
    categories = cursor.execute(query)
    return categories

_connection = None

@classmethod
def connect(cls):
    if cls._connection and cls._connection.open:
        return cls._connection

    try:
        cls._connection = mysql.connector.connect(**config).cursor()
        return cls._connection
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            xbmc.executebuiltin('Notification(Error!, Bad user name of password)')
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            xbmc.executebuiltin('Notification(Error!, Database does not exist)')
        else:
            xbmc.executebuiltin('Notification(Error!, {0})'.format(err))
     raise MyAddonConnectionFailed

I'm raising an exception in the connect classmethod; you'll need to decide how you want to handle the case where your database is misconfigured or can't connect. Displaying an error message is not enough. You could still call self.connect() from the __init__ method to signal this problem early of course.