Python数据库请求不更新

Question

我正在从python脚本中从数据库中获取数据，该脚本可用于启动，但在更改数据库中的值时不会更新。

脚本最初连接到数据库，然后选择ID等于1的所有内容(这有许多我打算使用的列(

然后，它使用车库列中的内容更新全局变量车库，以便在启动时等于0。

while循环它们打印出此变量，该变量在开始时为0

如果我将数据库中的列车库更改为10，则Python脚本保持在0。它不会更新我认为应该执行的全局变量

我的脚本在下面

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="*******",
  database="mydb"
  )
cursor = mariadb_connection.cursor()
garageHeating = 0 
def readDbHeating():
   global garageHeating
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
   except () as e:
      print (e)
while 1:
   readDbHeating()
   print garageHeating

我已经编辑了下面所示的代码，以显示函数之外的全局变量，并在try命令中打印出结果。在数据库的第一个数据库中，脚本不断显示第一个集数据，但如果我更改数据库记录，我仍然会遇到相同的问题。

。 。

import mysql.connector as mariadb
mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="14Odiham",
  database="mydb"
  )
cursor = mariadb_connection.cursor()
global garageHeating
def readDbHeating():
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
         print garageHeating
         print result
   except () as e:
      print (e)
while 1:
    readDbHeating()
    #print garageHeating

Answer 1

您必须在执行查询之前调用MySQLdb.connections.Connection.commit或在连接对象上设置autocommit。

在查询之前提交

def readDbHeating():
   global garageHeating
   result = []
   try:
      # commit
      mariadb_connection.commit()
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
         garageHeating = result[0][8]
   except () as e:
      print (e)

创建连接时自动加入

mariadb_connection = mariadb.connect(
  host="localhost",
  user="garage",
  passwd="14Odiham",
  database="mydb",
  # Auto commit
  autocommit=True
  )

自动加入连接创建后

mariadb_connection.autocommit(True)

Answer 2

我似乎使它起作用。我必须打开数据库连接，然后关闭函数中的连接，如下所示。这与我过去对MySQL所做的事情有所不同，但目前至少现在起作用

import mysql.connector as mariadb
import time
garageHeating = 0
def readDbHeating():
   mariadb_connection = mariadb.connect(
     host="localhost",
     user="garage",
     passwd="14Odiham",
     database="mydb"
     )
   cursor = mariadb_connection.cursor()
   result = []
   try:
      cursor.execute ("SELECT * FROM heating WHERE id = '1'")
      for reading in cursor.fetchall():
         result.append (reading)
   except () as e:
      print (e)
   finally:
    #closing database connection.
    if(mariadb_connection.is_connected()):
        mariadb_connection.close()
        print("connection is closed")
   return (result)

while 1:
   test = readDbHeating()
   print test
   time.sleep(1)