sara_bellum
asked on
Python mysqld format mapping error on insert
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:
but when I execute this, the query error reads "TypeError: format requires a mapping"
What to do?
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)
but when I execute this, the query error reads "TypeError: format requires a mapping"
What to do?
ASKER
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:
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!!
#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)
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!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much, I'm still figuring out how the model works. I used
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 :-)
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))) )
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 :-)
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:
Open in new window
It prints on my console (manually reformated):Open in new window