CODEWITHSUNDEEP
pythonsqlite
pencilNero
pencilNero

Reputation: 83

sqlite & python - only pulls the first result

This is pretty strange (admitedly, this is my first attempt with python / sqlite), but I can seem to get all of the rows if I do a fetchAll(), but other than that - no matter what I try, always ends up in the db only returning the first row - the second iteration stops because a null is returned. Wondering if there is something wrong with how I am coding this up in python? The db seems ok.. 

con = sqlite3.connect('backup.db')
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute('select * from tb1;')

for row in cur:
    try:
#       row = dataCur.fetchone()
        #if row == None: break
        print type(row)

        print ' Starting on: %i' % row[0]

        cleaner = Cleaner(scripts=True, remove_tags=['img'], embedded=True)
        try:
            cleaned = cleaner.clean_html(row[2]) #data stored in second col
            cur.execute('update tb1 set data = ? where id = ?;', (cleaned, row[0]))
        except AttributeError:
            print 'Attribute error'

        print ' Ended on: %i' % row[0]

    except IOError:
        print 'IOexception'

Upvotes: 5

Views: 2107

Answers (3)

nitwit
nitwit

Reputation: 586

You have to execute a command/SQL query before each time you fetch data. fetchall(), fetchone(), etc. only grab what's is already executed; therefore when you execute once you can't fetch more than once unless you have already executed a query.

Upvotes: 1

Epcylon
Epcylon

Reputation: 4727

If I'm not mistaken, the second time you call execute on a Cursor, the result of the previous call is discarded.

That's why you can use fetchall, because that returns all the results, before you call execute again.

For the update, use a second Cursor, to avoid invalidating the results on the first.

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 838116

You are reusing the same cursor to execute two different queries. Try using two different cursors and see if that solves your problem.

Upvotes: 10

Related Questions