3. The code
A small notes bot: /note buy milk saves a note, /notes shows your last five.
requirements.txt
python-telegram-bot>=22,<23
aiomysql
bot.py
import os
import aiomysql
from telegram import Update
from telegram.ext import Application, CommandHandler, ContextTypes
async def post_init(application: Application) -> None:
# One pool for the whole bot. The values come from the Environment pane.
pool = await aiomysql.create_pool(
host=os.environ["DB_HOST"],
port=int(os.environ["DB_PORT"]),
user=os.environ["DB_USER"],
password=os.environ["DB_PASSWORD"],
db=os.environ["DB_NAME"],
autocommit=True,
maxsize=5,
)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"CREATE TABLE IF NOT EXISTS notes ("
"id INT AUTO_INCREMENT PRIMARY KEY, "
"user_id BIGINT NOT NULL, "
"text VARCHAR(500) NOT NULL, "
"created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "
"KEY user_id (user_id))"
)
application.bot_data["pool"] = pool
print("Database ready")
print(f"Logged in as @{application.bot.username}")
async def post_shutdown(application: Application) -> None:
pool = application.bot_data.get("pool")
if pool is not None:
pool.close()
await pool.wait_closed()
async def note(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
text = " ".join(context.args).strip()
if not text:
await update.effective_message.reply_text("Write your note after the command, for example: /note buy milk")
return
if len(text) > 500:
await update.effective_message.reply_text("A note can be up to 500 characters.")
return
pool = context.bot_data["pool"]
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"INSERT INTO notes (user_id, text) VALUES (%s, %s)",
(update.effective_user.id, text),
)
await update.effective_message.reply_text("Saved. Send /notes to see your last five.")
async def notes(update: Update, context: ContextTypes.DEFAULT_TYPE) -> None:
pool = context.bot_data["pool"]
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT text FROM notes WHERE user_id = %s ORDER BY id DESC LIMIT 5",
(update.effective_user.id,),
)
rows = await cur.fetchall()
if not rows:
await update.effective_message.reply_text("You have no notes yet.")
return
await update.effective_message.reply_text("\n".join(f"- {text}" for (text,) in rows))
def main() -> None:
application = (
Application.builder()
.token(os.environ["TELEGRAM_BOT_TOKEN"])
.post_init(post_init)
.post_shutdown(post_shutdown)
.build()
)
application.add_handler(CommandHandler("note", note))
application.add_handler(CommandHandler("notes", notes))
application.run_polling()
if __name__ == "__main__":
main()
What it does: run_polling() first asks Telegram who the bot is, then runs post_init, and only then starts fetching messages. post_init opens the pool, creates the notes table if it is missing, puts the pool in application.bot_data["pool"] and prints Database ready and Logged in as @your_bot. Every handler gets the same dictionary as context.bot_data. When the bot stops, run_polling() runs post_shutdown, which closes the pool.
context.args is the text after the command, split into words, so the code joins it back with single spaces: line breaks and double spaces inside a note become one space. If you need the note exactly as typed, take update.effective_message.text and cut off the command yourself.
The %s placeholders are filled in by the driver, so a note such as '); DROP TABLE notes; -- is stored as plain text. In our test it was saved as row 2 and the table stayed. Never build the SQL with an f-string.
Store Telegram IDs as BIGINT
Telegram's Bot API documentation says a user or chat ID "may have more than 32 significant bits" but has at most 52, so a 64-bit integer is safe. Its page on IDs gives the ranges: user IDs go up to 1,099,511,627,775, while group, supergroup and channel IDs are negative. Supergroups and channels run from -1,000,000,000,001 to -1,997,852,516,352 (for example -1001234567890). MySQL's INT stops at 2,147,483,647. In our test, with the same strict SQL mode our database server uses, saving the user ID 7,000,000,001 in an INT column failed. aiomysql raised:
pymysql.err.DataError: (1264, "Out of range value for column 'user_id' at row 1")
A BIGINT column stored it, and -1001234567890 too. Use plain BIGINT, not BIGINT UNSIGNED, so chat IDs fit as well.
4. Run it
- Upload
bot.py and requirements.txt in Files.
- Press Start and open Console. The start installs the packages from
requirements.txt, then shows [PlayNHost] Starting your bot, Database ready and Logged in as @your_bot. That last line turns the status on Overview to Online in Telegram.
- In Telegram, send your bot
/note buy milk, then /notes. To look at the rows, open the database's page and click Open in phpMyAdmin.
From the second start on, the console also shows Warning: Table 'notes' already exists, with the path of aiomysql's cursors.py in front of it. That is MariaDB's note for CREATE TABLE IF NOT EXISTS, passed on by the driver. Nothing is wrong.
In our test the stand-in for Telegram sent these commands and got these answers:
| Sent | Answer |
|---|
/note buy milk | Saved. Send /notes to see your last five. |
/note | Write your note after the command, for example: /note buy milk |
/note and 501 characters | A note can be up to 500 characters. |
/notes | The saved notes, newest first |
/notes from a second user | You have no notes yet. |
One update at a time
With its default settings, python-telegram-bot handles one update at a time: the next command waits until the previous handler has finished. In our test, 4 commands that arrived together were answered in the order they arrived, and after the test the bot held 1 open connection. maxsize=5 still matters if you turn on concurrent_updates in Application.builder(), or if jobs and handlers query at the same time: the pool never opens more than 5 connections, and an extra query waits for a free one.
The autocommit trap
aiomysql opens connections with autocommit off. An INSERT then stays inside an open transaction. When the connection goes back to the pool with that transaction still open, aiomysql closes it, and MariaDB throws the uncommitted row away. In our test, a row inserted on a pool without autocommit=True was seen by 0 other connections. Either pass autocommit=True to create_pool, as above, or call await conn.commit() after your writes.
A failed query does not stop the bot
In our test we deleted the table while the bot was running and sent /notes. python-telegram-bot printed No error handlers are registered, logging exception. and a traceback ending in pymysql.err.ProgrammingError: (1146, "Table '...notes' doesn't exist"), sent no answer for that command, and kept running: once the table was back, the next /note was saved. The person who sent the command sees nothing, so catch the error in the handler, or register a handler with application.add_error_handler(), and answer with a short message.
A quiet night does not break the pool
Our database server closes a connection that has been idle for 8 hours. A bot that gets no messages for that long, overnight for example, finds its pooled connections closed. aiomysql checks for that when a handler asks for a connection and opens a new one in place of a closed one. We tested it with the idle limit lowered to 5 seconds: after 7 quiet seconds, the next /note was saved with no error.
Limits that matter for your pool
- 20 connections at once per database user. Every process that uses the same login counts. Connection 21 is refused with
(1226, "User '...' has exceeded the 'max_user_connections' resource (current value: 20)"). maxsize=5 keeps the bot well under it.
- A query that runs longer than 120 seconds is stopped. MariaDB ends it with error 1969,
Query execution was interrupted (max_statement_time exceeded). In an earlier test of ours, on an account set up the same way, that came after 120.03 seconds. A bot's queries normally take milliseconds, so this only bites a runaway query.
- 500 MB per database. The Databases page shows how much is used. At 500 MB, writes are stopped until you delete rows.
- Server backups do not include the database. Export it from phpMyAdmin from time to time.
Errors and what they mean
These are the errors aiomysql gave in our test, written the way they end a traceback in the console (they come from PyMySQL, which aiomysql uses underneath):
pymysql.err.OperationalError: (1045, "Access denied for user ..."): wrong DB_USER or DB_PASSWORD. After Reset password on the Connection card the old password stops working at once: replace DB_PASSWORD and restart the bot.pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on ..."): wrong host or port, or a connection from outside PlayNHost. The database only accepts connections from PlayNHost servers and phpMyAdmin, so this is also what you get on your own computer.pymysql.err.OperationalError: (1226, ... 'max_user_connections' ...): too many connections open, see the limits above. For example a second copy of the bot, or a pool opened for each message.pymysql.err.DataError: (1264, "Out of range value for column 'user_id' at row 1"): an ID column is INT. Change it to BIGINT.pymysql.err.ProgrammingError: (1146, "Table '...' doesn't exist"): the table is missing. The code above creates it at start; if you made it by hand, check its name.
A variable missing from Environment stops the bot before it connects, with KeyError: 'DB_HOST' or the name of the missing one, because the code reads each value with os.environ[...]. Add it, then restart the bot: variables apply the next time it starts.
A wrong token stops the bot before the database part, because post_init only runs after Telegram has accepted the token. With our fake token against the real Telegram, the bot never printed Database ready and stopped after about 1 second with telegram.error.InvalidToken: The token `...` was rejected by the server. That message contains the whole token, so do not paste console output with it anywhere public. Copy the token again from @BotFather and paste it on the Bot page, pane Telegram. More Telegram errors: Fix Telegram bot 409 Conflict.
How much memory it uses
In our test the running bot used 48.5 MB of memory (resident) after answering the 8 commands, with its pool open, in a container capped at 512 MB like our smallest bot plan. In our earlier hosting test, python-telegram-bot alone measured 44 MB (that bot stopped at a refused token, before handling any message), so the difference, about 4.5 MB, covers aiomysql, the pool and the handled commands. Installing python-telegram-bot and aiomysql with pip from nothing peaked at 73 MB, file cache included. The libraries side by side: python-telegram-bot vs aiogram on PlayNHost.
Same thing with another library
Need a server for your Telegram bot with a database included? See the Telegram bot plans.
