PostgreSQL环境下TeleBot无法自动建表且重复触发/start报错

问题1：Bot无法自动创建数据表 #

你的代码缺少数据表初始化逻辑，PostgreSQL不会自动生成users表，需要添加建表逻辑，在Bot启动时检查并创建表（如果不存在）。

添加初始化表的函数：

def init_db():
    conn = connect_to_db()
    cursor = conn.cursor()
    # 创建users表，将user_id设为主键确保唯一性
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        user_id BIGINT PRIMARY KEY,
        join_date TIMESTAMP,
        user_info TEXT
    )
    """
    cursor.execute(create_table_query)
    conn.commit()
    cursor.close()
    conn.close()

然后在Bot启动前调用该函数：

if __name__ == "__main__":
    init_db()
    bot.polling()

问题2：重复点击/start导致插入失败 #

user_id是用户唯一标识，重复插入会触发主键冲突。使用PostgreSQL的INSERT ... ON CONFLICT语法，冲突时可选择忽略或更新用户信息。

修改insert_user_data函数的SQL语句：

def insert_user_data(user_id, join_date, user_info):
    conn = connect_to_db()
    cursor = conn.cursor()
    # 冲突时更新用户的加入时间和信息，也可替换为DO NOTHING直接忽略重复插入
    query = """
    INSERT INTO users (user_id, join_date, user_info) 
    VALUES (%s, %s, %s)
    ON CONFLICT (user_id) 
    DO UPDATE SET 
        join_date = EXCLUDED.join_date,
        user_info = EXCLUDED.user_info
    """
    cursor.execute(query, (user_id, join_date, user_info))
    conn.commit()
    cursor.close()
    conn.close()

完整修改后的代码 #

import psycopg2
from telebot import TeleBot, Message

bot = TeleBot("590----86:AAF9_DU9F_6----rvls26HgJMzHyJJpY")

def connect_to_db():
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    return conn

def init_db():
    conn = connect_to_db()
    cursor = conn.cursor()
    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
        user_id BIGINT PRIMARY KEY,
        join_date TIMESTAMP,
        user_info TEXT
    )
    """
    cursor.execute(create_table_query)
    conn.commit()
    cursor.close()
    conn.close()

def insert_user_data(user_id, join_date, user_info):
    conn = connect_to_db()
    cursor = conn.cursor()
    query = """
    INSERT INTO users (user_id, join_date, user_info) 
    VALUES (%s, %s, %s)
    ON CONFLICT (user_id) 
    DO UPDATE SET 
        join_date = EXCLUDED.join_date,
        user_info = EXCLUDED.user_info
    """
    cursor.execute(query, (user_id, join_date, user_info))
    conn.commit()
    cursor.close()
    conn.close()

@bot.message_handler(commands=['start'])
def handle_start(message: Message):
    user_id = message.from_user.id
    join_date = message.date
    user_info = f"{message.from_user.first_name} {message.from_user.last_name}"
    insert_user_data(user_id, join_date, user_info)
    bot.reply_to(message, "User information has been stored in the database.")

if __name__ == "__main__":
    init_db()
    bot.polling()

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