这个更新查询为什么在我通过Python运行时不起作用?

huangapple go评论83阅读模式
英文:

Why doesn't this update query work when I run it through Python?

问题

UPDATE: 大家不用担心,问题已解决。原来你需要在每次操作后使用 connection.commit() 提交你的操作。感谢我的朋友 user56700。

我对编程还很陌生,我使用Python和MySQL创建了一个能够创建、读取、更新和删除表格的程序。为简单起见,更新表格只会添加预定义的元素。以下代码负责这个过程:

  1. mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);"""
  2. # 数据库 = valuesBase
  3. if userRequestUpdate == 1:
  4. cursor
  5. result4 = cursor.execute(mySql_Update_CV_Query)
  6. cursor.close()
  7. print("CVCode 更新成功。")
  8. mySql_Update_Values_Query = """UPDATE valuesTable
  9. SET factoryValue = 3, customValue = 0
  10. WHERE CVCode = 1;"""
  11. elif userRequestUpdate == 2:
  12. cursor
  13. result4 = cursor.execute(mySql_Update_Values_Query)
  14. cursor.close()
  15. print("userValue 更新成功。")

我在MySQL Workbench中运行了完全相同的查询,它们运行得很好,但是当我尝试以这种方式运行它们时,尽管打印出了"成功"的语句,但表格实际上并没有被更新。

英文:

UPDATE: Nevermind folks, I got it working. Turns out you need to commit your actions using connection.commit() each time. Big up my mate user56700.

I'm quite new to coding, I made a program using Python and MySQL that is able to create, read, update and delete tables. For simplicity's sake, updating the table only adds pre-specified elements. The following code is responsible for the process:

  1. mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);"""
  2. #database = valuesBase
  3. if userRequestUpdate == 1:
  4. cursor
  5. result4 = cursor.execute(mySql_Update_CV_Query)
  6. cursor.close()
  7. print("CVCode updated successfully.")
  8. mySql_Update_Values_Query = """UPDATE valuesTable
  9. SET factoryValue = 3, customValue = 0
  10. WHERE CVCode = 1;"""
  11. elif userRequestUpdate == 2:
  12. cursor
  13. result4 = cursor.execute(mySql_Update_Values_Query)
  14. cursor.close()
  15. print("userValue updated successfully.")

I ran the exact same queries in MySQL Workbench and they ran just fine, but when I attempted to run them this way, the table wasn't actually getting updated despite the "successful" statement printing.

答案1

得分: 1

make sure your commit happens before you close the cursor and, of course, don't forget to manage the rollback as well if an error triggers

  1. mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);"""
  2. #database = valuesBase
  3. if userRequestUpdate == 1:
  4. cursor
  5. try:
  6. result4 = cursor.execute(mySql_Update_CV_Query)
  7. cursor.commit() # <--- this
  8. print("CVCode updated successfully.")
  9. except Exception as e:
  10. print(f"Error triggered: {e}")
  11. cursor.rollback() # <--- if something goes wrong
  12. cursor.close()
  13. mySql_Update_Values_Query = """UPDATE valuesTable
  14. SET factoryValue = 3, customValue = 0
  15. WHERE CVCode = 1;"""
  16. elif userRequestUpdate == 2:
  17. cursor
  18. try:
  19. result4 = cursor.execute(mySql_Update_Values_Query)
  20. cursor.commit() # <--- this
  21. print("userValue updated successfully.")
  22. except Exception as e:
  23. print(f"Error triggered: {e}")
  24. cursor.rollback() # <--- if something goes wrong
  25. cursor.close()
英文:

I am glad you made your code work, I'm writing this so other users with your problem can have an example on how to use this:

make sure your commit happens before you close the cursor and, of course, don't forget to manage the rollback as well if an error triggers

  1. mySql_Update_CV_Query = """INSERT INTO valuesTable (CVCode) VALUE (1);"""
  2. #database = valuesBase
  3. if userRequestUpdate == 1:
  4. cursor
  5. try:
  6. result4 = cursor.execute(mySql_Update_CV_Query)
  7. cursor.commit() # <--- this
  8. print("CVCode updated successfully.")
  9. except Exception as e:
  10. print(f"Error triggered: {e}")
  11. cursor.rollback() # <--- if something goes wrong
  12. cursor.close()
  13. mySql_Update_Values_Query = """UPDATE valuesTable
  14. SET factoryValue = 3, customValue = 0
  15. WHERE CVCode = 1;"""
  16. elif userRequestUpdate == 2:
  17. cursor
  18. try:
  19. result4 = cursor.execute(mySql_Update_Values_Query)
  20. cursor.commit() # <--- this
  21. print("userValue updated successfully.")
  22. except Exception as e:
  23. print(f"Error triggered: {e}")
  24. cursor.rollback() # <--- if something goes wrong
  25. cursor.close()
  26. </details>

huangapple
  • 本文由 发表于 2023年4月4日 13:49:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/75925876.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定