使用psycopg2操作ElephantSQL的PostgreSQL表删除数据不生效求助

在MacOS系统上，通过psycopg2的cursor.execute操作ElephantSQL中的PostgreSQL表时，执行删除数据操作后脚本提示成功，但实际表数据并未更新。已设置connection.autocommit=True，且在cursor.execute后添加了connection.commit()语句，问题仍未解决。

相关代码片段：

# Establish Connection
try:
    connection = psycopg2.connect(conn_string)
    connection.autocommit=True
except Exception as e:
    print("******Cannot connect to DB****** \n" + "error: " + str(e))
    exit()

# ---   Later on,  when trying to update the DB

if request.method == "POST":
        if form.validate_on_submit():
            username_to_delete = request.form['username']
            print(" username to delete is: " + username_to_delete)
            try:
                with connection, connection.cursor() as cursor:
                        cursor.execute("DELETE FROM users WHERE username='username_to_delete';")
                        connection.commit()
                        cursor.close()
            except Exception as e:
                flash("Delete operation failed, error: " + str(e)) 
                print("Delete operation failed, try again ..." + str(e))
                return render_template("delete_user.html", form=form)
            flash("User Deleted Successfully!!")
            username_to_delete = ""
            return render_template("delete_user.html", form=form)
    
        else:
            print(" Error with Delete Form validation")
            flash(" There was a problem deleting user, try again...")
        return render_template("delete_user.html", form=form)

    else:
        flash(" User Delete failed! -  form problems")  
        return render_template("delete_user.html", form=form)

• 修复SQL变量传递错误：当前DELETE语句中username='username_to_delete'是将字符串字面量作为查询条件，并未使用变量username_to_delete的实际值，导致没有匹配到目标用户。必须使用psycopg2的参数化查询写法：

  cursor.execute("DELETE FROM users WHERE username = %s;", (username_to_delete,))
  

  注意占位符用%s，参数以元组形式传入，同时避免SQL注入风险。

• 避免autocommit与显式commit混用：已设置connection.autocommit=True后，无需再调用connection.commit()，两者混用可能导致事务行为异常。选择一种方式即可：要么保持autocommit开启自动提交，要么关闭autocommit后手动执行commit。

• 优化with语句用法：with connection, connection.cursor() as cursor会在with块结束时自动关闭连接，这可能导致后续操作失效。建议只通过with管理游标：

  with connection.cursor() as cursor:
      cursor.execute("DELETE FROM users WHERE username = %s;", (username_to_delete,))
  

• 验证查询影响行数：执行DELETE后通过cursor.rowcount查看实际删除的行数，如果返回0说明查询条件未匹配到任何用户，需检查用户名是否大小写一致（PostgreSQL默认区分大小写）、输入的用户名是否与数据库中记录完全一致：

  cursor.execute("DELETE FROM users WHERE username = %s;", (username_to_delete,))
  print(f"实际删除行数: {cursor.rowcount}")
  

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