MySQLDB query not returning all rows
- by RBK
I am trying to do a simple fetch using MySQLDB in Python.
I have 2 tables(Accounts & Products). I have to look up Accounts table, get acc_id from it & query the Products table using it.
The Products tables has more than 10 rows. But when I run this code it randomly returns between 0 & 6 rows each time I run it.
Here's the code snippet:
# Set up connection
con = mdb.connect('db.xxxxx.com', 'user', 'password', 'mydb')
# Create cursor
cur = con.cursor()
# Execute query
cur.execute("SELECT acc_id FROM Accounts WHERE ext_acc = '%s'" % account_num ) # account_num is alpha-numberic and is got from preceding part of the program
# A tuple is returned, so get the 0th item from it
acc_id = cur.fetchone()[0]
print "account_id = ", acc_id
# Close the cursor - I was not sure if I can reuse it
cur.close()
# Reopen the cursor
cur = con.cursor()
# Second query
cur.execute("SELECT * FROM Products WHERE account_id = %d" % acc_id)
keys = cur.fetchall()
print cur.rowcount # This prints incorrect row count
for key in keys: # Does not print all rows. Tried to directly print keys instead of iterating - same result :(
print key
# Closing the cursor & connection
cur.close()
con.close()
The weird part is, I tried to step through the code using a debugger(PyDev on Eclipse) and it correctly gets all rows(both the value stored in the variable 'keys' as well as console output are correct).
I am sure my DB has correct data since I ran the same SQL on MySQL console & got the correct result.
Just to be sure I was not improperly closing the connection, I tried using with con instead of manually closing the connection and it's the same result.
I did RTFM but I couldn't find much in it to help me with this issue.
Where am I going wrong?
Thank you.
EDIT: I noticed another weird thing now. In the line
cur.execute("SELECT * FROM Products WHERE account_id = %d" % acc_id), I hard-coded the acc_id value, i.e made it
cur.execute("SELECT * FROM Products WHERE account_id = %d" % 322) and it returns all rows
