Updating rows in a SQLite database using Python

Question

I have the following problem: I want to update an existing SQLite database row by row. What's happening now is that the iterator updates all existing rows with the last assigned value of dbdata.

I don't want that. I want update row 1 with the first assigned value of dbdata. Then take iterator shall go "up" again, get the next value and the updating should go on to the next row. Obviously there is a problem with the logic but I cannot get my head around it.

Whats happening now is that the rows are updated now which leaves me with the last assigned value of dbdatafor all rows. I only want one row to be updated per iteration. How do I tell Python to always "go one row down"? Can someone give a hint? I am not looking for a complete solution here. My current code is as follows:

for row in dbconnector:
    print (row)
    dbdata = langid.classify("{}".format(row))
    print (dbdata)
    connector.execute('''update SOMEDB set test1=? , test2=?''',(dbdata[-2], dbdata[-1]))

I am working with a SQLite Database and Python 3.3.

Not sure I'm getting what you're trying to achieve but it would seem to me that all you need is a double loop: one to iterate over the various rows, the other to iterate over the values of your dbdata variable (array? list? tuple?). — Priidu Neemre
– Priidu Neemre, Commented Aug 9, 2014 at 17:14

Spaceman Spiff · Accepted Answer · 2014-08-09 17:12:24Z

1

The reason all your data is set to the last dbdata is because your update isn't restricted to a single row so on each iteration all the rows are set to whatever dbdata you just processed. To restrict your update use a where clause so the only row affected is the one you want.

answered Aug 9, 2014 at 17:12

Spaceman Spiff

9441 gold badge14 silver badges32 bronze badges

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

12 Comments

four-eyes Over a year ago

Yep. That is exactly the problem. I am aware of that. But I want all rows to be updated. First row with the first value of dbdata, second row with the second assigned value to dbdata. A where clause would restrict the process of updating to only one row, wouldnt it?

Spaceman Spiff Over a year ago

That's correct, that is what the loop is for. You update one row at a time, and then by looping through all the rows as you do, you set each row, it just happens one at a time.

Spaceman Spiff Over a year ago

Right now you change every row in somedb on each iteration of the loop. That is why all the values in somedb look like the last iteration of the loop. You need to just change one row per iteration of the loop.

four-eyes Over a year ago

thanks. I understand whats happening now but a where clause would restrict the updating process to only one row, right?

Spaceman Spiff Over a year ago

If it is hard coded. You need to have some way of identifying what row you want to update and then include that as part of the where clause. Some type of row count or something, I am not sure exactly what your table structure is so I can't say for sure but if the rows have a unique id that you can use that would be ideal. Essentially it needs to be a different where clause on each loop.

|

four-eyes · Accepted Answer · 2014-08-09 19:15:28Z

1

Solved it. Thanks for all the input!

n = 0
for row in dbconnector:
    print (row)
    dbdata = langid.classify("{}".format(row))
    print (dbdata)
        for amount in row:
        n += 1
        print (n)
        connector.execute('''update SOMEDB set test1=? , test2=? where rowid == ?''',(dbdata[-2], dbdata[-1], n))

That works! It alters with every iteration the number of the rowid.

answered Aug 9, 2014 at 19:15

four-eyes

12.7k37 gold badges134 silver badges258 bronze badges

Collectives™ on Stack Overflow

Updating rows in a SQLite database using Python

2 Answers 2

12 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Related