mysql - Insert dictionary within list to database in python

Question

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}]

My table structure is as below:

Datetime,Item,Value

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)

Answer 1

You have three tasks:

1. Exclude the date fields from the list
2. Set up Python to run SQL commands
3. Create code to insert the data into the database

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]

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

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.