Help formatting a mysql query string

[Steve Holden]

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