3. The code
A small notes bot: /note buy milk saves a note, /notes shows your last five.
requirements.txt
aiogram>=3,<4
aiomysql
bot.py
import asyncio
import logging
import os
import sys
import aiomysql
from aiogram import Bot, Dispatcher
from aiogram.filters import Command, CommandObject
from aiogram.types import Message
dp = Dispatcher()
@dp.message(Command("note"))
async def note(message: Message, command: CommandObject, pool: aiomysql.Pool) -> None:
text = (command.args or "").strip()
if not text:
await message.answer("Write your note after the command, for example: /note buy milk")
return
if len(text) > 500:
await message.answer("A note can be up to 500 characters.")
return
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"INSERT INTO notes (user_id, text) VALUES (%s, %s)",
(message.from_user.id, text),
)
await message.answer("Saved. Send /notes to see your last five.")
@dp.message(Command("notes"))
async def notes(message: Message, pool: aiomysql.Pool) -> None:
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",
(message.from_user.id,),
)
rows = await cur.fetchall()
if not rows:
await message.answer("You have no notes yet.")
return
await message.answer("\n".join(f"- {text}" for (text,) in rows))
async def main() -> 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))"
)
print("Database ready")
bot = Bot(os.environ["BOT_TOKEN"])
try:
# Every keyword passed here reaches the handlers that ask for it by name.
await dp.start_polling(bot, pool=pool)
finally:
pool.close()
await pool.wait_closed()
if __name__ == "__main__":
logging.basicConfig(level=logging.INFO, stream=sys.stdout)
asyncio.run(main())
What it does: main() opens the pool, creates the notes table if it is missing and prints Database ready before the bot asks Telegram for messages. dp.start_polling(bot, pool=pool) is aiogram's own way to share an object: every extra keyword you pass becomes data that aiogram hands to any handler with a parameter of that name, so note() and notes() get the same pool. When the bot stops, the finally block closes the pool.
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 Run polling for bot @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. |
Many messages at once
aiogram handles each incoming update as its own task, so several commands can run their queries at the same time. In our test, 4 commands that arrived together were each handled in about 50 ms. maxsize=5 caps the pool at 5 connections; a sixth query waits for a free one instead of opening another. After the test, the bot held 2 open connections.
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. aiogram logged the error with 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 if a query can fail, catch the error in the 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 shows up after the database part. With our fake token against the real Telegram, the bot printed Database ready, then stopped with aiogram.exceptions.TelegramUnauthorizedError: Telegram server says - Unauthorized. 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 174 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. Most of that is aiogram itself: in our earlier hosting test, aiogram alone measured 173 MB (that bot stopped at a refused token, before handling any message, so the two numbers are close but not an exact comparison). Installing aiogram and aiomysql with pip from nothing peaked at 163 MB, file cache included. That leaves room for your own code on the smallest plan. The hosting guide compares the libraries: 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.
