passing a parameter into the execute command

[paulb]

Can someone please tell me how to pass a parameter into the execute command.
For example I want to use the command:

DROP TABLE IF EXISTS animal

I believe I can use the command is:

test="animal"

cursor.execute ("DROP TABLE IF EXISTS %s", (test,))

Can someone please confirm?

Thanks

[Cat slave]

Unfortunately, this will pass the following code to the server:

DROP TABLE IF EXISTS "animal"

which is not what you are looking for. You could simply go for:

test = "animal"

cursor.execute("DROP TABLE IF EXISTS %s" % (test,))

but it's not exactly what you asked for...

[paulb]

Thanks Huuda for the reply. It seems a bit of an overkill. Just to clarify the
reason I am using quotes is to tell python that it is a string.

Maybe there is a other way to do it in python.

[paulb]

Thanks Huuda,

There was a bit of confusion with the viewing your post in this forum! My
e-mail did show me exactly what I neeeded and that worked! Thanks for all your
help.

For future reference is there a simple test that I can do to check the
complete string (ie DROP TABLE IF EXISTS animal) what is being passed to the
mySQL server? Can I turn any debug on? Maybe I could of tested simply with a
python string?

Thanks again,

Paul.

[Kyle VanderBeek]

The important thing to remember is that the escaping mechanism built into
execute() is for column values. You'd use it for "INSERT INTO t (a, b)
VALUES (%s, %s)" or "SELECT * FROM t WHERE col1=%s".

What you were doing is not value substitution, but dynamic SQL (creating a
dynamic query with a changeable table name). That's the place for doing
string-style (%) substitution. Continue to use the automatic escaping
mechanism in execute() when you need to sub in column values.

[paulb]

Thanks Kylev for the advice. Although I am not too sure exactly what you mean
: (

[paulb]

Hi,

Can someone please help me out with the following. I need to a create a simple
table with a parameter passed in for the name and a parameter passed in for an
item. I have tried the following code but I get an error.

cursor.execute ("""

CREATE TABLE %s

(

ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

%s TEXT,

)

""" % (name, item))

Also I would probably like to add to this table at some point and could add
some extra parameters. I am guessing the syntax is the same?

Hope someone can help,

Thanks paul.

[Cat slave]

Just drop the last comma after "%s TEXT".

The parameter to execute() essentially assure you that the value is properly
escaped (right combinaison of quotes or double-quotes is applied to your
value).

[paulb]

Thanks again Huuda for your reply. It was a simple mistake : (