Member Avatar for 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.

Edited by xav.vijay
  • 3 Contributors
  • 6 Replies
  • 637 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by TrustyTony

Dani AI

Generated

A focused triage checklist and a few concrete checks to explain why a client (Navicat/Toad) and a Python/MySQLdb session can return different counts.

First, the cursor.execute() return value is the number of result rows (COUNT(*) returns one row), not the numeric count itself; use count = cursor.fetchone()[0] to get the scalar. That aside, the mismatch you describe (client shows 1 but Python shows 0) usually comes from one of three causes: different connection/schema, transaction snapshot (autocommit/isolation), or time/column-type differences.

Quick diagnostic queries to run on both connections (replace with your client of choice):

SELECT DATABASE(), USER(), @@hostname, @@session.time_zone;
SHOW COLUMNS FROM hesk_tickets LIKE 'lastchange';
SHOW VARIABLES LIKE 'sql_mode';

If lastchange is a TIMESTAMP, session time_zone matters; if it is DATETIME, it does not. To inspect actual stored timestamps:

SELECT UNIX_TIMESTAMP(lastchange), lastchange
FROM hesk_tickets
WHERE category = 4
ORDER BY lastchange DESC
LIMIT 5;

Transaction/snapshot: MySQLdb connections often do not run in autocommit; an open transaction with REPEATABLE READ will keep a snapshot and will not see rows committed after the transaction began. Refresh the snapshot by calling connection.commit() (or close and reconnect), or enable autocommit for testing.

Quoting and modes: avoid double quotes for string literals (use single quotes) — in ANSI_QUOTES mode double quotes act like identifier quotes and can change semantics. Also confirm column types and values (numeric vs string) so comparisons are explicit.

Tiebacks: ’s suggestion about fetchall/len is relevant for row counts on SELECT *, but for a scalar COUNT(*) you need the first column of fetchone(); ’s pointer to identifiers/quoting is also apt. The above checks (same database, transaction snapshot, column type/timezone, correct literal quoting) will locate the root cause in nearly all cases.

Recommended Answers

All 6 Replies

Member Avatar for TrustyTony

I see that it returns 1, at line 2.

Member Avatar for 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.

Edited by woooee
Member Avatar for 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

Edited by xav.vijay
Member Avatar for 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:

Edited by TrustyTony
Member Avatar for 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.

Edited by xav.vijay
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.