Help formatting a mysql query string

[Rigga]

Steve Holden wrote:

> Pierre-Frédéric Caillaud wrote:
> 
>> 
>>> sqlquery = "INSERT INTO %s", tablename + " values(%s,%s,%s)", datavalue"
>> 
>> 
>>     - what are the "," doing here if you are trying to build a string ?
>>     - you should use the python povided way which is better (yours looks
>> like  php)
>> 
>>     cursor.execute( "INSERT INTO %(tb)s VALUES(%(a)s,%(b)s,%(c)s)",  {
>> 'tb':tablename, 'a':first data, 'b':second data, etc... }
> 
> Better still, create the query string with the right table name in it
> and parameter markers for the data:
> 
> sqlquery = "INSERT INTO %s VALUES (%%s, %%s, %%s)" % tablename
> 
> Supposing tablename is "customer" this gives
> 
> "INSERT INTO customer VALUES (%s, %s, %s)"
> 
> Then you can use the parameter substitution mechanism of the DB API to
> insert your data in there. Suppose datavalue is ("Steve", "Holden", 85)
> then you would do
> 
> cursor.execute(sqlquery, datavalue)
> 
> The problem with building the data portion of the statement is having to
> put the single quotes in around strings and escape any single quotes
> that might occur in the values you present. It's much easier to use the
> parameter substitution mechanism, even though that *can't* be used to
> change a table name in most SQL implementations.
> 
> I've assumed for the sake of argument that you're using MySQLdb, which
> uses the "%s" paramstyle. mxODBC, for example, you'd use the "?" style,
> which makes building statements rather easier.
> 
> One final comment: it's much safer to use the column names in INSERT, as
> in
> 
> INSERT INTO customer (First, Last, age)
> VALUES ('Steve', 'Holden', 95)
> 
> because that isolates you from a change in the column ordering, which
> can happen during database reorganizations when you insert a new column
> without thinking about the consequences. Just paranoia induced by years
> of experience, and therefore often effort-saving.
> 
> regards
>   Steve
Thanks for the help, sorry I posted this twice, my news reader was not
showing the original post so i resubmitted it.  I normally work with php
thats why I was trying to build it as a string. I now see how I can load
the data values from my variables, however is the same possible for the
fields? I know you say its best to specify the column names etc however my
script is parsing a web page and getting the field headings (which will
stay the same), data and table name so I wanted to make the script handle
all this rather than having to have a seperate cursor.execute() for each
table I want to update - does this make sense?

Regards

Rigga