a question about mysqldb

[Evan]

a simple problem but I do not know why...:(, could anyone help me?

MySQLdb nominally uses just the %s placeholder style, in my script, i
got error if you want to use placeholder(%s) for table name:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +

>>str="select tID,tNote from %s where tID=1" <-------- check here

e=["tmp"]

>>s.dbptr.execu te(str,e)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
166, in execute
self.errorhandl er(self, exc, value)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py" , line
35, in defaulterrorhan dler
raise errorclass, errorvalue
_mysql_exceptio ns.ProgrammingE rror: (1064, "You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''tmp') where tID=1' at line
1")

>>>

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++
But sql worked but the I got no query result:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

>>str="select tID,tNote from tmp where %s = %s" <----------check here
e=["tID",int(1 )]
s.dbptr.execu te(str,e)

0L <------------------ check here

>>>
s.dbptr.fetch all()

()

>>>

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++
And then, it worked if I do:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

>>str="select tID,tNote from %s where %s = %s" % ("tmp","tID" ,1)

str

'select tID,tNote from tmp where tID = 1'

>>s.dbptr.execu te(str)

1L

>>>
s.dbptr.fetch all()

({'tID': 1L, 'tNote': 'kao'},)
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++
mysqldesc tmp
-;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tID | int(11) | NO | PRI | NULL | auto_increment |
| tDate | date | YES | | NULL | |
| tSID | int(11) | NO | | NULL | |
| tCom | varchar(15) | YES | | NULL | |
| tNote | text | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++

mysql>
mysql>

Thanks,

[Fredrik Lundh]

Evan wrote:

a simple problem but I do not know why...:(, could anyone help me?

MySQLdb nominally uses just the %s placeholder style, in my script, i
got error if you want to use placeholder(%s) for table name:

Placeholders are supposed to be used for *values*, not other parts of
the SQL statement. To insert table names, column names and stuff like
that, use Python-level formatting.

try doing:

table = "tmp"
sql = "select tID,tNote from " + table + " where tID=%s"
param = [1]
s.dbptr.execute (sql, param)

But sql worked but the I got no query result:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

>>str="select tID,tNote from tmp where %s = %s"
e=["tID",int(1 )]

the string value "tID" doesn't match an integer with the value 1, so
that's expected.

</F>

[Bruno Desthuilliers]

Evan a écrit :

a simple problem but I do not know why...:(, could anyone help me?

MySQLdb nominally uses just the %s placeholder style, in my script, i
got error if you want to use placeholder(%s) for table name:

db-api placeholders won't work for table names - or for anything that
isn't supposed to be a value FWIW. String args are quoted, so you end up
with you sql looking like:

select tID, tNote from 'tmp' where tID=1

instead of

select tID, tNote from tmp where tID=1
You may want to try this instead:

tablename = "tmp"
sql = "select tID, tNote from %s where tID=%%s" % tablename
args = (1,)

s.dbptr.execute (sql, args)

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ +

>>>str="selec t tID,tNote from %s where tID=1" <-------- check here

e=["tmp"]

>>>s.dbptr.exec ute(str,e)

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
166, in execute
self.errorhandl er(self, exc, value)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py" , line
35, in defaulterrorhan dler
raise errorclass, errorvalue
_mysql_exceptio ns.ProgrammingE rror: (1064, "You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''tmp') where tID=1' at line
1")
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++
But sql worked but the I got no query result:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

>>>str="selec t tID,tNote from tmp where %s = %s" <----------check here
e=["tID",int(1 )]

<ot>
- 1 is an int already, so make this e = ["tID", 1]
- str is a very bad choice for an identifier. It's not only
uninformative, but it will also shadow the builtin str type
</ot>

>>>s.dbptr.exec ute(str,e)

0L <------------------ check here

>>>s.dbptr.fetc hall()

()
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

Same problem. Here you end up with something like:

select tID, tNote from tmp where 'tID'=1

You want:

field = "tID"
sql = "select tID,tNote from tmp where %%s = %s" % field
args = (1,)

>
And then, it worked if I do:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

>>>str="selec t tID,tNote from %s where %s = %s" % ("tmp","tID" ,1)

str

'select tID,tNote from tmp where tID = 1'

>>>s.dbptr.exec ute(str)

1L

>>>s.dbptr.fetc hall()

({'tID': 1L, 'tNote': 'kao'},)
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++++

Since your not using the db-api quoting mechanism, this of course works
as you expect. *But* this is a potential security hole (perfect
candidate for an sql-injection attack). Use the db-api quoting mechanism
for args, use string formatting for anything else.

[Eric Wertman]

I also like to use escaped identifiers in cases like this:

sql = "select tID,tNote from %s where %s = %%s" % ("tmp","tID" )
cursor.execute( sql,1)

should work fine.