[Tutor] Formatting String for SQL Insert

[Lloyd Kvam]

In the code snippets below,
	tablename is the name of the table (you knew that)
	db_keys is a list with the names of the database fields (attributes)
	db_vals is a list of the corresponding values to go into the database

stmt = 'INSERT INTO %s (%s) VALUES (%s);' % (
	tablename,
	','.join(db_keys),
	','.join(db_values)
	)
This is one simple approach to creating an insert statement.  It will
work so long as the db_values have already been turned into strings
and each string is properly quoted.

An alternative approach depends upon the parameter processing for
you sql module.  Since your example shows %s, I'll assume that's
correct.  the module will have an attribute paramstyle which tells
you how parameters are specified (see the DB API spec).  To use %s,
the paramstyle should be 'format'.

code is UNTESTED but should give correct idea

stmt = 'INSERT INTO %s (%s) VALUES (%s);' % (
	tablename,
	','.join(db_keys),
	','.join(['%s'] * len(db_keys)),
	)
cursor.execute(stmt, tuple(db_values))

This approach puts the burden of converting your values into the proper
format for your DB on the DB module.

HTH

richard wrote:
> 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()
> 
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
> 

-- 
Lloyd Kvam
Venix Corp.
1 Court Street, Suite 378
Lebanon, NH 03766-1358

voice:	603-653-8139
fax:	801-459-9582