Solved

Python mysqld format mapping error on insert

Posted on 2013-11-26
4
498 Views
Last Modified: 2013-11-30
I am trying to insert values into a mysql table containing 75 fields; the python mysqldb syntax to insert those values without specifying the field names in the sql statement is needed (otherwise the insert statement would be very long!).  I tried this:

my_dictionary = {'Local_Time':'Tuesday', 'Last_Name':'Smith', 'First_Name':'Sally', 'Middle_Name':'Jolie'}
    
    try:
	#use config.py to do this...
        con = mdb.connect('localhost', 'admin', 'password', 'db_name');

        with con:
            cursor = con.cursor()
	    
	    for field in my_dictionary:
		value = my_dictionary[field]
		sql = "INSERT into table_name (field) VALUES %(value)s" 
	    
            cursor.executemany(sql, my_dictionary)

Open in new window


but when I execute this, the query error reads "TypeError: format requires a mapping"
What to do?
0
Comment
Question by:sara_bellum
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:pepr
ID: 39680093
Peeping shortly to the doc, the .executemany does expect a list of tuples as the second argument, not the dictionary. The tuples in the list are the records to be inserted. You still need to supply the command template with that many of %s how many of columns you have (i.e. how many tuples). You also need to name the columns in the first part of the INSERT INTO.

However, if you have one record in a dict, it is possible to construct the string of the INSERT INTO command. Here with commented-out execution of the command:
my_dictionary = {'Local_Time':'Tuesday', 'Last_Name':'Smith', 'First_Name':'Sally', 'Middle_Name':'Jolie'}

sql_command = 'INSERT INTO table_name (' + ', '.join(my_dictionary.keys())        \
              + ') VALUES (' + ', '.join(repr(v) for v in my_dictionary.values()) \
              + ')'
   
print(sql_command)
   
#con = mdb.connect('localhost', 'admin', 'password', 'db_name');
#cursor = con.cursor()
#cursor.execute(sql_command)
#con.close()

Open in new window

It prints on my console (manually reformated):
c:\__Python\sara_bellum\Q_28304887>a.py
INSERT INTO table_name (First_Name, Last_Name, Middle_Name, Local_Time) 
VALUES ('Sally', 'Smith', 'Jolie', 'Tuesday')

Open in new window

0
 

Author Comment

by:sara_bellum
ID: 39682859
Thanks Pepr I was astounded at how quickly I was able to get this working, all things considered.  Your solution produced a substitution error on insert that I can't seem to find at the moment, but thanks to help you provided on this occasion and earlier I found that this works:

#user_data is the dictionary passed to the function
#form_list matches/orders the dictionary keys 
 value_list = [] 
 model, name = modelAndNameDictionaries(user_data)
 for i in form_list: 
	for k, v in model.items():    
	    if k == i:
		value_list.append(v)
	
 #format list values to build query    
 sql_1 = ', '.join(form_list)
 sql_2 = "','".join(value_list)    
 sql_cmd = ("INSERT INTO shc_students(" + sql_1 + ") VALUES ('" + sql_2 + "')")
 #print sql_cmd #verify syntax
    
  try:
        con = mdb.connect('localhost', 'username', 'password', 'dbname');
        with con:
            cursor = con.cursor()
	    cursor.execute(sql_cmd)

Open in new window


I've been working other issues that I had hoped to fix by now but will close this out tomorrow or the next day.  Happy Thanksgiving!!
0
 
LVL 28

Accepted Solution

by:
pepr earned 500 total points
ID: 39682917
Well, we do not have that Turkey custom here. Thanks anyway. :)

For the substitution error, I know I did use the repr(v) for the values. It means the values are quoted the Python way if Python thinks it should be represented as a string literal. Similarly, you are single-quoting every value. This should probably be rethought and implemented in a more robust way.

For dict.keys() and dict.values() -- they both traverse the dict item in an unpredictable order. Anyway, when the dictionary is not modified, the order is the same for both keys and values. However, composing of the name list and of the value list could be done in parallel (I do not mean parallel in the sense of more procesors, you know).

You are repeating your earlier inefficient nested for loops at the lines 5 to 8:
for i in form_list: 
	for k, v in model.items():    
	    if k == i:
		value_list.append(v)

Open in new window

Whenever you find youself nesting the for loops, think twice what you do (no offence). Here you loop through the form_list (this is OK). Then you loop through all items in the model dictionary only to find one element with the given key k. This is not OK. The dictionary is good just because you can find the items more easily when the key is given. The code should look like:
for i in form_list:
    v = model.get(i, default_value)    # or v = model[i] if you know it is inside
    value_list.append(v)

Open in new window

0
 

Author Comment

by:sara_bellum
ID: 39687785
Thanks so much, I'm still figuring out how the model works.  I used

v = model[i]

and fixed up the sql command like so:

sql_cmd = ("INSERT INTO shc_students(%s) VALUES ('%s')" % (cgi.escape( sql_1, True), (cgi.escape( sql_2, True))) )

Open in new window


Spent much of the past few days patching up loop-from-hell errors, my code is too long.  When I know more I'll rewrite this, but it works well enough for now.

Happy holidays :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now