python Mysql选择数据未刷新

Question

非商业，非专业用户，为业余项目寻求建议。

我试着从一个Pi中提取数据，然后在另一个Pi上滚动。在

但是，终端显示的是最初加载的数据的重复，而不是我在数据库中看到的新数据。 （供参考…数据库每十分钟自动更新一次。对于这样的测试，我正在运行一个手动更新脚本，以强制将新的读数输入数据库。）

#!/usr/bin/env python

import signal
import time
import scrollphathd
from scrollphathd.fonts import font5x7
import mysql.connector
from mysql.connector import Error
con = mysql.connector.connect(host='192.168.###############')

str_len = 0
scroll_x = 0
timer = 2 # number of mins for display loop

### Create cursorS for each database element:
### Keep in buffer with =True
while True:
    curT = con.cursor(buffered=True)
    curY = con.cursor(buffered=True)
    curL = con.cursor(buffered=True)

### Use each cursor to read required data from database:
    curT.execute('SELECT Temp FROM readings ORDER BY Added DESC LIMIT 1')
    curY.execute('SELECT Yaxis FROM readings ORDER BY Added DESC LIMIT 1')
    curL.execute('SELECT Lux FROM readings ORDER BY Added DESC LIMIT 1') 

### Get rid of trailing comma from each SELECT result: 
    resultT = [row[0] for row in curT.fetchall()]
    resultY = [row[0] for row in curY.fetchall()]
    resultL = [row[0] for row in curL.fetchall()]



### Not essential, but let's show the 
### result of each SELECT query in the terminal:
    print resultT
    print resultY
    print resultL

    # set loop time in seconds
    start = time.time()
    end = start + ( timer + 60 )


### Set strings for display on Scroll PhatHD from SELECT results: 
    while time.time() < end:
            temperature = resultT[0]
            yaxis = resultY[0]
            lux = resultL[0]


### Dim down Scroll Phat HD, and clear its buffer:            
            scrollphathd.set_brightness(0.1)
            scrollphathd.clear()


### Uncomment/comment the below line to rotate Scroll PhatHD by 180/upside down
            scrollphathd.rotate(degrees=180)


### Uncomment line below to test all data on Scroll PhatHD in one go.
### str_len = scrollphathd.write_string(" :-) %.1fC Y%i L%i "%(temperature, yaxis, lux), brightness=0.5)


### Check light levels and door angle (Yaxis) and report appripriately. Always show the temperature:
            if lux <= 100 and yaxis >=3500 :
                str_len = scrollphathd.write_string("Garage: light off & door closed.  %.1fC Y%i "%(temperature, yaxis), x=0, y=0, font=font5x7)
            elif lux <= 100 and yaxis <500:
                str_len = scrollphathd.write_string("Garage: Light off & door open.  %.1fC "%(temperature), x=0, y=0, font=font5x7)        
            elif lux > 100 and yaxis <500:
                str_len = scrollphathd.write_string("Garage: Light on & door open.  %.1fC "%(temperature), x=0, y=0, font=font5x7)
            elif lux > 100 and yaxis >=3500:
                str_len = scrollphathd.write_string("Garage: Light on & door closed.  %.1fC "%(temperature), x=0, y=0, font=font5x7)
            elif yaxis >500 and yaxis <3499:
                str_len = scrollphathd.write_string("Garage door ajar %.1fC  "%(temperature), x=0, y=0, font=font5x7)


            scrollphathd.scroll_to(scroll_x, 0)
            scrollphathd.show()
            time.sleep(0.01)
            scroll_x += 1
            if scroll_x >= str_len:
                scroll_x = 0

我需要做些什么来使显示显示数据库中的新数据，而不是重复显示旧的过时数据？在

谢谢。在

编辑： 我想知道它是否在游标缓冲区中，是否需要在每个SELECT循环之间进行刷新？在

Answer 1

我在运行Python的不同笔记本上遇到了同样的问题。我看到了一些明显的例子，我从一个笔记本上查询到的数据没有被其他笔记本记录下来。在

这是一个非常简单的解决方案，但我发现它很有效。在

只需建立一个新的连接，然后处理SQL，最后关闭连接。Python中的代码如下所示：

import mysql.connector

def do_SQL_Stuff(rds_Dict):

   #Get RDS connection
   cnx_GDAX = mysql.connector.connect(
      host = rds_Dict['host'],
      user = rds_Dict['user'], 
      password = rds_Dict['password'],
      database = rds_Dict['database'])

   #Do SQL stuff

   #Close RDS connection
   cnx_GDAX.close()

   return r_Stuff

这对我的编码很好，完全消除了这个问题。在