基于aiogram实现Telegram Bot数据库数据5行分页美化展示

需求说明 #

需要从MySQL数据库获取全部商品数据，以每页5条的形式展示，每条数据的product_name、discount_price、regular_price字段用加粗格式美化，同时配备分页切换的InlineKeyboard。原有代码仅支持单条展示，需调整实现需求。

修改后的完整代码 #

from aiogram import Bot, Dispatcher, executor, types
import mysql.connector as connector
from config import TOKEN_API

bot = Bot(TOKEN_API)
dp = Dispatcher(bot)

# 数据库连接
conn = connector.connect(
    database='awesugn_products',
    host='localhost',
    username='root',
    password='root'
)
cursor = conn.cursor()

# 每页展示条数
PER_PAGE = 5

def get_all_products():
    """获取所有商品数据，封装成字典列表"""
    cursor.execute("SELECT product_name, discount_price, regular_price FROM discount_products")
    objs = cursor.fetchall()
    return [
        {
            "product_name": obj[0],
            "discount_price": obj[1],
            "regular_price": obj[2]
        } for obj in objs
    ]

def construct_pagination_keyboard(total_pages: int, current_page: int) -> types.InlineKeyboardMarkup:
    """构建分页键盘"""
    kb = types.InlineKeyboardMarkup(row_width=3)
    buttons = []
    
    # 上一页按钮
    if current_page > 1:
        buttons.append(types.InlineKeyboardButton(text='<-', callback_data=f'page_{current_page-1}'))
    else:
        buttons.append(types.InlineKeyboardButton(text=' ', callback_data='none'))
    
    # 当前页码/总页数
    buttons.append(types.InlineKeyboardButton(text=f'{current_page}/{total_pages}', callback_data='none'))
    
    # 下一页按钮
    if current_page < total_pages:
        buttons.append(types.InlineKeyboardButton(text='->', callback_data=f'page_{current_page+1}'))
    else:
        buttons.append(types.InlineKeyboardButton(text=' ', callback_data='none'))
    
    kb.add(*buttons)
    return kb

def format_products_page(products: list) -> str:
    """格式化单页商品为Markdown格式"""
    page_text = []
    for product in products:
        product_str = (
            f"**product_name**: {product['product_name']}\n"
            f"**discount_price**: {product['discount_price']}\n"
            f"**regular_price**: {product['regular_price']}\n\n"
        )
        page_text.append(product_str)
    return ''.join(page_text).strip()

@dp.message_handler(commands='start')
async def start(message: types.Message):
    all_products = get_all_products()
    total_pages = (len(all_products) + PER_PAGE - 1) // PER_PAGE  # 计算总页数
    current_page = 1
    # 取当前页的商品
    start_idx = (current_page - 1) * PER_PAGE
    end_idx = start_idx + PER_PAGE
    page_products = all_products[start_idx:end_idx]
    
    page_text = format_products_page(page_products)
    await message.answer(page_text, parse_mode='Markdown', reply_markup=construct_pagination_keyboard(total_pages, current_page))

@dp.callback_query_handler(text_startswith='page_')
async def handle_pagination(call: types.CallbackQuery):
    current_page = int(call.data.split('_')[1])
    all_products = get_all_products()
    total_pages = (len(all_products) + PER_PAGE - 1) // PER_PAGE
    
    # 边界处理，防止页码超出范围
    if current_page < 1:
        current_page = 1
    elif current_page > total_pages:
        current_page = total_pages
    
    # 取当前页商品
    start_idx = (current_page - 1) * PER_PAGE
    end_idx = start_idx + PER_PAGE
    page_products = all_products[start_idx:end_idx]
    
    page_text = format_products_page(page_products)
    # 编辑原消息，避免重复发送
    await call.message.edit_text(page_text, parse_mode='Markdown', reply_markup=construct_pagination_keyboard(total_pages, current_page))
    await call.answer()  # 回调确认，避免Telegram提示加载中

if __name__ == "__main__":
    executor.start_polling(dp, skip_updates=True)

关键改动说明 #

1. 分页逻辑实现：

   • 定义PER_PAGE = 5控制每页条数，通过(总条数 + PER_PAGE -1) // PER_PAGE计算总页数
   • 按(当前页-1)*PER_PAGE到当前页*PER_PAGE的切片获取当前页商品数据

2. 消息格式美化：

   • 新增format_products_page函数，将商品字段用**包裹实现Telegram Markdown加粗效果，每条商品间换行分隔

3. 分页键盘修复：

   • 原键盘错误使用商品总条数作为总页数，现在改为计算后的总页数
   • 优化按钮状态，当处于第一页/最后一页时，对应按钮替换为占位符避免无效操作

4. 代码优化：

   • 封装get_all_products函数统一获取商品数据，避免重复执行SQL查询
   • 分页回调时使用edit_text编辑原消息，避免刷屏，提升用户体验
   • 增加页码边界校验，防止非法页码导致错误

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