How to extend sqlite3 connection object with own functions?

Question

I have a project written in Python 2.7 where the main program needs frequent access to a sqlite3 db for writing logs, measurement results, getting settings,...

At the moment I have a db module with functions such as add_log(), get_setting(), and each function in there basically looks like:

def add_log(logtext):
    try:
        db = sqlite3.connect(database_location)
    except sqlite3.DatabaseError as e:
        db.close()  # try to gracefully close the db
        return("ERROR (ADD_LOG): While opening db: {}".format(e))
    try:
        with db:  # using context manager to automatically commit or roll back changes.
            # when using the context manager, the execute function of the db should be used instead of the cursor
            db.execute("insert into logs(level, source, log) values (?, ?, ?)", (level, source, logtext))
    except sqlite3.DatabaseError as e:
        return("ERROR (ADD_LOG): While adding log to db: {}".format(e))
    return "OK"

(some additional code and comments removed).

It seems I should write a class extends the base sqlite connection object function so that the connection is created only once (at the beginning of the main program), and then this object contains the functionality such as

class Db(sqlite3.Connection):
    def __init__(self, db_location = database_location):
        try:
            self = sqlite3.connect(db_location)
            return self
        except sqlite3.DatabaseError as e:
            self.close()  # try to gracefully close the db

    def add_log(self, logtext):
        self.execute("insert into logs(level, source, log) values (?, ?, ?)", (level, source, logtext))

It seems this should be fairly straightforward but, I can't seem to get it working.

It seems there is some useful advise here: Python: How to successfully inherit Sqlite3.Cursor and add my customized method but I can't seem to understand how to use a similar construct for my purpose.

Answer 1

You are not that far away.

First of all, a class initializer cannot return anything but None (emphasis mine):

Because __new__() and __init__() work together in constructing objects (__new__() to create it, and __init__() to customise it), no non-None value may be returned by __init__(); doing so will cause a TypeError to be raised at runtime.

Second, you overwrite the current instance self of your Db object with a sqlite3.Connection object right in the initializer. That makes subclassing SQLite's connection object a bit pointless.

You just need to fix your __init__ method to make this work:

class Db(sqlite3.Connection):

    # If you didn't use the default argument, you could omit overriding __init__ alltogether
    def __init__(self, database=database_location, **kwargs):
        super(Db, self).__init__(database=database, **kwargs)

    def add_log(self, logtext, level, source):
        self.execute("insert into logs(level, source, log) values (?, ?, ?)", (level, source, logtext))

That lets you use instances of your class as context managers:

with Db() as db:
    print [i for i in db.execute("SELECT * FROM logs")]
    db.add_log("I LAUNCHED THAT PUG INTO SPACE!", 42, "Right there")

---

Maurice Meyer said in the comments of the question that methods such as execute() are cursor methods and, per the DB-API 2.0 specs, that's correct.
However, sqlite3's connection objects offer a few shortcuts to cursor methods:

This is a nonstandard shortcut that creates an intermediate cursor object by calling the cursor method, then calls the cursor’s execute method with the parameters given.

---

To expand on the discussion in the comments:
The remark about the default argument in my code example above was targeted at the requirement to override sqlite3.Connection's __init__ method.

The __init__ in the class Db is only needed to define the default value database_location on the database argument for the sqlite3.Connection initializer.
If you were willing to pass such a value upon every instantiation of that class, your custom connection class could look like this, and still work the same way, except for that argument:

class Db(sqlite3.Connection):

    def add_log(self, logtext, level, source):
        self.execute("insert into logs(level, source, log) values (?, ?, ?)", (level, source, logtext))

However, the __init__ method has nothing to do with the context manager protocol as defined in PEP 343.

When it comes to classes, this protocol requires to implement the magic methods __enter__ and __exit__

The sqlite3.Connection does something along these lines:

class Connection:

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_val is None:
            self.commit()
        else:
            self.rollback()

Note: The sqlite3.Connection is provided by a C module, hence does not have a Python class definition. The above reflects what the methods would roughly look like if it did.

Lets say you don't want to keep the same connection open all the time, but rather have a dedicated connection per transaction while maintaining the general interface of the Db class above.
You could do something like this:

# Keep this to have your custom methods available
class Connection(sqlite3.Connection):

    def add_log(self, level, source, log):
        self.execute("INSERT INTO logs(level, source, log) VALUES (?, ?, ?)", 
                     (level, source, log))


class DBM:

    def __init__(self, database=database_location):
        self._database = database
        self._conn = None

    def __enter__(self):
        return self._connection()

    def __exit__(self, exc_type, exc_val, exc_tb):
        # Decide whether to commit or roll back
        if exc_val:
            self._connection().rollback()
        else:
            self._connection().commit()
        # close connection
        try:
            self._conn.close()
        except AttributeError:
            pass
        finally:
            self._conn = None

    def _connection(self):
        if self._conn is None:
            # Instantiate your custom sqlite3.Connection
            self._conn = Connection(self._database)
        return self._conn

    # add shortcuts to connection methods as seen fit
    def execute(self, sql, parameters=()):
        with self as temp:
            result = temp.execute(sql, parameters).fetchall()
        return result

    def add_log(self, level, source, log):
        with self as temp:
            temp.add_log(level, source, log)

This can be used in a context and by calling methods on the instance:

db = DBM(database_location)

with db as temp:
    print [i for i in temp.execute("SELECT * FROM logs")]
    temp.add_log(1, "foo", "I MADE MASHED POTATOES")

# The methods execute and add_log are only available from
# the outside because the shortcuts have been added to DBM
print [i for i in db.execute("SELECT * FROM logs")]
db.add_log(1, "foo", "I MADE MASHED POTATOES")

---

For further reading on context managers refer to the official documentation. I'll also recommend Jeff Knupp's nice introduction. Also, the aforementioned PEP 343 is worth having a look at for the technical specification and rationale behind that protocol.