MySQLdb and MySQL stored functions

Discussion in 'Python' started by kurt.forrester.fec@googlemail.com, Feb 3, 2009.

  1. Guest

    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=`root`@`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
    , Feb 3, 2009
    #1
    1. Advertising

  2. Guest

    On Feb 3, 8:28 am, Dennis Lee Bieber <> wrote:
    > On Mon, 2 Feb 2009 23:28:05 -0800 (PST),
    > 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
    >                      
    >                 HTTP://wlfraed.home.netcom.com/
    >         (Bestiaria Support Staff:               )
    >                 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
    , Feb 3, 2009
    #2
    1. Advertising

  3. Steve Holden Guest

    wrote:
    > On Feb 3, 8:28 am, Dennis Lee Bieber <> wrote:
    >> On Mon, 2 Feb 2009 23:28:05 -0800 (PST),
    >> 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
    >>
    >> HTTP://wlfraed.home.netcom.com/
    >> (Bestiaria Support Staff: )
    >> 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
    > --

    try:
    ...
    except MySQLdb.Warning:
    pass

    There is probably some setting you can establish with MySQL so it
    doesn't raise these warnings: I have never seen one, and zero rows is a
    perfectly valid retrieval result.

    regards
    Steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
    Steve Holden, Feb 3, 2009
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JZ
    Replies:
    3
    Views:
    432
    Gabriel Cooper
    Oct 22, 2004
  2. Dave Merrill
    Replies:
    7
    Views:
    420
    Dave Merrill
    Nov 29, 2004
  3. Replies:
    12
    Views:
    1,448
    Claudio Grondi
    Feb 28, 2006
  4. Sean O'Donnell
    Replies:
    2
    Views:
    335
    Sean O'Donnell
    Jan 5, 2007
  5. Shiraz
    Replies:
    14
    Views:
    234
    Shiraz
    Jan 9, 2006
Loading...

Share This Page