compare an array to a values returned from mysql database using Python

Question

When I use this script to get how many time each element in an array A is repeated in an array B I get the correct results

A = ['text1','text2','text3','text4']
B = ['text1','text2','text3','text4','text1','text2','text3','text4','text1','text2','text3','text4','text1','text2','text3','text4','text1','text2','text3','text4','text3','text4']
for c in A :
    countVar = 0
    for e in B :
        if c == e :
            countVar +=1

    print c     
    print countVar

But when the B array in this case is a return from a SQL query is does pull the correct number for the first elemt in this case is text1 and it pull 0 for the other elements count.

cursor = db.cursor() 
cursor.execute("select * from Table")

A = ['text1','text2','text3','text4']

for element in A:
    #print element 

    for row in cursor.fetchall() :

        if row[0] == element :
            #Count
            countClient += 1
    print row
    print countClient
    countClient = 0

The results are like: knowing that I have text2 and text3 in my table
text1
15
text2
0
text3
0
text4
0
Any one know why this is happening? or suggesting another way to do so? I'm also working on doing other calculation like calculating the SUM of the value returned from the SQL array, and AVG because I'm working on some data processing.

Thanks in advance!

David Kiger · Accepted Answer · 2013-03-06 04:29:46Z

1

My reading of the documentation is that fetchall() won't return anything after the first loop. For debugging, you should check that it actually enters that inner for loop on the second (and later) passes.

To fix this with only minor modifications to your code, you have a few options that I can think of:

Loop through with a fetchall, storing it in a new array, and use that in your current code.
Call execute each time.
Reset the cursor pointer. Not sure if this is possible in Python, but a quick search isn't turning anything up.

Sorry I can't give more specifics, but Python isn't a language I'm all that familiar with.

--

For the first option, something along the lines of (pseudocode):

i = 0
C = []    
for row in cursor.fetchall() :
    c[i] = row[0]
    i += 1

And then in your inner loop, do a for el in C instead of for row in cursor.fetchall()

edited Mar 6, 2013 at 4:29

answered Mar 6, 2013 at 3:15

David Kiger

1,9961 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mongotop Over a year ago

Thank you so much for the reply David!!! You are right, it's not looping on the second time, can you please suggest another method to replace 'fetchall()'? Thanks a lot again!!!

David Kiger Over a year ago

See my latest edit. It was easier than trying to squeeze it in a comment block.

mongotop Over a year ago

You are correct, I was just trying to modify you answer :) that what I just thought about and it works perfect! you are awesome David! thank you so much!!!!

mongotop Over a year ago

can you explain you idea about : Loop through with a fetchall, storing it in a new array, and use that in your current code. because that will be more officiant because I'm dealing with millions rows. Thanks a lot!!!

mongotop Over a year ago

You are a great guy!!! thank you so much!!!!! because python doesn't support ++ i will change it to i +=1

Collectives™ on Stack Overflow

compare an array to a values returned from mysql database using Python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related