MySQL query parameter binding...

[Bruza]

This is driving me nuts... The very very simple SQL query below using
":parameter"
always gives me syntax error. However, the same query using constant
'1' then it
works fine. I hope this is not because some stupid mistake I made at
4:00 AM...

Can anybody help?

Thanks,

Ben

>>> c.execute('select * from t_test where c1=:c1', {'c1':1})
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=:c1
2007-11-09 04:53:34,953 INFO sqlalchemy.engine.base.Engine.0x..10
{'c1': 1}
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\orm\session.py", line 527, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, par
ams or {}, **kwargs)
File "C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py", line 779, in execute
return Connection.executors[c](self, object, multiparams, params)
File "C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py", line 789, in _execute_text
self.__execute_raw(context)
File "C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py", line 852, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0
], context=context)
File "C:\utils\Python24\lib\site-packages\sqlalchemy-0.4.0-py2.4.egg
\sqlalchem
y\engine\base.py", line 869, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) (1064, "You
have an e
rror in your SQL syntax; check the manual that corresponds to your
MySQL server
version for the right syntax to use near ':c1' at line 1") 'select *
from t_test
where c1=:c1' {'c1': 1}
>>> c.execute('select * from t_test where c1=1')
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10
select * from
t_test where c1=1
2007-11-09 04:53:44,515 INFO sqlalchemy.engine.base.Engine.0x..10 {}
<sqlalchemy.engine.base.ResultProxy object at 0x00AEE110>
>>>

[Michael Bayer]

MysqlDB uses "format" style bind parameters, i.e. "%s". if youd like
SQLAlchemy to convert ":c1" to an appropriate bind param for MySQL,
use c.execute(text('select * from t_test where c1=:c1'), {'c1':1}).

[Bruza]

By using "%s", does that mean MySQL does not support "binding" of
parameter
and will have to pass the entire SQL statement as one text string?

c.execute("select * from t_test where c1=%s" % '1234567')

works, but this means the parameter was first substituted into the
query string
(by Python) before sending it to MySQL. I think that is why people
uses ":c1"
notation in query so that parameters are sent as binary format
separated from
the original query string itself...

[jason kirtland]

Bruza wrote:
> By using "%s", does that mean MySQL does not support "binding" of
> parameter
> and will have to pass the entire SQL statement as one text string?
>
> c.execute("select * from t_test where c1=%s" % '1234567')

That should be a comma separating the bind values, not a % format operator:

c.execute("select * from t_test where c1=%s", '1234567')

This db-api uses '%s' notation as its placeholder marker. It's
equivalent to '?' or ':foo'. There's a whole mess of different possible
styles in db-api for specifying binds. If you use sqlalchemy's text(),
you can use ':foo' notation cross platform and not have to care about
the db-api's bind implementation or typos like the % above.

[Bruza]

Thanks for the explanation. I got it now. This is one more example
that the "S"
in SQL was never meant to stand for "Standard" :-)...

Ben