我正在尝试向MySQL写一个python字典,该字典每个键具有多个值,试图使用类似于以下内容的东西:
placeholders = ', '.join(['%s'] * len(myDict))
columns = ', '.join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
# valid in Python 2
cursor.execute(sql, myDict.values())
# valid in Python 3
cursor.execute(sql, list(myDict.values()))
我遇到以下错误:
Traceback (most recent call last):
File "./grabber.py", line 86, in <module>
cursor.execute(sql, list(relay_stats.values()))
File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')
字典如下:
server01 (0.0, 0.0, '2020-07-07 15:01:54')
server02 (0.0, 0.0, '2020-07-07 15:01:54')
server03 (0.0, 0.0, '2020-07-07 15:01:54')
server04 (0.0, 0.0, '2020-07-07 15:01:54')
MySQL表:
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| server_name | char(1) | YES | | NULL | |
| file_count | float | YES | | NULL | |
| file_size | float | YES | | NULL | |
| curtime | datetime | YES | | NULL | |
+-------------+----------+------+-----+---------+----------------+
如何插入此值,以便每个值都是数据库中的一列,同时将0.0值保留为整数?
创建一个包含值的元组列表,而不是创建另一个字典:
values = []
for key, value in sorted(servers.items()):
values.append((key, fc_grab(value), fs_grab(value), curtime))
现在您可以创建SQL
placeholders = ', '.join(['%s'] * len(values[0]))
columns = ', '.join(['server_name', 'file_count', 'file_size', 'curtime'])
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
cursor.executemany(sql, values)
cursor.executemany
将所有行插入单个python方法调用中,而不是循环执行cursor.execute
。