Fredrik Lundh wrote: > David Bear wrote > >> Fredrik Lundh wrote: >> >> > cursor.execute( >> > "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)), >> > *values >> > ) >> >> Thanks for the hint. However, I don't understand the syntax. >> >> I will be inserting in to postgresql 8.x. I assumed the entire string >> would be evaluated prior to being sent to the cursor. > > Looks like you missed advice 1-3. I'll take it again: DON'T USE STRING > FORMATTING TO INSERT VALUES IN A DATABASE. Sorry for shouting, > but this is important. Parameter passing gives you simpler code, and > fewer security holes. > >> However, when I attempt to manual construct the sql insert statment >> above I get an error: >> >> >>> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields), >> *values) >> File "<stdin>", line 1 >> print "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields), >> *values) >> ^ >> SyntaxError: invalid syntax > > DON'T MANUALLY CONSTRUCT THE SQL INSERT STATEMENT. Use string > formatting to insert the field names, but let the database layer deal with > the values. > > If you want to do things in two steps, do the fields formatting first > > query = "INSERT INTO table (%s) VALUES (%%s);" % (",".join(fields)) > > and pass the query and the values sequence to the database layer: > > cursor.execute(query, values) > > The database will take care of the rest. > > </F>
I think I'm missing some important documentation somewhere. Here's what I tried (using both % and $ signs): >>> sql 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES (%s);' >>> sql2 'INSERT INTO nic (addr_code,ip_address,property_control,mac_address) VALUES ($s);' >>> values ['p', '129.219.120.134', '6154856', '00:40:50:60:03:02'] >>> cursor.execute(sql1, values) Traceback (most recent call last): File "<stdin>", line 1, in ? NameError: name 'sql1' is not defined >>> cursor.execute(sql, values) Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib64/python2.4/site-packages/pgdb.py", line 163, in execute self.executemany(operation, (params,)) File "/usr/lib64/python2.4/site-packages/pgdb.py", line 187, in executemany raise OperationalError, "internal error in '%s': %s" % (sql,err) pg.OperationalError: internal error in 'INIT': not all arguments converted during string formatting I get the same error if using $ sign. When I look at the pygresql dbapi official site at http://www.pygresql.org/pgdb.html "this section needs to be written"... I would really appreciate some more examples on using pgdb (pygresql) -- David Bear -- let me buy your intellectual property, I want to own your thoughts -- -- http://mail.python.org/mailman/listinfo/python-list