Sqlite&Python: Exiting for loop

Question

Can someone please explain why the first loop gets exited, when the second loop is done. First i get all table names in database(Total 4 results) Then i want to get all data from that table.

But i only get the data from the first table for some reason. If i remove the loop that gets the data from the table, then it runs the first for loop all the way to the end.

#Get all tables in database file
for tablename in c.execute("SELECT name FROM sqlite_master WHERE type='table';"):
    print(tablename[0])

    for elementdate in c.execute('SELECT * FROM %s ORDER BY Date DESC' % tablename[0]):
        print(elementdate)

Output:
table_1
(1, '20120210', 360)
(2, '20100210', 204)
Loop Excited

Same code just without last for loop

#Get table names
for tablename in c.execute("SELECT name FROM sqlite_master WHERE type='table';"):
    print(tablename[0])

    #for elementdate in c.execute('SELECT * FROM %s ORDER BY Date DESC' % tablename[0]):
    #   print(elementdate)

Output:
table_1
table_2
table_3
table_4
Loop Excited

Have i found an error or am i just dumb?

Is c a database cursor? In this case, you are trying to nest multiple queries on the same cursor. — dhke
– dhke, Commented Mar 9, 2015 at 11:07

Eugene Soldatov · Accepted Answer · 2015-03-09 11:13:16Z

2

You shouldn't execute few queries in the same cursor before fetching results of first one:

c.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = c.fetchall()
for tablename in tables:
    print(tablename[0])
    c.execute('SELECT * FROM %s ORDER BY Date DESC' % tablename[0])
    for elementdate in c.fetchall():
        print(elementdate)

answered Mar 9, 2015 at 11:13

Eugene Soldatov

10.2k2 gold badges39 silver badges44 bronze badges

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

Comments

CL. · Accepted Answer · 2015-03-09 11:28:48Z

1

A single cursor object works only with a single query at a time; execute() overwrites any previous results.

If you want to execute two queries at the same time, use two cursors:

c = db.cursor()
c2 = db.cursor()
for row in c.execute("SELECT name FROM sqlite_master WHERE type='table'"):
    tablename = row[0]
    for row2 in c2.execute("SELECT * FROM %s ORDER BY Date DESC" % tablename):
        ...

Note: it would be a bad idea to modify the table while some other query on it is still running.

answered Mar 9, 2015 at 11:28

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

RasmusGP Over a year ago

I see, never actually knew what the cursor was for, but now it makes sense.

Collectives™ on Stack Overflow

Sqlite&Python: Exiting for loop

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related