insert values from a Python dictionary including the key to MySQL

Question

I have the following dictionary:

   {    '': ['0', '9'], 
    '3904': ['playback_error', '87'], 
    '3808': ['playback_error', '24'], 
    '3902': ['qp_library_failed_to_start', '1'], 
    '3903': ['playback_error', '464'],
     '3805': ['playback_error', '141'], 
    '3807': ['playback_error', '29'], 
    '3806': ['playback_error', '1'], 
    '1309': ['playback_error', '2'],
     '3803': ['playback_error', '28'], 
    'BL-1008': ['parental_controls_error', '5'], 
    'errorCode': ['eventKey', '2'],
     '404': ['tbr_error', '68'],
     '3308': ['playback_error', '10']}

I want to insert those values into mysql database for example:

   ERRORCODE, EVENTKEY, COUNT

    3904, playback_error, 87

    3808,playback_error, 24

    3902,qp_library_failed_to_start,1

    3903,playback_error

I created a python code to perform this action but it doesnt insert the values to the database i get the error : mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1") here's my python code:

data=mydict
print data
#print data.values()

# Open database connection
db = MySQLdb.connect(host,user,passwd,db)

cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = '''INSERT INTO errorscode (id,date,errorcode,eventkey,count) VALUES(NULL,(DATE_ADD(CURDATE(), INTERVAL -1 day),%s,%s,%s))'''


   # Execute the SQL command
   cursor.executemany(sql,data.values())
   # Commit your changes in the database
   db.commit()

   # disconnect from server
   cursor.close()
   db.close()

Thanks to an answer below, i fix my problems FINAL CODE WORKING 100% :) :

#!/usr/bin/python
from StringIO import StringIO
import numpy as np
import csv
import MySQLdb
import os

with open('csv_err2.log', mode='r') as infile:
    reader = csv.reader(infile)
    mydict = dict((rows[0],[rows[2], rows[1]]) for rows in reader)

data=mydict
print data

# Open database connection
db = MySQLdb.connect(host="localhost",user="root",passwd="bravoecholimalima",db="capacityreports_mobiletv")

cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = '''INSERT INTO errorscode (id,date,errorcode,count,eventkey) VALUES(NULL,(DATE_ADD(CURDATE(), INTERVAL -1 day)),%s,$

sql_values_list = list()
for key, value in data.iteritems():
    sql_values_list.append((key,int(value[0]),value[1]))

print sql_values_list

# Execute the SQL command
cursor.executemany(sql, sql_values_list)

# Commit your changes in the database
db.commit()

# disconnect from server
cursor.close()
db.close()

Sure it is not displaying an error - you are ignoring it by having an except with a rollback. — alecxe
– alecxe, Commented Feb 23, 2015 at 19:26
To add to @alecxe's comment, if you add a raise statement immediately after the db.rollback(), then you'll at least see the exception and maybe have a place to start debugging. — mgilson
– mgilson, Commented Feb 23, 2015 at 19:33

favoretti · Accepted Answer · 2015-02-23 19:41:24Z

1

Well, this approach won't work.

For one, mydict.values() or data.values() in your case will be an list of lists:

[['0', '9'], ['playback_error', '87'], ['playback_error', '24'], ['qp_library_failed_to_start', '1'], ['parental_controls_error', '5'], ['playback_error', '141'], ['playback_error', '29'], ['playback_error', '1'], ['playback_error', '2'], ['playback_error', '28'], ['eventKey', '2'], ['playback_error', '10'], ['playback_error', '464'], ['tbr_error', '68']]

So, if you want to iterate, you'd need something in lines of:

sql = '''INSERT INTO errorscode (id,date,errorcode,eventkey,count) VALUES(NULL,(DATE_ADD(CURDATE(), INTERVAL -1 day),%s,%s,%s))'''

sql_values_list = list()
for key, value in data.iteritems():
    sql_values_list.append((key, value[0], value[1]))

try:
    # Execute the SQL command
    cursor.executemany(sql, sql_values_list)
    # Commit your changes in the database
    db.commit()
except:
    # bla

data.iteritems() will retrieve the key and the corresponding value in one go during the iteration, which you can further use. Your value is a list, that's why to pass it onwards you'll need to address the members explicitly.

You restructure your dict to .executemany()'s format and pass it on.

Note that the way I showed in my example is potentially unsafe if your data structure isn't static, i.e. if you have a value where list only consists of 1 element - the code will fail miserably.

edited Feb 23, 2015 at 19:41

answered Feb 23, 2015 at 19:34

favoretti

30.4k4 gold badges52 silver badges62 bronze badges

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

3 Comments

favoretti Over a year ago

Pardon my constant editing I'm somewhat tired... Should be done now :)

Raphael E Over a year ago

Hello favoretti Thanks for the fast reply i tried your code but i get the error : mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")

favoretti Over a year ago

@RaphaelE: well, you need to adjust my example code to fit your columns count, etc :)

Collectives™ on Stack Overflow

insert values from a Python dictionary including the key to MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related