MySqlDb - Incorrect value returned by select.

[xav.vijay]

Hello All

I am having an odd issue with mysqldb connection from python to a database.
The issue is, when I run the query directly or via a sql client like navicat or toad, I get the required value, but this does not happen via python/mysqldb combo.

This is the query which i am running..

SELECT count(*) FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND `status` ="3";

The above query returns the value 1 from toad. but from python it is returning Zero.

>>> cursor.execute ('SELECT count(*) FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND `status` = "3"')
1L
>>> cursor.fetchone()
(0L,)

I am not sure, where the issue is ... can someone help?

Regards

Antonio.

[TrustyTony]

I see that it returns 1, at line 2.

[woooee]

Since it is a count, fetchone will not return anything. You could also fetchall, on a straight select, and check the length of the returned list if that is more straight forward.

[xav.vijay]

>>> cursor.execute ("SELECT count(*) FROM hesk_tickets WHERE `status` = '2' or `status` = '4' AND category = 4")
    1L
    >>> result=cursor.fetchone()
    >>> result
    (13L,)


    >>> cursor.execute ('SELECT count(*) FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND `status` = "3"')
    1L
    >>> cursor.fetchone()
    (0L,)
    >>> cursor.fetchall()
    ()
    >>> cursor.execute ('SELECT * FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND `status` = "3"')
    0L
    >>> cursor.fetchall()
    ()

Actually the 1L returned seems to be the same for any query. More like whether the query worked or not, but not sure.
fetchall didnt work either .. :(

Regards
Antonio

[TrustyTony]

cursor.execute ('SELECT count(*) FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND status = "3"')

AS I understand wooeee means to select not count(*) but * and to do len(cursor.fetchall())

Also using a class to wrap the SQL seems to be common: http://python.about.com/od/pythonanddatabases/ss/mymysqlread1_3.htm

[xav.vijay]

Hi

I have done as per wooee has mentioned. In my last post I have given the query as select * instead of of select count(*), but it returns me a null value.

>>> cursor.execute ('SELECT * FROM hesk_tickets WHERE lastchange BETWEEN DATE_SUB(NOW(), INTERVAL 9 HOUR)AND NOW() AND category = 4 AND `status` = "3"')
0L
>>> cursor.fetchall()
()

I am trying to see if the query works from the python command line, before I could incorporate it in a class. But since it returns an incorrect value from command line itself, I am not sure defining it in a class would make a difference.

Regards..

Antonio.

[TrustyTony]

Are you sure that status is string and category is numeric? I think the backquotes are not needed for names without special characters in them in SQL.