我正在将python脚本转换为使用mysql而不是sqlite3，并且mysql语法错误有很多问题，这些问题确实让我感到困惑。我对数据库没有太多经验。似乎这些行会在短时间内工作，然后它们会抛出错误。
这是给我第一个错误的行。我认为一旦我掌握了正确的语法，我也可以更改所有其他语法。
elif 'Sensors' in line:
Sensors,pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,TankTotal,Tank1,Tank2,Tank3,Tank4,WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4=line.split(",")
Sensors = Sensors.replace("Read fail", "")
WaterTempP4 = WaterTempP4.rstrip()
elapsedTime = now-startTime
elapsedSeconds = (elapsedTime.microseconds+(elapsedTime.days*24*3600+elapsedTime.seconds)*10**6)/10**6
print(" 33[10;0Hr")
print(" 33[10;0H(" + now.strftime("%Y/%m/%d %H:%M:%S") + ") Sensors: %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s"%(pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,TankTotal,Tank1,Tank2,Tank3,Tank4,WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4))
now = datetime.now()
delta = float(now.strftime('%s')) - float(LastDataPoint_Time.strftime('%s'))
if (delta < 0):
TimeString = LastDataPoint_Time.strftime("%Y-%m-%d %H:%M:%S")
update_sql("DELETE FROM Sensors_Log WHERE Time='" + TimeString + "'")
LastDataPoint_Time = datetime.now()
addMessageLog("Negative Delta - Deleting Last Record (Wrong Time?)")
printMessageLog()
if (delta >= TakeDataPoint_Every) or (Datapoint_count == 0 and first_timesync == True):
addMessageLog("Added a data point to the sensor values log.")
printMessageLog()
update_sql("INSERT INTO Sensors_Log (Time,pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,TankTotal,Tank1,Tank2,Tank3,Tank4,WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4) VALUES ('" + now.strftime("%Y-%m-%d %H:%M:%S") + "'," + pH1 + "," + pH2+ "," + Temp + "," + RH + "," + TDS1 + "," + TDS2 + "," + CO2 + "," + Light + "," + Water + "," + MagX + "," + MagY + "," + MagZ + "," + TankTotal + "," + Tank1 + "," + Tank2 + "," + Tank3 + "," + Tank4 + "," + WaterTempP1 + "," + WaterTempP2 + "," + WaterTempP3 + "," + WaterTempP4 + ")")
LastDataPoint_Time = datetime.now()
timesync = 0 #do a timesync
Datapoint_count = Datapoint_count + 1
#SENSOR VALUES
update_sql("UPDATE `Sensors` SET pH1 = " + pH1 + ", pH2 = " + pH2 + ", Temp = " + Temp + ", RH = " + RH + ", TDS1 = " + TDS1 + ", TDS2 = " + TDS2 + ", CO2 = " + CO2 + ", Light = " + Light + ", Water = " + Water + ", MagX = " + MagX + ", MagY = " + MagY + ", MagZ = " + MagZ + ", TankTotal = " + TankTotal + ", Tank1 = " + Tank1 + ", Tank2 = " + Tank2 + ", Tank3 = " + Tank3 + ", Tank4 = " + Tank4 + ", WaterTempP1 = " + WaterTempP1 + ", WaterTempP2 = " + WaterTempP2 + ", WaterTempP3 = " + WaterTempP3 + ", WaterTempP4 = " + WaterTempP4 + "")
db.commit()
它的update_sql("底部的 UPDATE 字符串会抛出 1064 错误，任何帮助让我使用格式走上正确轨道的帮助将不胜感激。当串行字符串中带有"传感器"时，上面的代码将运行。后面是用逗号分隔的传感器读数。插入但不更新时，代码似乎工作正常。
提前致谢
这是完整的错误代码
Traceback (most recent call last):
File "yieldbuddy.py", line 1211, in <module>
serialerr=checkSerial()
File "yieldbuddy.py", line 481, in checkSerial
update_sql("UPDATE `Sensors` SET pH1 = " + pH1 + ", pH2 = " + pH2 + ", Temp = " + Temp + ", RH = " + RH + ", TDS1 = " + TDS1 + ", TDS2 = " + TDS2 + ", CO2 = " + CO2 + ", Light = " + Light + ", Water = " + Water + ", MagX = " + MagX + ", MagY = " + MagY + ", MagZ = " + MagZ + ", TankTotal = " + TankTotal + ", Tank1 = " + Tank1 + ", Tank2 = " + Tank2 + ", Tank3 = " + Tank3 + ", Tank4 = " + Tank4 + ", WaterTempP1 = " + WaterTempP1 + ", WaterTempP2 = " + WaterTempP2 + ", WaterTempP3 = " + WaterTempP3 + ", WaterTempP4 = " + WaterTempP4 + "")
File "yieldbuddy.py", line 33, in update_sql
cursor.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")
我一直在使用下面的第三个示例来设置 sql update 语句，这似乎在工作，直到我到达以下代码
elif 'SetPoint_pH2' in line:
if oldSetPoint_pH2 != line:
oldSetPoint_pH2 = line
#print("%s"%(line)) For Debugging
SetPoint_pH2,pH2Value_Low,pH2Value_High,pH2_Status=line.split(",")
SetPoint_pH2 = SetPoint_pH2.replace("Read fail", "")
pH2_Status = pH2_Status.rstrip()
print(" 33[16;0H ")
print(" 33[16;0H(" + now.strftime("%Y/%m/%d %H:%M:%S") + ") SetPoint_pH2: %s,%s,%s"%(pH2Value_Low,pH2Value_High,pH2_Status))
#SetPoint_pH
# update_sql("UPDATE `pH2` SET Low='" + pH2Value_Low + "',High='" + pH2Value_High + "',Status='" + pH2_Status + "'")
update_sql("UPDATE `pH2` SET Low = {}, High = {}, Status = {}".format(pH2Value_Low,pH2Value_High,pH2_Status))
由于某种原因，它不会将状态字段识别为数据库中的状态列，而是抛出此错误。
(2016/08/07 12:10:42) SetPoint_pH2: -1.00,6.20,OK
Traceback (most recent call last):
File "yieldbuddy.py", line 1310, in <module>
serialerr=checkSerial()
File "yieldbuddy.py", line 607, in checkSerial
update_sql("UPDATE `pH2` SET Low = {}, High = {}, Status = {}".format(pH2Value_Low,pH2Value_High,pH2_Status))
File "yieldbuddy.py", line 33, in update_sql
cursor.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'OK' in 'field list'")
我似乎看不到这一行与我已经完成的其他行之间的区别，它尝试使用"ok"列，这实际上是我想放入实际列"状态"的值。
从语法上讲，根据 SET 子句以及等号和逗号的位置，您的 UPDATE SQL 语句是正确的。最有可能发生的事情是您的 21 个变量中的一个或多个返回一个零长度字符串 ( '' (，MySQL 无法将其设置为数字列。考虑有条件地将此类零长度字符串转换为在 MySQL 中转换为 NULL 的 None 。
例如，下面显示了v5的空值
line = "this,is,a,test,"
v1, v2, v3, v4, v5 = line.split(",")
print(v5=='')
# TRUE
若要解决此问题，请有条件地为此类零长度字符串生成None：
v1, v2, v3, v4, v5 = [None if i == '' else i for i in [v1, v2, v3, v4, v5]]
print(v5==None) # ALTERNATIVELY: print(v5 is None)
# TRUE
因此，在初始化变量时，请考虑顶部的以下调整：
...
Sensors,pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,TankTotal,
Tank1,Tank2,Tank3,Tank4,WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4 = line.split(",")
Sensors,pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,TankTotal,
Tank1,Tank2,Tank3,Tank4,WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4 =
[None if i == '' else i for i in [Sensors,pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,
MagX,MagY,MagZ,TankTotal,Tank1,Tank2,Tank3,Tank4,
WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4]]
每个变量包装在str()中，因为NoneType对象需要显式转换字符串，而不是用串联隐式转换。下面的更新会将MySQL列设置为在Python的每个实例None NULL：
update_sql("UPDATE `Sensors` SET pH1 = " + str(pH1) + ", " +
"pH2 = " + str(pH2) + ", Temp = " + str(Temp) + ", RH = " + str(RH) + ", " +
"TDS1 = " + str(TDS1) + ", TDS2 = " + str(TDS2) + ", CO2 = " + str(CO2) + ", " +
"Light = " + str(Light) + ", Water = " + str(Water) + ", MagX = " + str(MagX) + ", " +
"MagY = " + str(MagY) + ", MagZ = " + str(MagZ) + ", TankTotal = " + str(TankTotal) + ", " +
"Tank1 = " + str(Tank1) + ", Tank2 = " + str(Tank2) + ", Tank3 = " + str(Tank3) + ", " +
"Tank4 = " + str(Tank4) + ", WaterTempP1 = " + str(WaterTempP1) + ", " +
"WaterTempP2 = " + str(WaterTempP2) + ", WaterTempP3 = " + str(WaterTempP3) + ", " +
"WaterTempP4 = " + str(WaterTempP4) + "")
或者，考虑字符串格式，它也适应None：
update_sql("UPDATE `Sensors` SET pH1 = {},
pH2 = {}, Temp = {}, RH = {},
TDS1 = {}, TDS2 = {}, CO2 = {},
Light = {}, Water = {}, MagX = {},
MagY = {}, MagZ = {}, TankTotal = {},
Tank1 = {}, Tank2 = {}, Tank3 = {},
Tank4 = {}, WaterTempP1 = {},
WaterTempP2 = {}, WaterTempP3 = {},
WaterTempP4 = {} ".format(pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,
MagX,MagY,MagZ,TankTotal,Tank1,Tank2,Tank3,Tank4,
WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4))
更好的是，在 cursor.execute(( 方法中将更新作为参数化查询运行，如果源数据来自外部源，则这样做以避免 sql 注入：
cur.execute("UPDATE `Sensors` SET pH1 = %s,
pH2 = %s, Temp = %s, RH = %s,
TDS1 = %s, TDS2 = %s, CO2 = %s,
Light = %s, Water = %s, MagX = %s,
MagY = %s, MagZ = %s, TankTotal = %s,
Tank1 = %s, Tank2 = %s, Tank3 = %s,
Tank4 = %s, WaterTempP1 = %s,
WaterTempP2 = %s, WaterTempP3 = %s,
WaterTempP4 = %s ",
(pH1,pH2,Temp,RH,TDS1,TDS2,CO2,Light,Water,MagX,MagY,MagZ,
TankTotal,Tank1,Tank2,Tank3,Tank4,
WaterTempP1,WaterTempP2,WaterTempP3,WaterTempP4)
db.commit()