Help formatting a mysql query string

[RiGGa]

Hi,

I am trung to create a mysql query string that contais two variables, the
first holds a table name and the second holds the values as a tuple. I
have tried the following however I can not work out how to get the format
right so the %s is subsituted with the contents of the variable, I think I
just have the quoting wrong, can anyone advise?

tablename contains the table I want to use
datavalue contains the data I want to use (contains multiple fields, we will
say 3 here for this example)

sqlquery = "INSERT INTO %s", tablename + " values(%s,%s,%s)", datavalue"

Any help appreciated

Thanks

Rigga

 

[=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=]

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... }

 

[Steve Holden]

- 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

 

[Rigga]

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

 

[=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=]

The PHP way is awful (you have to escape your strings and
if you forget, you have a vulnerability)..

Is you want to have dynamic fields you can do :

cursor.execute( "INSERT INTO mytable SET %s=%s", (fieldname, value) )

or you could also do this :

query = "INSERT INTO mytable SET %s=%%s" % fieldname
cursor.execute( query, (value,) )

The last one is preferred if
- your SQL library precompiles and reuses queries (I don't know if it
does)
- You use executemany to insert several lines.

HOWEVER
The last variant has a security vulnerability : fieldname is not quoted.

Solution :
On entering your script, test :
if fieldname not in ('field1', 'field2'):
raise ValueError, "Invalid field name"

 

[Rigga]

The PHP way is awful (you have to escape your strings and
if you forget, you have a vulnerability)..

Is you want to have dynamic fields you can do :

cursor.execute( "INSERT INTO mytable SET %s=%s", (fieldname, value) )

or you could also do this :

query = "INSERT INTO mytable SET %s=%%s" % fieldname
cursor.execute( query, (value,) )

The last one is preferred if
- your SQL library precompiles and reuses queries (I don't know if it
does)
- You use executemany to insert several lines.

HOWEVER
The last variant has a security vulnerability : fieldname is not quoted.

Solution :
On entering your script, test :
if fieldname not in ('field1', 'field2'):
raise ValueError, "Invalid field name"

Thank you for explaining this so clearly it helps me a great deal.

All the best

R