[Tutor] Formatting String for SQL Insert

[Lloyd Kvam]

 >  c.execute(sqlquery1, % tuple(datavalues)) # assume this inserts each
                        ^^^
omit the %

You are not doing string interpolation here.  The %s in the sqlquery is
a placeholder for the sql module and not an interpolation marker for
python.

richard wrote:

> Greetings,
> 
> Ok, working on the second example which you gave, (seems
> a good idea to let the module do some of the work if it means
> less coding and ensuring that the format is correct.)
> 
> (connection module is PyPgSQL format string %s)
> 
> However when I have tried using the code, virtually as is
> apart from the comma at then end of the 4th line. The
> script will not compile complaining of a syntax error in
> the c.execute line.
> 
> If I remove the reference to the table fields (which in this
> case I do not need) I then get the error of not enough
> attributes. The new code is below. I have added some
> comments of what I think the code is doing. It may be
> I am misunderstanding something.
> 
> -----> Start
> def saveButton(self, event):
> datavalues=[]  #set up empty list
> db_keys = [] #set up empty list
> db_keys = 
> ('code','name','shortname','adds1','adds2','adds3','town','county','pcode','country','shortloc','tel1','tel2','spare1','spare2','mob1','mob2','fax1','type','contact','comments') 
> # field names
> tablename = 'fmna' # set name of table
> 
> for i in range(21): # loop to get datavalues from the the text controls 
> on form
>         datavalues.append(eval('self.textCtrl%d.GetValue()' %i))
>  c = mydb.cursor() # setup up database query
>  sqlquery1 = 'Insert into %s (%s) values (%s);' % (  #string
>                     tablename,  # 1st %s
>                     ','.join(db_keys), #2nd %s
>                     ','.join(join(['%s'] * len(db_keys)) # unsure of this
>                     )
>  c.execute(sqlquery1, % tuple(datavalues)) # assume this inserts each 
> datavalue as correctly
> formated for SQL string??
> 
> <---- end (spacing is mangled)
> 
> More help is once again appreciated.
> 
> Regards
> 
> Richard
> 
> 
> 
>> 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
> 
> 
> 
> _______________________________________________
> 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