CODEWITHSUNDEEP
pythonmysqlpython-2.7
Bharat
Bharat

Reputation: 3000

MySQLDB query not returning all rows

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 RTM 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

Upvotes: 3

Views: 3188

Answers (2)

Bharat
Bharat

Reputation: 3000

I sort of figured out the problem. It was silly at the end. It was a race condition!

This is how my actual code was organized : 

 Code Block 1
 {code which calls an API which creates an entry in Accounts table &
 Creates corresponding entries in Product table(10 entries)}

......

Code Block2
{The code I had posted in my question}

The problem was that the API(called in Code Block 1) took a few seconds to add 10 entries into the Product table.

When my code(Code Block 2) ran a fetch query, all the 10 rows were not added and hence fetched somewhere between 0 to 6 rows(how much ever was added at that time).

What I did to solve this was made the code sleep for 5 seconds before I did the SQL queries:

Code Block 1
time.sleep(5)
Code Block 2

The reason why it worked when I hard coded the acc_id was that, the acc_id which I hard-coded was from a precious execution(each run returns a new acc_id). And the reason why it worked while stepping through a debugger was that manually stepping acted like giving it a sleep time.

It is a lesson for me to know a little about the inside working of APIs(even though they are supposed to be like a black box) and think about race conditions like this, the next time I come across similar issues.

Upvotes: 1

abarnert
abarnert

Reputation: 365707

This is not actually an answer, just an attempt to gather together all of the information from a chat with RBK that ruled out a bunch of potential problems, but still didn't come up with an explanation or a solution, in hopes that someone else can spot the problem or think of something else to try.

It's clearly something in this line:

cur.execute("SELECT * FROM Products WHERE account_id = %d" % acc_id)

Especially since putting 322 in place of acc_id fixes everything. (As proven below.)

There are actually two problems with that line, which could be getting in the way. You always want to use DB-API binding rather than string formatting (and the equivalent in any other language), to avoid SQL injection attacks, for correctness of escaping/conversion/etc., and for efficiency. Also, both DB-ABI binding and string formatting require a tuple of arguments, not a single argument. (For legacy reasons, a single argument often works, but sometimes it doesn't, and then it's just confusing to debug… better not to do it.) So, this should be:

cur.execute("SELECT * FROM Products WHERE account_id = %d", (acc_id,))

Unfortunately, after discussing this in chat, and having you try a bunch of things, we were unable to find what's actually wrong here. Summarizing what we tried:

So then, we tried:

cur.execute("SELECT COUNT(*) FROM Devices WHERE account_id = %s" , (333,)) 
print cur.fetchone()[0]

print 'account id =', acc_id
print type(acc_id)
cur.execute("SELECT COUNT(*) FROM Devices WHERE account_id = %s" , (acc_id,)) 
print cur.fetchone()[0]

The output was:

10
account id = 333
<type 'long'>
2

When run repeatedly, the last number varies from 0-6, while the first is always 10. There's no way using acc_id could be different from using 333, and yet it is. And just in case one query was somehow "infecting" the next one, without the first two lines, the rest works the same way.

So, there's no way using acc_id could possibly be different than using 333. And yet, it is.

At some point during the chat we apparently moved from Products to Devices, and from 322 to 333, but regardless, the tests shown above were definitely done exactly as shown, and returned different results.

Maybe he has a buggy or badly-installed version of MySQLDb. He's going to try looking for a newer version, or one of the other Python MySQL libraries, and see if it makes a difference.

My next best guess at this point is that RBK has inadvertently angered some technologically-sophisticated god of mischief, but I can't even think of one of those off the top of my head.

Upvotes: 1

Related Questions