I’ve seen two approaches which I’m going to post in the comments to see which one is considered best. Feel free to suggest others.

  • @[email protected]OP
    link
    fedilink
    251 year ago

    A function decorator: You can create a decorator that handles the connection and cursor creation and passes the cursor to the decorated function.

    import sqlite3
    from functools import wraps
    
    DB_FILE = "your_database_file.db"
    
    def with_cursor(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            conn = sqlite3.connect(DB_FILE)
            cursor = conn.cursor()
    
            result = func(cursor, *args, **kwargs)
    
            conn.commit()
            cursor.close()
            conn.close()
    
            return result
    
        return wrapper
    
    @with_cursor
    def insert_post_to_db(cursor: sqlite3.Cursor, issue: Issue, lemmy_post_id: int) -> None:
        cursor.execute(
            "INSERT INTO posts (issue_url, lemmy_post_id, issue_title, issue_body) VALUES (?, ?, ?, ?)",
            (issue.url, lemmy_post_id, issue.title, issue.formatted_body),
        )