1

I am trying to make a ChatBot but I've run into a small problem. I have a nested or loop in python and it's supposed to cycle through a sqlite table that had all the names of other table. The inner for loop cycles through the table inside the first one, iterating over cells.

for i in c.execute("""SELECT * FROM triggers_sql"""):
    for l in c.execute("""SELECT * FROM "{}" """.format(i)):
        print(i, l)

However, for some reason, the outer for loop only cycles through the first cell of the first table. I can't think of anything that I've done wrong here.

Improve this question
1
  • Don't use dynamic table names, those are almost always an indication of bad database design. Commented Oct 8, 2018 at 7:55

1 Answer 1

Reset to default
2

You need to use two separate cursors to do that. A cursor represents a single result set, the inner c.execute() clears the result set that the outer loop attached to the cursor.

If c is a connection object, you need to explicitly create cursors from that:

outercursor = c.cursor()
for tablename, in outercursor.execute("SELECT tablename FROM triggers_sql"):
    innercursor = c.cursor()
    for row in innercursor.execute('SELECT * FROM "{}"'.format(tablename)):
        # ...

If c is a cursor object, just create another one from the connection. You can even do so from an existing cursor with:

innercursor = c.connection.cursor()

Note that there are probably better ways of structuring your database where you don't have to use dynamic table names in the first place. Store everything that you now use separate tables for in a single table with one extra column replacing the tablename. At that point you can use a JOIN and leave it to sqlite to worry about how to produce the looping.

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

4 Comments

So how do I get two cursors? Preferably the most simple, but effective, way.
@HarryLe: how did you get one cursor to begin with? Or is c a connection object?
c is the cursor.
@HarryLe: then look at how you created that cursor. Create another one. You are not limited to just one cursor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.