mysql 请求中的 Python 变量

Question

我对 Python 还很陌生(Python 3.4.6):)
我正在尝试将一些带有变量的行插入到 mysql 数据库中。
一开始，我有一个字典list_hosts。
这是我的代码:

import mysql.connector
import time
db = mysql.connector.connect(host='localhost', user='xxxxx', passwd='xxxxx', database='xxxxx')
cursor = db.cursor()
now_db = time.strftime('%Y-%m-%d %H:%M:%S')
for key, value in list_hosts
    key_db += key+", "
    value_ex += "%s, "
    value_db += "\""+value+"\", "
key_db = key_db.strip(" ")                                                                                                                                                                                                                            
key_db = key_db.strip(",")
value_ex = value_ex.strip(" ")
value_ex = value_ex.strip(",")
value_db = value_db.strip(" ")
value_db = value_db.strip(",")
add_host = ("INSERT INTO nagios_hosts (date_created, date_modified, "+key_db+") VALUES ("+value_ex+")")
data_host = ("\""+now_db+"\", \""+now_db+"\", "+value_db)
cursor.execute(add_host, data_host)
db.commit()
db.close()

list_hosts 示例:

OrderedDict([('xxxx1', 'data1'), ('xxxx2', 'data2'), ('xxxx3', 'data3'), ('xxxx4', 'data4'), ('xxxx5', 'data5'), ('xxxx6', 'data6')])

我当然简化了代码。我这样做是因为我的字典中从来没有相同数量的项目。我正在尝试创建这样的东西:

add_host - INSERT INTO TABLE (date_created, date_modified, xxxx1, xxxx2, xxxx3, xxxx4, xxxx5, xxxx6) VALUES (%s, %s, %s, %s, %s, %s)
data_host - now, now, data1, data2, data3, data4, data5, data6

永远不会有相同数量的 xxxx...它们都存在于数据库中，但我不需要为字典中的每个项目填充每一列。

当我执行时，我收到此错误:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxxxxxxxxxxxxxxxxxxxx' at line 1

当我开始使用 Python 时，我认为我们也可以清理很多东西......不要犹豫:)

Answer 1

这是一个规范的 python3(与 python2 兼容)解决方案:

import time
from collections import OrderedDict

list_hosts = OrderedDict([("field1", "value1"), ("field2", "value2"), ("fieldN", "valueN")])

# builds a comma-separated string of db placeholders for the values:
placeholders = ", ".join(["%s"] * (len(list_hosts) + 2))

# builds a comma-separated string of field names     
fields =  ", ".join(("date_created","date_modified") + tuple(list_hosts.keys()))

# builds a tuple of values including the dates
now_db = time.strftime('%Y-%m-%d %H:%M:%S')
values = (now_db, now_db) + tuple(list_hosts.values())


# build the SQL query:
sql = "INSERT INTO nagio_hosts({}) VALUES({})".format(fields, placeholders)

# and safely execute it 
cursor.execute(sql, values)
db.commit()