Python & SQLite: Loop through rows only selecting first line

Question

I am trying to loop through a series of rows in an SQLite DB using the following code:

cur.execute("SELECT * FROM SmallBodyDB")
count = 0
for row in cur:

    - Some code -

    conn.commit()
    count = count + 1

The code in - some code - works fine, but will only look at the first line. Basically, it will only loop once. I need it to loop through the whole DB.

Any ideas why?

Cheers

@JamesMcPherson I am just using it to count up how many loops have been iterated. — GalacticPonderer
– GalacticPonderer, Commented Jun 4, 2020 at 1:09
Do you need it though? Assuming that you do, then try something like for count, row in enumerate(cur.fetchall()): which gives you the count for pretty much no extra cost — James McPherson
– James McPherson, Commented Jun 4, 2020 at 1:12
That is a good idea, thank you. However, when I just ran it on a DB with 21 lines of data in it, it returned a value of 20. Any idea why? — GalacticPonderer
– GalacticPonderer, Commented Jun 4, 2020 at 1:16

James McPherson · Accepted Answer · 2020-06-04 01:09:02Z

2

You need to fetch all the rows from the cursor:

for row in cur.fetchall():
    ... per-row operations
    conn.commit()

Though that will, of course, slow you down since you're committing the implicit transaction for every row. You could probably (since this is sqlite) run conn.commit() after the conclusion of your for loop.

answered Jun 4, 2020 at 1:09

James McPherson

2,5861 gold badge14 silver badges17 bronze badges

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

2 Comments

GalacticPonderer Over a year ago

That worked. Thank you. Regarding the comment about conn.commit(), what is the ballpark limit of the number of lines that can be looped through before it has to be run, supposing it is being run on a good laptop (32GB RAM)? 100s, 1000s, or 10,000s?

James McPherson Over a year ago

That depends entirely on your confidence :-) I was writing a pipeline recently which had to update an Oracle DB, and my confidence limit was around 100,000 rows per commit.

Collectives™ on Stack Overflow

Python & SQLite: Loop through rows only selecting first line

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related