Python SQLite3: want to iteratively fetching rows, but code is pulling every other row

Question

I'm trying to accomplish a very simple task:

Create a table in SQLite
Insert several rows
Query a single column in the table and pull back each row

Code to create tab:

import sqlite3
sqlite_file = '/Users/User/Desktop/DB.sqlite'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

c.execute('''CREATE TABLE ListIDTable(ID numeric, Day numeric, Month
numeric, MonthTxt text, Year numeric, ListID text, Quantity text)''')


values_to_insert = [
(1,16,7,"Jul",2015,"XXXXXXX1","Q2"),
(2,16,7,"Jul",2015,"XXXXXXX2","Q2"),
(3,14,7,"Jul",2015,"XXXXXXX3","Q1"),
(4,14,7,"Jul",2015,"XXXXXXX4","Q1")]     #Entries continue similarly

c.executemany("INSERT INTO ListIdTable (ID, Day, Month, MonthTxt,
Year, ListID, Quantity) values (?,?,?,?,?,?,?)", values_to_insert)

conn.commit()
conn.close()

When I look at this table in SQLite DB Browser, everything looks fine.

Here's my code to try and query the above table:

import sqlite3
sqlite_file = '/Users/User/Desktop/DB.sqlite'
conn = sqlite3.connect(sqlite_file)
conn.row_factory = sqlite3.Row
c = conn.cursor()

for row in c.execute('select * from ListIDTable'):
    r = c.fetchone()
    ID = r['ID']
    print (ID)

I should get a print out of 1, 2, 3, 4. However, I only get 2 and 4.

My code actually uploads 100 entries to the table, but still, when I query, I only get ID printouts of even numbers (i.e. 2, 4, 6, 8 etc.).

Thanks for any advice on fixing this.

mgilson · Accepted Answer · 2016-12-01 04:24:11Z

2

You don't need to fetchone in the loop -- The loop is already fetching the values (one at a time). If you fetchone while you're iterating, you'll only see half the data because the loop fetches one and then you immediately fetch the next one (without ever looking at the one that was fetched by the loop):

for r in c.execute('select * from ListIDTable'):
    ID = r['ID']
    print (ID)

answered Dec 1, 2016 at 4:24

mgilson

312k70 gold badges656 silver badges722 bronze badges

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

Collectives™ on Stack Overflow

Python SQLite3: want to iteratively fetching rows, but code is pulling every other row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related