[Tutor] Formatting String for SQL Insert

richard python at keep-trying.com
Tue Jan 20 18:57:52 EST 2004


Greetings,

Despite the help with my other string questions I am struggling
with this problem. Now that I can loop through my controls
(previous emails) I am able to convert the textCrtl data into a list.
This should be simple to pass through to my sql insert query. However
despite formatting, spliting, joining and even pickling? I have been
unable to do this and this is even before  trying to cope with nulls and 
special
characters.

I either get syntax errors, complaints that there are not enough
elements (when using %s) which I do not understand why, or errors
complaining I am trying to mix a string and tuple.

It must be possible to be able to loop it, format it to the correct
string and also allow for nulls and special characters. Infact
having read quite a few postings on google groups I know it
can be done but adapting instructions always fails. Whats
more annoying is that experimenting in the python shell I can
get the result I want.

Any help?? Should I being doing this at the point at which
I append the data from the textctrl into the list??

Regards

Richard

def saveButton(self, event):
         datavalues=[]
         for i in range(21):
             datavalues.append(eval('self.textCtrl%d.GetValue()' %i))
         c = mydb.cursor()
         #values = "'%s,'" %datavalues[0] + "'%s,'" %datavalues[1] + 
"'%s,'" %datavalues[2] + "'%s,'" %datavalues[3] + "'%s,'" %datavalues[4] + 
"'%s,'" %datavalues[5] + "'%s,'" %datavalues[6] + "'%s,'" %datavalues[7] + 
"'%s'," %datavalues[8] + "'%s,'" %datavalues[9] + "'%s,'" %datavalues[10] + 
"'%s,'" %datavalues[11] + "'%s,'" %datavalues[12] + "'%s,'" %datavalues[13] 
+ "'%s,'" %datavalues[14] + "'%s,'" %datavalues[15]+ ""'%s,'"" 
%datavalues[16]+ "'%s,'" %datavalues[17]+ "'%s,'" %datavalues[18]+ "'%s,'" 
%datavalues[19] + "'%s'" %datavalues[20])


values = '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' 
'%s' '%s' '%s' '%s' '%s' '%s' '%s' '%s' % (datavalues[0], datavalues[1], 
datavalues[2], datavalues[3], datavalues[4], datavalues[5], datavalues[6], 
datavalues[7], datavalues[8], datavalues[9], datavalues[10], 
datavalues[11], datavalues[12], datavalues[13], datavalues[14], 
datavalues[15], datavalues[16], datavalues[17], datavalues[18], 
datavalues[19], datavalues[20])

#values = %s,datavalues

         sqlquery1 = "Insert into fmna values (" + values + ");"
         print sqlquery1
         c.execute(sqlquery1)
         mydb.commit()
         mydb.close() 




More information about the Tutor mailing list