在Python PostgreSQL插入函数中添加增量usage列，跟踪Telegram Bot命令使用次数

Got it, let's tackle this problem step by step. You want to add a usage column to track how many times each command is used—either per user or globally—with logic that increments the count instead of inserting a new duplicate row. Here's how to implement this properly using PostgreSQL's UPSERT feature and adjust your Python function:

1. Update Your Database Table #

First, you need to add the usage column and create a unique constraint to identify when a command entry already exists. Run these SQL commands in your PostgreSQL console:

-- Add the usage column with a default value of 1 for new entries
ALTER TABLE "Logs" ADD COLUMN usage INTEGER DEFAULT 1;

-- Option 1: Track usage per user per command (most common use case)
ALTER TABLE "Logs" ADD CONSTRAINT unique_user_command UNIQUE (user_id, telegram_nickname, command);

-- Option 2: Track global command usage (count all users' uses of a command together)
-- ALTER TABLE "Logs" ADD CONSTRAINT unique_command UNIQUE (command);

2. Modify the Python Function #

Replace your existing SQL query with PostgreSQL's INSERT ... ON CONFLICT syntax (UPSERT) to handle both insertion and increment logic in one query. We'll also add a rollback in the exception block to avoid leaving the database connection in a broken state:

def logs(user_logs):
    try:
        conn = psycopg2.connect(DB_CONNECTION_STRING)
        cursor = conn.cursor()
        # UPSERT logic: Insert new row if no conflict, increment usage if there is
        sql_query = """
            INSERT INTO "Logs" (user_id, telegram_nickname, command, usage)
            VALUES (%s, %s, %s, 1)
            -- Match the unique constraint you created earlier
            ON CONFLICT (user_id, telegram_nickname, command)
            DO UPDATE SET usage = "Logs".usage + 1;
        """
        cursor.execute(sql_query, user_logs)
        conn.commit()
        return None
    except Exception as e:
        # Rollback the transaction if any error occurs
        if conn:
            conn.rollback()
        return e

3. How It Works #

• First-time command use: The query inserts a new row with usage = 1 (leveraging the column's default value if you prefer not to pass it explicitly).
• Repeat command use: When the unique constraint detects a duplicate entry (same user + same command, or same command globally if you chose that constraint), it updates the existing row's usage value by adding 1.
• Your original user_logs tuple (containing user_id, telegram_nickname, command) works exactly as before—no need to modify the data you pass to the function.

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