Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

fetchone() compare to a string

Help
damaex
2011-07-22
2012-09-19
  • damaex
    damaex
    2011-07-22

    Hi!

    how can i compare the result of a fetchone() with a string?

    this is my query and fetch PetName is a string.

    cursor.execute("SELECT AnimalName FROM Animals WHERE UPPER(AnimalName) = '%s'"
    % (PetName.upper()))

    max = cursor.fetchone()

    the print statement and the return:

    print "Animal:%s Pet:%s" % (max, PetName)

    Animal: Pet: Hamster

    now i want to compare the two:

    if max == PetName:

    match +=1

    match will never increment :(

    i tried max but got the error:

    TypeError: 'NoneType' object is unsubscriptable

    any suggestions or hints?

     
  • Andy Dustman
    Andy Dustman
    2011-07-22

    If max is None, that means you hit the end of the result set. fetchone()
    returns a tuple for each row returned.

    The real problem is your query. Rewrite it like this:

    cursor.execute("SELECT AnimalName FROM Animals WHERE AnimalName = %s", (PetName,)
    

    MySQL is normally case-insensitive (depends on the default character set and
    collation you have configured). But more importantly, you were not passing
    parameters correctly, which is why you got None instead of a row (tuple).

     
  • damaex
    damaex
    2011-07-22

    I receive a error when writing the query like this:

    _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 'Monkey's' at line 1

    I have to query with the '%s', no?

     
  • Andy Dustman
    Andy Dustman
    2011-07-22

    No.

    You have to lose the Python % operator in your original query. Even though
    MySQLdb uses %s as a parameter placeholder, it is not doing straight string
    substitution. The values you pass are quoted as needed.

     
  • Andy Dustman
    Andy Dustman
    2011-07-22

    I'd also say, since you are only testing for the presence of that row in the
    database, you can and probably should do this:

    If max:
        print max[0] # the name, but not necessary to test it, the query did that
        match += 1
    
     
  • damaex
    damaex
    2011-07-22

    okay i use now this query:

    cursor.execute("SELECT AnimalName FROM Animals WHERE AnimalName = %s",
    (PetName.upper(),))

    max is still giving a: TypeError: 'NoneType' object is unsubscriptable

    the print of max is and not the string Hamster

    == Hamster is still not true

    is the data in my database the reason?

     
  • Andy Dustman
    Andy Dustman
    2011-07-22

    fetchone() returns tuples, not strings, even if you only selected a single
    column. When it returns None, you've come to the end of the result set.

    I recommend reading http://www.python.org/dev/peps/pep-0249/

     
  • damaex
    damaex
    2011-07-22

    i understand the concept of how the return of fetchone() works but shouldn't I
    be able to access the first item in a tuple via tuple?

     
  • Andy Dustman
    Andy Dustman
    2011-07-22

    Yes, you should, unless fetchone() returned None, which it will if eventually
    if you call it enough times.

     
  • damaex
    damaex
    2011-07-22

    Thank you. I appreciate your help!