The Problem:
You’re experiencing sqlalchemy.exc.ResourceClosedError: This Connection is closed errors in your Telegram bot, which uses SQLAlchemy for database operations. The error occurs randomly when the bot interacts with the database, suggesting a problem with how you manage database sessions. Your current implementation uses a global db_session variable, leading to connection issues.
Understanding the “Why” (The Root Cause):
The core issue lies in your global db_session variable. In your current setup, a single database session is created and reused across multiple requests handled by your Flask application. This single session is susceptible to issues like connection timeouts or accidental closure. When a connection is closed (e.g., due to inactivity or a database server restart), subsequent attempts to use the db_session result in the ResourceClosedError. Each request to your webhook should ideally use its own, independent database session to prevent conflicts and avoid stale connections.
Step-by-Step Guide:
Step 1: Replace Global Session with Per-Request Sessions:
Refactor your code to create and manage database sessions within the scope of each request. This prevents the reuse of potentially closed or stale sessions. Use SQLAlchemy’s context manager (async with) to ensure proper session cleanup, even if errors occur.
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
import telebot
import flask
from datetime import datetime, timedelta
# ... (Your other imports and configurations) ...
async def create_session():
engine = create_async_engine(database_config['connection_string'], pool_recycle=3600, pool_pre_ping=True, connect_args={'connect_timeout': 10}) #Added connection parameters
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
async with async_session() as session:
yield session
telegram_bot = telebot.TeleBot(bot_config['api_token'])
web_app = flask.Flask(__name__)
@web_app.route('/webhook', methods=['POST'])
async def handle_webhook():
if flask.request.headers.get('content-type') == 'application/json':
request_data = flask.request.get_data().decode('utf-8')
bot_update = telebot.types.Update.de_json(request_data)
async with create_session() as session:
await process_updates(session, [bot_update])
return ''
else:
flask.abort(403)
async def process_updates(session, updates):
for update in updates:
await process_message(session, update)
async def process_message(session, msg):
sender_id = msg.from_user.id
existing_record = await session.execute(
session.query(MessageRecord).filter(
MessageRecord.msg_id == msg.message_id
).statement
)
existing_record = existing_record.scalar() #Return the count as an integer
if existing_record == 0:
expiry_time = datetime.now().replace(second=0, microsecond=0) + \
timedelta(seconds=bot_config['message_duration'])
if msg.pinned_message != None:
expiry_time = None
new_message = MessageRecord(
sender_id=None, order_id=None, msg_id=msg.message_id, expires_at=expiry_time)
session.add(new_message)
await session.commit()
# ... (rest of your code, including webhook setup) ...
if __name__ == "__main__":
web_app.run()
Step 2: Error Handling and rollback():
Wrap your database interactions in try...except blocks to catch potential exceptions. If an exception occurs during a transaction, use session.rollback() before closing the session to ensure data consistency.
Step 3: Verify Database Configuration:
Double-check your database connection string (database_config['connection_string']) and ensure your database server is running and accessible. Incorrect credentials or network issues can cause connection problems. Consider adding parameters to create_async_engine like pool_recycle and pool_pre_ping to improve connection handling.
Common Pitfalls & What to Check Next:
- Incorrect Database URL: Verify that your database connection string is correct, including the host, port, database name, username, and password. A single typo can lead to connection failures.
- Database Server Issues: Check the status of your database server. If the server is down or unreachable, your bot will be unable to connect.
- Deadlocks: In a high-traffic environment, database deadlocks can occur. Examine your database queries for potential deadlock scenarios and optimize them if necessary.
- Connection Pool Exhaustion: If your application receives a large number of concurrent requests, your database connection pool might get exhausted. Increase the pool size if necessary. Review the connection parameters in
create_async_engine.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!