MySQLdb select

[Andy Todd]

F. GEIGER wrote:
> "Gerhard Häring" <gh at ghaering.de> schrieb im Newsbeitrag
> news:mailman.1008.1091284523.5135.python-list at python.org...
> 
> 
> 
>>That's particularly BAD STYLE. It's best to keep to letting the DB-API
>>do the proper quoting for all parameters.
> 
> 
> 
> Well, yes.
> 
> So I tried this:
> 
> 
>>>>import MySQLdb as ms
>>>>con = ms.connect(db="isa",user="root")
>>>>cur = con.cursor()
>>>>cur.execute("select id from %s limit 10;", ("tagevents",))
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line 95,
> in execute
>     return self._execute(query, args)
>   File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\cursors.py", line
> 114, in _execute
>     self.errorhandler(self, exc, value)
>   File "C:\PROGRA~1\Python23\lib\site-packages\MySQLdb\connections.py", line
> 33, 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 ''tagevents'
> limit 10' at line 1")
> 
> 
> Hmm, despite the fact, that it is bad style, I tried:
> 
> 
>>>>cur.execute("select id from %s limit 10;" % "tagevents")
> 
> 10L
> 
> 
> and succeeded.
> 
> Looks like MySQL doesn't like the quoting, MySQLdb seems to perform.
> 
> Okay, as you shouted to me "BAD STYLE" I presume, it had to work, if I only
> did it right. So, what am I doing wrong? Or did I misconfig MySQL? Is MySQL
> 4.0 not yet supported?
> 
> My environment:
> 
> Win XP
> 
> Python 2.3.4 (#53, May 25 2004, 21:17:02) [MSC v.1200 32 bit (Intel)] on
> win32
> Type "help", "copyright", "credits" or "license" for more information.
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 7 to server version: 4.0.20a-nt
> 
>  MySQLdb 1.0.0
> 
> 
> Kind regards
> Franz GEIGER
> 
[snip]

That's because MySQLdb will take care of converting the *parameters* in 
a SQL statement. These are (almost) always in the WHERE clause.

What you are trying to do is generate the SQL statement dynamically - 
which you have to do yourself using string formatting as you have found 
out.

It's a subtle but very important distinction.

Processing a SQL statement is usually done in two parts; parsing and 
binding. Parsing is where the database engine figures out *where* to get 
the information requested in the statement (e.g. which files the rows 
from the tables are physically stored in), binding is when the parameter 
values you supply are used to figure out *what* to return.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/