在列表中将字典插入python中的数据库 [英] Insert dictionary within list to database in python

问题描述

我有一个如下列表，我整理了一下列表.数据是按日期排列的.所以对于每个日期，都有不同项目的值.列表的每个索引都是字典

I have a list as below,I have trimmed the list.The data is by date.So for each date there are values for different items. each index of the list is a dictionary

ls=[{'item1': 6755, 'item2': 20, 'item3': 3, 'item4': 8.04, 'item5': 24, 'item6': 208, 'date': 'Thu. 29 Oct. 2015', 'item6': 329, 'datetime': datetime.datetime(2015, 10, 29, 0, 0), 'item7': 31, 'item8': 1.24, 'item9': 28.00, 'item10': 10},{'item1': 67, 'item2': 202, 'item3': 33, 'item4': 28.04, 'item5': 234, 'item6': 2308, 'date': 'Thu. 30 Oct. 2015', 'item6': 3249, 'datetime': datetime.datetime(2015, 10, 30, 0, 0), 'item7': 331, 'item8': 21.24, 'item9': 238.00, 'item10': 410}]

我的表结构如下:

Datetime,Item,Value

我需要按日期将每个项目插入此表单，并在列表中排除日期字段.我是python的新手，无法解决此问题.我之前已经完成了每个项目进入表中的单独列的操作，但是在这里我需要插入选择性数据的地方有所不同，即(从列表中排除日期字段)

I need to insert each of the item by date into this form and exclude the date field in the list. I am new with python not able get how to go about this. I have done before where each item goes to separate column in table but here it's different where I need to insert selective data, i.e(excluding date field from the list)

推荐答案

您有三个任务:

1. 从列表中排除日期字段
2. 设置Python以运行SQL命令
3. 创建代码以将数据插入数据库

我不确定您希望如何存储数据库中包含的数据，但是我会尽力而为.

I'm not 100% sure how you hope to store the data that you've included in the database, but I'll give my best guess.

items_to_insert = []
for dictionary in ls:
  #pop removes the value from the dict
  date_for_insert = dictionary.pop("datetime", None)
  if date_for_insert is None:
    raise ValueError('No datetime - aborting')
  for key in dictionary:
    items_to_insert.append([date_for_insert, key, dictionary[key]

此代码转到ls列表中的每个字典，删除日期时间，然后将数据解析为数组.现在您已设置要插入数据

This code goes to each dictionary in the ls list, removes the datetime, and then parses the data into an array. Now you're set to insert the data

对于任务2，您需要使用PyMySQL或类似的东西，并设置连接和内容，然后对于任务3运行:

For task 2 you'll need to use PyMySQL or something like it, and set up your connections and stuff, and then for task 3 run:

for item in items_to_insert:
  cursor.execute("INSERT INTO mytable (Datetime,Item,Value) VALUES ('{}', '{}', '{}')".format(item[0], item[1], item[2]))

或者类似的东西.由于从上方进行了数据预处理，因此此行更容易.

Or something like that. This line is easier because of the data preprocessing from above.

您可能需要以某种方式设置日期时间格式，以便此代码正常工作.

You may need to format the datetime in a certain way for this code to work correctly.

这篇关于在列表中将字典插入python中的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！