使用Python脚本无法更新PostgreSQL数据库的问题求助

问题详情 #

尝试通过Python脚本更新PostgreSQL数据库，将已过期记录的calendar_event_live字段设为false。在pgAdmin查询工具中运行相同逻辑的查询可正常生效，但执行脚本时无报错，且提示“14 Records effected in Calendar table”，但数据库实际未更新。

原代码 #

def connect_db(self):
    event_over = datetime.now()

try:
    # Creating a cursor object using the cursor() method
    cursor = self.connection().cursor()
    postgres_insert_query = """UPDATE dashboard_calendarevent SET calendar_event_live=false WHERE 
    calendar_dates_end <= (%s); """

    cursor.execute(postgres_insert_query, (event_over,))
    self.connection().commit()

    count = cursor.rowcount
    print(count, "Records effected in Calendar table")

except (Exception, psycopg2.Error) as error:
    print("Failed to update record into Calendar table because ", error)

finally:
    # closing database connection.
    if self.connection():
        cursor.close()
        self.connection().close()
        print("PostgreSQL connection is closed")

执行信息 #

• 生成的查询语句：

  UPDATE dashboard_calendarevent SET calendar_event_live=false WHERE calendar_dates_end <= '2022-08-22 16:05:05.426078';
  

• 脚本返回：

  14 Records effected in Calendar table
  PostgreSQL connection is closed
  

• 数据库时间示例：2022-08-19 15:30:00+00，脚本生成时间：2022-08-22 16:05:05.426078，已确认连接正确数据库，无错误日志。

问题原因 #

核心问题是重复调用self.connection()导致使用多个独立数据库连接：

• 游标在连接A上创建，执行UPDATE后事务仅存在于连接A中
• 调用self.connection().commit()时，实际是在新创建的连接B上执行提交，连接A的事务并未被提交
• 最后关闭的是连接C，连接A的事务会在其自动关闭时回滚，因此数据库无更新

解决方案 #

复用同一个数据库连接实例，避免重复调用self.connection()：

修改后的代码 #

def connect_db(self):
    event_over = datetime.now()

try:
    # 只创建一次连接并复用
    conn = self.connection()
    cursor = conn.cursor()
    postgres_insert_query = """UPDATE dashboard_calendarevent SET calendar_event_live=false WHERE 
    calendar_dates_end <= (%s); """

    cursor.execute(postgres_insert_query, (event_over,))
    conn.commit()  # 用同一个连接提交事务

    count = cursor.rowcount
    print(count, "Records effected in Calendar table")

except (Exception, psycopg2.Error) as error:
    print("Failed to update record into Calendar table because ", error)
    # 发生错误时回滚事务
    if 'conn' in locals():
        conn.rollback()

finally:
    # 关闭同一个连接和游标
    if 'cursor' in locals() and not cursor.closed:
        cursor.close()
    if 'conn' in locals() and conn:
        conn.close()
        print("PostgreSQL connection is closed")

额外注意事项 #

数据库中calendar_dates_end是带UTC时区的时间（如2022-08-19 15:30:00+00），而datetime.now()生成的是本地时间（无时区信息），可能导致时间比较逻辑不符合预期。建议改用UTC时间：

from datetime import datetime, timezone
event_over = datetime.now(timezone.utc)

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