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