python Mysql SELECT数据未刷新

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 的不同笔记本上遇到了同样的 MySQL 问题。我看到了一些明显的情况，即我从一个笔记本中查询的结果没有看到其他笔记本发布的数据。

这是一个非常简单的解决方案，但我发现它运行良好。

只需设置一个新连接，然后处理 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

这对我的编码很有效，并且完全解决了这个问题。