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.
python
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),
)
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), )