Let the Caller Own the Connection

Here is the whole lifetime of one connection:

show_available_items
    opens connection
    passes connection to list_available_items
    uses returned rows
    closes connection

The function that knows the full operation is the one that can decide when the connection is no longer needed. Read this named caller from top to bottom:

def show_available_items(database_path):
    connection = open_database(database_path)
    try:
        result, rows = list_available_items(connection)
        asset_tags = []
        for row in rows:
            asset_tags.append(row["asset_tag"])
        return asset_tags
    finally:
        connection.close()


show_available_items(database_path)

show_available_items opens one configured connection. It passes that same object to list_available_items(connection), uses the rows, and closes once in finally. The finally clause ensures that closing still happens if reading or transforming the rows raises an exception.

Neither smaller database function owns that whole lifetime. open_database creates and configures a connection, then hands it to its caller alive. list_available_items uses the supplied connection, but it does not know whether the caller wants to run another query next. Closing inside either one would make composition awkward or simply break the next operation.

For the sample catalog, the named call produces:

show_available_items(database_path) -> ['IT-100', 'IT-200', 'IT-400', 'IT-500']
connection closed by caller: yes

You do not need to change catalog_db.py here. The named caller makes the responsibility visible before you polish the return value and add commands. Those functions can now accept a connection without guessing who will close it.