使用SQLAlchemy开发Telegram Bot遇ResourceClosedError问题求助

Let's break down why you're hitting this error and how to fix it quickly.

The Root Cause #

Your issue comes down to using a global session variable in a multi-request environment. Flask's webhook endpoint handles concurrent requests, and your current code reassigns the global session every time a webhook hits. But Telegram's message handlers (like chat_handler) don't run in lockstep with the webhook request lifecycle—by the time the handler fires, the previous global session might already be closed or reused by another request, leading to that "connection closed" error.

On top of that, SQLAlchemy Sessions are not thread-safe, so sharing them globally is a recipe for concurrency bugs like this.

Step-by-Step Fixes #

1. Ditch the Global Session

Stop relying on a global session variable entirely. Instead, create a fresh Session inside your message handler for each message processing task. This ensures each operation gets its own isolated connection.

2. Rewrite Your chat_handler with a Local Session

Use a context manager (with statement) to automatically handle Session creation and cleanup. This guarantees the Session is closed properly after use, preventing connection leaks.

Here's the updated handler:

@bot.message_handler(func=lambda message: message.chat.id == config['chat_id'], content_types=['text'])
def chat_handler(message):
    # Create a fresh Session for this message handling task
    with Session() as session:
        tg_id = message.from_user.id
        in_db = session.query(ChatMessage).filter(
            ChatMessage.message_id == message.message_id).count()
        
        if in_db == 0:
            die_time = datetime.now().replace(second=0, microsecond=0) + \
                       timedelta(seconds=config['chat_message_lifetime'])
            if message.pinned_message is not None:
                die_time = None
            
            chat_mess = ChatMessage(
                user_id=None,
                advorder_id=None,
                message_id=message.message_id,
                dietime=die_time)
            
            session.add(chat_mess)
            session.commit()

3. Clean Up the Webhook Route

Remove the global session references from your /tg endpoint—you don't need them anymore:

@app.route('/tg', methods=['POST'])
def webhook():
    if flask.request.headers.get('content-type') == 'application/json':
        json_string = flask.request.get_data().decode('utf-8')
        update = telebot.types.Update.de_json(json_string)
        bot.process_new_updates([update])
        return ''
    else:
        flask.abort(403)

Also delete the global session = None line at the top of your code.

4. Optional: Use Scoped Sessions for Larger Apps

If your bot has multiple handlers that need database access, you can use scoped_session to automatically manage thread-local Sessions. This is cleaner than creating a Session in every handler:

# Replace your Session definition with this
from sqlalchemy.orm import scoped_session

Session = scoped_session(sessionmaker(bind=engine))

# Then in handlers:
@bot.message_handler(...)
def some_handler(message):
    session = Session()
    # Do your DB operations
    session.commit()
    # Clean up when done (or do this at the end of each request)
    Session.remove()

Scoped sessions ensure each thread gets its own Session, avoiding cross-request conflicts.

Why This Works #

By creating a Session per message (or per thread), you eliminate shared connection state. The context manager handles closing the Session automatically, so connections are properly returned to the pool instead of being left in a closed state. This fixes the ResourceClosedError and prevents other concurrency-related bugs.

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