如何在Flask中通过类的__init__管理SQLite数据库以简化调用？

Hey Jack, let's break this down step by step to solve your Flask database setup headache and clear up those questions about classes, self, and __init__.

Step 1: Refactor Your db.py into a Class #

First, let's rewrite your functional database code into a clean, reusable class. This will let you call methods like db.execute() directly without manually invoking get_db() every time.

Here's what the updated db.py would look like:

import sqlite3
from flask import current_app, g

class Database:
    def __init__(self):
        # Replicate the logic from your original get_db() function here
        if 'db' not in g:
            g.db = sqlite3.connect(
                current_app.config['DATABASE'],
                detect_types=sqlite3.PARSE_DECLTYPES
            )
            g.db.row_factory = sqlite3.Row  # Lets us access columns by name
        # Bind the database connection to this instance
        self.conn = g.db

    # Proxy common connection methods so you can call them directly on the instance
    def execute(self, query, args=()):
        return self.conn.execute(query, args)
    
    def fetchall(self):
        return self.conn.fetchall()
    
    def fetchone(self):
        return self.conn.fetchone()
    
    def commit(self):
        return self.conn.commit()
    
    def rollback(self):
        return self.conn.rollback()

    # Optional: Add a method to handle closing (though Flask can do this via teardown)
    @staticmethod
    def close_db(e=None):
        db = g.pop('db', None)
        if db is not None:
            db.close()

Step 2: Use the Class in Your Routes #

Now in routes.py, you can use the class directly—no more repeated get_db() calls:

from flask import Blueprint, jsonify
from .db import Database

bp = Blueprint('api', __name__)

@bp.route('/users')
def get_all_users():
    # Create a Database instance (connection is handled automatically in __init__)
    db = Database()
    db.execute('SELECT id, username FROM users')
    users = db.fetchall()
    
    # Convert rows to dictionaries for JSON response
    user_list = [dict(user) for user in users]
    return jsonify(user_list)

@bp.route('/users/<int:user_id>')
def get_user(user_id):
    db = Database()
    db.execute('SELECT * FROM users WHERE id = ?', (user_id,))
    user = db.fetchone()
    
    if not user:
        return jsonify({"error": "User not found"}), 404
    return jsonify(dict(user))

Understanding __init__ and self #

Let's demystify these two core class concepts:

• __init__: This is the class constructor. When you create an instance (like db = Database()), Python automatically runs this method. It's where you set up the instance's initial state—here, we're grabbing or creating the database connection and attaching it to the instance.
• self: This is a reference to the current instance of the class. When you write self.conn = g.db, you're storing the database connection as a property of that specific Database object. All other methods (like execute) use self to access that shared connection.

Why Classes Are Better Than Functions Here #

You asked about the advantages of using a class over your original functional approach—here are the key benefits:

• Cleaner, more readable code: Instead of scattering get_db() calls everywhere, you create a single Database instance and call intuitive methods like db.execute() or db.commit().
• Encapsulation: All database-related logic lives in one place. If you need to tweak how connections are made (e.g., add error handling or switch to PostgreSQL later), you only modify the Database class—not every route that uses the database.
• State management: The class holds onto the database connection for you. You don't have to worry about repeatedly fetching connections; the __init__ method handles that once per instance.
• Scalability: Adding new functionality (like bulk inserts or transaction helpers) is as simple as adding a new method to the class. Your route code stays clean and focused on business logic, not database boilerplate.

Bonus: Integrate with Flask's Teardown #

Don't forget to register the close_db method with Flask to ensure connections are closed after each request. Add this to your Flask app setup (e.g., app.py):

from .db import Database

app = Flask(__name__)
# ... other app config ...

# Register teardown to close database connections
app.teardown_appcontext(Database.close_db)

内容的提问来源于stack exchange，提问作者Jack