Good way to “wrap” the opening and closing of a database around functions in Python?

I’ve looked at a few related questions on StackOverflow and at some documentation/guides regarding wrappers, all of which tells me “no,” but this doesn’t seem right. That said, I’m very new to programming, so 🤷‍♂️

Problem: Opening and closing a database (using python/sqlite3) requires a tedious amount of repeated code (as I understand it):

connection = None
connection = sqlite3.connect(path)
conn.execute("bla bla bla")
conn.commit()
conn.close()

So, I tried to write a reusable wrapper for my functions that access the database. But it’s not ideal and so far, mostly complicates the code (problems discussed afterwards):

import functools
import sqlite3 
from sqlite3 import Error


conn = None         # a global that I hope to get rid of soon.  

def connect_db(function):
    """
    wrapper that should open db, performs function, then closes db
    """
    global conn
    try:
        conn = sqlite3.connect("journ.db")
        print("connected")
    except:
        print("problem")

    @functools.wraps(function)
    def wrapper(*args, **kwargs):
        return function(*args, **kwargs)
        # conn.close()  # unreachable code!!
    return wrapper

@connect_db
def find_entry_info(user_id):
    """
    Queries database regarding users' entries and returns a dictionary (k=entry_url, v=[date, time]).
    """
    entry_info = {}
    # entry_tags = {}
    db_handler = conn.execute("SELECT entry_url, date, time FROM entries WHERE user_id=? ORDER BY date DESC, time DESC",
                        user_id
                            )
    for row in db_handler:
        entry_info[row[0]]=[row[1], row[2]]
    return entry_info

entry_info = find_entry_info(user_id)

The problems that I know of right now:

conn is a global variable, the classic “bad idea”. I’m confident I can figure this one out eventually, but I’m focused more on the following:
there’s no way, based on the documentation, to close the db within the wrapper after returning the needed values from the wrapped function. I could close it within the wrapped function, of course, but that defeats the point of the wrapper, and isn’t any better than calling a regular function to open the database.

So, is there a clever/simple way to write a wrapper that opens/closes the database? A popular library that I’m missing? Should I try looking at python classes . . . or just accept the clutter surrounding my queries? Thanks to all in advance for your time and kindness.

Answer

Connections can be used as context managers which automatically commit transactions or rollback in case of exceptions:

with sqlite3.connect(path) as conn:
    conn.execute(query)

The connection will also automatically be closed at the end.

Advertisement

Answer