Implicitly close `sqlite3` connections with context managers

Ideas
1

I’ve noticed a handful of scenarios where sqlite3 connections remain open when using a context manager in Python. This sometimes happens when a developer forgets to implement an explicit sqlite3.connect.close() after / outside of the context manager’s closure.

This follows the functionality found in the documentation (where connections must be explicitly closed, as per the statement "Note The context manager neither implicitly opens a new transaction nor closes the connection.". While it does follow the documentation, it might be less obvious when using context managers across different objects that do involve an implicit close. Would it make sense to enable an implicit connection close when using sqlite3.connect()'s in context managers to decrease the chance of surprising behavior and developer challenges?

For example, and somewhat related given the pattern of file-based object handling, the input and output documentation covering reading and writing files offers a familiar example that uses implicit file object closes with context managers.

Modified from reading and writing files:

with open('workfile', encoding="utf-8") as f:
    read_data = f.read()

# the file object f is closed automatically

I propose that we follow a similar pattern with sqlite3 connections, closing the connection with the context (instead of requiring an additional call outside of the context.

Modified from sqlite3 docs linked above:

with sqlite3.connect("workfile.sqlite") as con:
    con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
    con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))

# proposed: the sqlite3 connection object con is closed automatically.
# as-is: an additional statement to con.close() is required to properly close the database connection.

Thank you in advance for reading and interested to hear your thoughts on this! I’d also be interested in the historical reasons for this, recognizing there’s likely a cause for the existing functionality.

2 Likes
2

The existing code has the context management handle the commit vs rollback case as the docs you link to show.

It is common that each connection is used more then once so that multiple transactions can be executed. In these cases closing the connection is not wanted.

The comparison with open() falls down when you considered multiple transactions.

1 Like
3

True; the proposed change would be a major breaking change for sqlite3.

I’m -1 to this. If the docs are unclear, we should instead try to improve them. For the behaviour you are looking for, I suggest using contextlib.closing:

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(db)) as cx:
    with cx:
        do_complex_transaction(cx)
    with cx:
        do_another_complex_transaction(cx)
1 Like
4

Thanks @barry-scott - great point. To help account for forward compatibility of this feature without breaking prior behavior, maybe a parameter could be used, for example:

with sqlite3.connect("workfile.sqlite", full_close_context=True) as con:
    con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
    con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))

# proposed: the sqlite3 connection object con is closed automatically.
# as-is: an additional statement to con.close() is required to properly close the database connection.
5

Thank you @erlendaasland - I wasn’t familiar with contextlib.close and thought the solution you raised was very elegant. I tried a modified version of what you provided mixing in what I posted earlier and it worked as described (see below).

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("workfile.sqlite")) as cx:
    with cx:
        cx.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
        cx.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))

# no need to close the connection as contextlib.closing closed it for us

To your point about improving the docs, I’d love to see contextlib.closing mentioned somewhere on the sqlite3 docs to help describe the multi-transactional case of how sqlite3.connect() contexts function by default along with a workaround for cases where folks would like to use it for singular transactions.

2 Likes
6

I agree with this. I had no idea contextlib.closing existed until this thread, and it’s perfect for this use. No need to modify connect at all.

2 Likes
7

I don’t think you need to nest it, can do this:

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("workfile.sqlite")) as cx, cx
    cx.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
    cx.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))

# no need to close the connection as contextlib.closing closed it for us

Demo:

from contextlib import closing, contextmanager

class connect:
    def execute(self, command):
        print(command)
    def close(self):
        print('close')
    def __enter__(self):
        print('enter')
    def __exit__(self, exc_type, exc_val, exc_tb):
        print('exit')

with closing(connect()) as cx, cx:
    cx.execute('query')

Output (Attempt This Online!):

enter
query
exit
close
1 Like
8

True, you don’t need to nest it if you’ve only got one transaction. But, I prefer the explicit variant. It is more readable, IMO.

1 Like
9

I’ll create an issue for this. (Feel free to beat me to it :slight_smile:)

1 Like
10

Thanks @erlendaasland ! I hope you don’t mind, I went ahead and created an issue :slightly_smiling_face: :

1 Like