Weird result with sqlite in python

March 2, 2023

I’m trying to get a count of document where the email of my user appear, I’m doing this for every user so I use a loop which takes every email of my user that got a document (to avoid having people with 0 document).

So I know for sure that i got 5 row (5 email with document) and in this row I do a select count(*) to get the number of document but it stop at the first without returning any error and when I get ride of the select it do the whole 5 row.

I’m already using select in row in other part of my program so I don’t understand why it doesn’t work here.

my code is like this:

def fAnalyseProd(self):
    for row in self.cur.execute("SELECT DISTINCT emailContact,idContact FROM contact JOIN REX ON idContact=redacteur"):

        print(row) # Returns the email + it's id

        emailContact = str(row[0])
        emailContact = emailContact.split("@")
        emailContact = emailContact[0].split(".")

        print(str(row[1])) # the id only

        self.cur.execute("SELECT count(*) FROM REX WHERE redacteur = ?", (str(row[1]),))
        totalREX = self.cur.fetchone()[0]
        print(totalREX)

I cutted some code but it’s all tkinter GUI I precise that every code that I write after the print(totalREX) will execute but it stops at only one iteration whereas if i get ride of it I got my 5 row and I don’t have any error displayed or anything like that.

>Solution :

You’re using the same cursor to execute a query while you’re still iterating over the first query’s results.

Don’t do that; use two separate cursors instead (or, as in here, use the Connection.execute() utility that does that for you):

def fAnalyseProd(self):
    for row in self.conn.execute("SELECT DISTINCT emailContact,idContact FROM contact JOIN REX ON idContact=redacteur"):

        print(row) # Returns the email + it's id

        emailContact = str(row[0])
        emailContact = emailContact.split("@")
        emailContact = emailContact[0].split(".")

        print(str(row[1])) # the id only

        res = self.conn.execute("SELECT count(*) FROM REX WHERE redacteur = ?", (str(row[1]),))
        totalREX = res.fetchone()[0]
        print(totalREX)