python - 值被读入作为 mysql 查询中的列标题

Question

我正在尝试将数据添加到 mysql 数据库。有人可以解释为什么查询似乎将 temp 视为列而不是它应该是的字符串值吗？

pi@raspberrypi ~/Desktop/PiControl $ python writedb.py
rollback:  (1054, "Unknown column 'temp' in 'field list'")
done

我的 table

mysql> select * from logs;
+----+--------+---------+------+
| id | sensor | reading | time |
+----+--------+---------+------+
|  1 | temp   |    NULL | NULL |
|  2 | NULL   |   69.69 | NULL |
|  3 | NULL   |   69.69 | NULL |
|  4 | NULL   | 1234.56 | NULL |
|  5 | NULL   | 1234.56 | NULL |
|  6 | NULL   | 1234.56 | NULL |
|  7 | temp   |     123 | NULL |
|  8 | temp   |      20 | NULL |
+----+--------+---------+------+
8 rows in set (0.00 sec)

脚本:

import MySQLdb
conn = MySQLdb.connect(host= "localhost",user="xx",passwd="yy",db="homelog")
x = conn.cursor()

try:
   x.execute("INSERT INTO `logs` (`sensor`,`reading`) VALUES (`temp`,`4242`)")
   #print x.execute("SELECT * FROM logs") #returns number of rows in table for some reason
   conn.commit()
except Exception, e:
   print 'rollback: ',e
   conn.rollback()

conn.close()
print "done"

Answer 1

尝试参数化您的查询，避免对列值使用反引号:

sensor = "temp"
reading = 4242
x.execute("INSERT INTO `logs` (`sensor`,`reading`) VALUES (%s, %s)", (sensor, reading))

print x.execute("SELECT * FROM logs") #returns number of rows in table for some reason

执行后需要从游标中取出所有匹配的记录:

sensor = "temp"
reading = 4242
x.execute("INSERT INTO `logs` (`sensor`,`reading`) VALUES (%s, %s)", (sensor, reading))
conn.commit()

x.execute("SELECT * FROM logs")
print(x.fetchall())