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.

  • GodOfThunder@lemm.eeOP
    link
    fedilink
    arrow-up
    29
    ·
    1 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),
        )
    
  • GodOfThunder@lemm.eeOP
    link
    fedilink
    arrow-up
    29
    ·
    1 year ago

    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),
            )
    
    • coffeewithalex@lemmy.world
      link
      fedilink
      arrow-up
      6
      ·
      1 year ago

      This, but, with DatabaseConnection being a singleton, and preventing multiple enter clauses.

      You can ensure it’s a singleton by modifying how a new object is built, by overriding the new dunder method. If an instance exists, return that, otherwise create a new one.

    • elguaxo@lemmy.dbzer0.com
      link
      fedilink
      arrow-up
      3
      ·
      1 year ago

      I’ve always liked SQL, so I avoided ORMs for a long time… They are great for so many reasons! I definitely recommend you ORMs, specially the ones that also help you with migrations.

  • const void*@lemmy.world
    link
    fedilink
    arrow-up
    3
    ·
    1 year ago

    Depends on the use case; the question is, do you need to construct and destroy the connection inside each method, or can you do it once, at the start of the program, and exit at the end?

    db = Db(DB_FILE)
    
    post_id = insert_post(db,"hello")
    comment_id = insert_comment(db,post_id)
    
    db.close()