MySQLdb and MySQL stored functions
Hello All,
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=`ro...@`localhost` FUNCTION `authenticate`(a TEXT, p
TEXT) RETURNS int(11)
BEGIN
DECLARE STATUS INT DEFAULT -1;
SELECT id INTO STATUS FROM user WHERE alias = a
AND password = p;
RETURN STATUS;
END
table: `user`
`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)
and
`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.
Kurt
--
http://mail.python.org/mailman/listinfo/python-list
MySQLdb and MySQL stored functions
Hello All,
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=`ro...@`localhost` FUNCTION `authenticate`(a TEXT, p
TEXT) RETURNS int(11)
BEGIN
DECLARE STATUS INT DEFAULT -1;
SELECT id INTO STATUS FROM user WHERE alias = a AND password = p;
RETURN STATUS;
END
table: `user`
`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)
and
`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.
Kurt
--
http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb and MySQL stored functions
On Feb 3, 8:28 am, Dennis Lee Bieber wrote: > On Mon, 2 Feb 2009 23:28:05 -0800 (PST), > kurt.forrester@googlemail.com declaimed the following in > comp.lang.python: > > > 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. > > Show the code! > > At a rough guess, given the lack of details... > > You forgot to .fetch() the result and are looking at the status code > from the .execute() > -- > Wulfraed Dennis Lee Bieber KD6MOG > wlfr...@ix.netcom.com wulfr...@bestiaria.com > HTTP://wlfraed.home.netcom.com/ > (Bestiaria Support Staff: web-a...@bestiaria.com) > HTTP://www.bestiaria.com/ Correct diagnosis. Thanks. Any ideas on how to suppress the warning output: __main__:1: Warning: No data - zero rows fetched, selected, or processed -- http://mail.python.org/mailman/listinfo/python-list
