a question about mysqldb

E

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.execute(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.errorhandler(self, exc, value)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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.execute(str,e) 0L <------------------ check here

s.dbptr.fetchall() ()
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


And then, it worked if I do:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++({'tID': 1L, 'tNote': 'kao'},)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> desc 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,
 
F

Fredrik Lundh

Evan said:
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>
 
B

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="select tID,tNote from %s where tID=1" <-------- check here

e=["tmp"]
s.dbptr.execute(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.errorhandler(self, exc, value)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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)]

<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
()
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
({'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.
 
E

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,818
Messages
2,569,736
Members
45,709
Latest member
DRISenaida

Latest Threads

Top