SQLAlchemy connection issues in Telegram bot

Other Questions Telegram bot
1

Hey everyone! I’m having trouble with my Telegram bot that uses SQLAlchemy. Sometimes I get this error:

sqlalchemy.exc.ResourceClosedError: This Connection is closed

I think it might be related to how I’m handling sessions, but I’m not sure how to fix it. Here’s a simplified version of my code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import telebot
import flask
from datetime import datetime, timedelta

db = create_engine('sqlite:///mybot.db')
SessionMaker = sessionmaker(bind=db)
current_session = None

bot = telebot.TeleBot('your_token_here')
app = flask.Flask(__name__)

@app.route('/webhook', methods=['POST'])
def handle_update():
    global current_session
    current_session = SessionMaker()
    update = telebot.types.Update.de_json(flask.request.get_json())
    bot.process_new_updates([update])
    return ''

@bot.message_handler(func=lambda msg: msg.chat.id == 12345, content_types=['text'])
def process_message(message):
    msg_id = message.message_id
    existing = current_session.query(Message).filter_by(message_id=msg_id).count()
    if existing == 0:
        expiry = datetime.now() + timedelta(hours=1)
        new_msg = Message(message_id=msg_id, expires_at=expiry)
        current_session.add(new_msg)
        current_session.commit()

bot.set_webhook('https://yourserver.com/webhook')
app.run()

Any ideas on how to properly manage the SQLAlchemy session in this setup? Thanks!

2

I’ve encountered similar issues with SQLAlchemy in my Telegram bots. The problem likely stems from using a global session variable, which can lead to connection timeouts or closures between requests.

Instead, I’d recommend creating a session for each request and closing it afterward. Here’s how I restructured my code to solve this:

def get_db_session():
    Session = sessionmaker(bind=engine)
    return Session()

@app.route('/webhook', methods=['POST'])
def handle_update():
    update = telebot.types.Update.de_json(flask.request.get_json())
    bot.process_new_updates([update])
    return ''

@bot.message_handler(func=lambda msg: msg.chat.id == 12345, content_types=['text'])
def process_message(message):
    with get_db_session() as session:
        msg_id = message.message_id
        existing = session.query(Message).filter_by(message_id=msg_id).count()
        if existing == 0:
            expiry = datetime.now() + timedelta(hours=1)
            new_msg = Message(message_id=msg_id, expires_at=expiry)
            session.add(new_msg)
            session.commit()

This approach ensures each request gets a fresh session and properly closes it, avoiding resource leaks and connection issues. It’s made my bot much more stable.

3

I’ve dealt with similar SQLAlchemy connection issues in web applications. The problem lies in how you’re managing database sessions across multiple requests. A more robust approach is to use SQLAlchemy’s scoped session, which is thread-safe and perfect for web applications.

Here’s how you can modify your code:

from sqlalchemy.orm import scoped_session, sessionmaker

db = create_engine('sqlite:///mybot.db')
Session = scoped_session(sessionmaker(bind=db))

@app.route('/webhook', methods=['POST'])
def handle_update():
    update = telebot.types.Update.de_json(flask.request.get_json())
    bot.process_new_updates([update])
    Session.remove()
    return ''

@bot.message_handler(func=lambda msg: msg.chat.id == 12345, content_types=['text'])
def process_message(message):
    msg_id = message.message_id
    existing = Session.query(Message).filter_by(message_id=msg_id).count()
    if existing == 0:
        expiry = datetime.now() + timedelta(hours=1)
        new_msg = Message(message_id=msg_id, expires_at=expiry)
        Session.add(new_msg)
        Session.commit()

This approach ensures proper session management and should resolve your connection issues.

4

yo, had similar probs. try using a contextmanager for sessions. here’s a snippet:

from contextlib import contextmanager

@contextmanager
def session_scope():
    sess = SessionMaker()
    try:
        yield sess
        sess.commit()
    except:
        sess.rollback()
        raise
    finally:
        sess.close()

use it in ur handler: with session_scope() as sess: # db stuff. works for me