MySQLdb and MySQL stored functions
kurt.forrester.fec at googlemail.com
Mon Feb 2 23:35:48 CET 2009
I am running
- Ubuntu 8.10
- Python 2.5.2
- MySQLdb (1, 2, 2, 'final', 0)
- MySQL Server/Client 5.0.67
I am trying to write an authentication script for a python application
that connects to a MySQL database. The database has a table named `user`
which has the fields `id`, `alias` and `password` as well as a stored
function `authenticate` as detailed below:
CREATE DEFINER=`root`@`localhost` FUNCTION `authenticate`(a TEXT, p
TEXT) RETURNS int(11)
DECLARE STATUS INT DEFAULT -1;
SELECT id INTO STATUS FROM user WHERE alias = a AND password = p;
`id` = 1
`alias` = 'captain'
`password' = 'a'
I have been executing the following query from various connections:
`SELECT authenticate('captain', 'a')` (this is what is in the table and
should return 1)
`SELECT authenticate('captain', 'aa')` (this is a incorrect version of
the password and should return -1)
I have tried running this query from the MySQL Query Browser and it
returns results as expected.
I have also tried query from python using the _mysql module and this
also returns results as expected.
However, when I try to use the MySQLdb module it returns an incorrect
value (it returns 1).
I wish to use the DB API 2.0 compliant module for flexibility. Therefore
I am trying to work out why the MySQLdb does not return the value as
expected (that is as it is returned by the Query Browser).
Any help would be greatly appreciated.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Python-list