即使在我执行 commit() 和 autocommit=True 之后，也无法使用 psycopg2 脚本更新 PostgreSQL 表

Question

我正在尝试使用psycopg2游标更新在ElephantSQL服务上运行的PostgreSQL表。从我的电脑(MacOS)执行SQL语句。脚本认为一切都很好，但表没有更新。我指定了"connection.autocommit= true"还包括connection.commit()"语句紧接在cursor.execute(SQL)语句之后。没有什么工作。非常感谢你的帮助。

下面是一个代码片段:

# 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)

如前所述，脚本报告用户已成功删除，但事实并非如此。

就像我说的，我整天都在抓耳挠腮。希望有人能给我指出正确的方向。

我已经尝试将自动提交属性添加到连接中，并且我还在事务之后添加了connection.commit()语句。不知怎么的，这对我不起作用。非常感谢您的帮助。

Answer 1

问题的答案与DB commit()或autocommit=True无关。

正如注释所指出的，我传递了错误的参数。

光标。Execute语句不正确，没有传递元组作为语句的第二部分。

try:
with connection, connection.cursor() as cursor:
cursor.execute("DELETE FROM users WHERE id=%s;",(id_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)

所以语句应该是:cursor.execute("SELECT id FROM users WHERE username=%s;"， (username，))。最后一部分:(username，)之前没有做正确。感谢Richard Hutton和Adrian Klaver。你的意见帮助我度过了难关。