[Tutor] Formatting String for SQL Insert

[Lloyd Kvam]

Then it's probably an indent issue or a missing
	)
on the previous line.  Python points to the unexpected character,
but may be using the context from an earlier line.

richard wrote:

> Hi,
> 
> Removing the % still gives the following,
> 
>   File "frmfmna.py", line 243
>     c.execute(sqlquery1, tuple(datavalues))
>     ^
> SyntaxError: invalid syntax
> 
> 
> Regards
> 
> 
> At 19:49 21/01/2004, you wrote:
> 
>> >  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
>>
> 
> 

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

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