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)

Have a look at stackoverflow.com/questions/8316176/… for starters. — Mike P
– Mike P, Commented Oct 30, 2015 at 9:13
The list I have has sublist within, and the values of those sublist needs to be updated — Deb
– Deb, Commented Oct 30, 2015 at 10:12

Devin Howard · Accepted Answer · 2015-10-30 10:25:09Z

2

You have three tasks:

Exclude the date fields from the list
Set up Python to run SQL commands
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.

answered Oct 30, 2015 at 10:25

Devin Howard

7151 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deb Over a year ago

Thanks... I am getting syntax error with this code for key in dictionary: items_to_insert.append([date_for_insert, key, dictionary[key])

Deb Over a year ago

To add on is it because type(ls) is a list and here dictionary parsing is used... just guessing

Devin Howard Over a year ago

@D14 no I just forgot 2 brackets. You should configure your code editor to highlight bracket matching. I've updated my answer to fix the missing bracket, and tested to make sure the code has no syntax errors.

Devin Howard Over a year ago

Looks like you'll also need to pop the "date" items as well, depending on what you're trying to accomplish. I won't give you the code for that ;)

Collectives™ on Stack Overflow

Insert dictionary within list to database in python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related