To avoid defining the sqlite3.Connection and sqlite3.Cursor in many functions throughout your code, you can use the following approaches:

  1. Use a context manager: Create a context manager that handles the connection and cursor creation, as well as closing the connection when done. This way, you can use the with statement to manage the connection and cursor in your functions.
import sqlite3

DB_FILE = "your_database_file.db"

class DatabaseConnection:
    def __enter__(self):
        self.conn = sqlite3.connect(DB_FILE)
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.conn.commit()
        self.cursor.close()
        self.conn.close()

def insert_post_to_db(issue: Issue, lemmy_post_id: int) -> None:
    with DatabaseConnection() as cursor:
        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),
        )
  1. Use 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),
    )

Both of these approaches help you avoid defining the connection and cursor in every function that interacts with the database. The context manager approach is more explicit and easier to read, while the function decorator approach is more concise. Choose the one that best fits your coding style and project requirements.

Citations: [1] https://stackoverflow.com/questions/14511337/efficiency-of-reopening-sqlite-database-after-each-query [2] https://stackoverflow.com/questions/50075325/python-sqlite3-nested-cursor-execute [3] https://blog.udemy.com/python-sqlite/ [4] https://stackoverflow.com/questions/54395773/what-are-the-side-effects-of-reusing-a-sqlite3-cursor [5] https://pynative.com/python-sqlite/ [6] https://arctype.com/blog/guide-sqlite-python/ [7] https://sqlite.org/forum/info/4393a42b3b5e2382 [8] https://docs.python.org/3/library/sqlite3.html [9] https://www.reddit.com/r/learnpython/comments/94i4k9/using_a_global_sqlite_cursor_across_multiple/ [10] https://stackoverflow.com/questions/9561832/what-if-i-dont-close-the-database-connection-in-python-sqlite [11] https://climbtheladder.com/10-python-sqlite-best-practices/ [12] https://pypi.org/project/cuttlepool/ [13] https://www.sitepoint.com/sqlite-python/ [14] https://pyneng.readthedocs.io/en/latest/book/25_db/sqlite3.html [15] https://www.geeksforgeeks.org/python-sqlite-connecting-to-database/ [16] https://towardsdatascience.com/python-sqlite-tutorial-the-ultimate-guide-fdcb8d7a4f30 [17] https://codereview.stackexchange.com/questions/285730/simple-connection-pool-for-sqlite-in-python [18] https://developer.android.com/training/data-storage/sqlite [19] https://www.blog.pythonlibrary.org/2021/09/30/sqlite/ [20] https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3 [21] https://developer.android.com/topic/performance/sqlite-performance-best-practices [22] https://www.reddit.com/r/learnpython/comments/8tkbor/how_does_sqlalchemy_connection_pooling_work_with/ [23] https://pymotw.com/2/sqlite3/ [24] https://vegibit.com/interact-with-databases-using-the-python-sqlite3-module/ [25] https://blog.rtwilson.com/a-python-sqlite3-context-manager-gotcha/ [26] https://remusao.github.io/posts/few-tips-sqlite-perf.html [27] https://www.digitalocean.com/community/tutorials/how-to-use-an-sqlite-database-in-a-flask-application [28] https://www.tutorialspoint.com/sqlite/sqlite_python.htm [29] https://www.sqlite.org/whentouse.html [30] https://rogerbinns.github.io/apsw/execution.html [31] https://stackoverflow.com/questions/42635749/sqlite-database-connection-best-practice [32] https://realpython.com/python-mysql/