[Tutor] Formatting String for SQL Insert

[richard]

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