解码JSON-字符串并插入SQL-数据库(sqlite3)

Question

我想解码一个JSON格式的字符串，并通过MySQL将它插入到一个SQL-数据库中。

import json
import MySQLdb as mdb

my_json = '{"timestamp":1479132183,"sn":"B59EC63F","u":[3346,3346,3347,3346],"soc":96,"i":-32,"cc":345351,"ccMax":360000}'

parsed_stuff = json.loads(my_json)

con = None

try:
    con = mdb.connect('localhost', 'name', 'password', 'mysql')
    cur = con.cursor()
    cur.execute("SELECT * FROM database")
    

    cur.execute("INSERT INTO database (timestamp, sn, u0, u1, u2, u3, soc, i, cc, ccMax) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % (parsed_stuff['timestamp'], parsed_stuff['sn'], parsed_stuff['u'[1]], parsed_stuff['u'[2]], parsed_stuff['u'[3]], parsed_stuff['u'[4]], parsed_stuff['soc'], parsed_stuff['i'], parsed_stuff['cc'], parsed_stuff['ccMax']))
    con.commit()

except mdb.Error as e:
     print("Error %d: %s" % (e.args[0], e.args[1]))
     sys.exit(1)

if con:
    con.close()

它抛出错误：

“字段列表中的未知列'B59EC63F‘”

额外抛出

"IndexError: string索引超出范围“。

Answer 1

我改变了

VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

至

VALUES (%s, '%s', %s, %s, %s, %s, %s, %s, %s, %s)

现在起作用了。