Updating SQLite table with Python function using table columns as arguments

Question

I am trying to UPDATE all records of a table with the results of a function which uses the other rows of the table as arguments. However, the result of this operation for all records is repeated from the first record. Can anyone explain why this might be the case?

def fun(a,b,c,d):
    return a + b + c + d

cur = conn.cursor()

cur.execute("SELECT field1, field2, field3, field4 FROM TABLE1")

for row in cur:
        cur.execute("UPDATE TABLE1 SET field5 = ?", (fun(row[0],row[1],row[2],row[3]),))

The completed table looks like this:

field1, field2, field3, field4, field5
4, 3, 2, 1, 10
7, 3, 1, 0, 10
8, 5, 2, 0, 10

When it should look like this:

field1, field2, field3, field4, field5
4, 3, 2, 1, 10
7, 3, 1, 0, 11
8, 5, 2, 0, 15

icktoofay · Accepted Answer · 2013-07-21 23:35:35Z

2

First, you select all of the rows:

SELECT field1, field2, field3, field4 FROM TABLE1

This yields this result set in cur:

4, 3, 2, 1
7, 3, 1, 0
8, 5, 2, 0

You begin to iterate through the rows. You start with the first row:

4, 3, 2, 1

You add all the columns together, correctly yielding the result 10. Then you execute this SQL statement:

UPDATE TABLE1 SET field5 = 10

Whoa, there! There's no where clause! You've just changed field5 of every single row! That's problem #1: you need to add a where clause. Most tables have a primary key, so if you have a primary key, you'll probably want to add a clause like where id = ?. If you don't have a primary key, probably the best you can do is include all the other columns, e.g.

update table1
set field5 = ?
where field1 = ?
and field2 = ?
and field3 = ?
and field4 = ?

Make sure that you provide a value for each ? in the execute call.

So you've finished executing your update statement on the cur cursor. You go to iterate again… and there's no more rows. Why? Because that update statement changed the result set of the cursor, discarding the remaining rows of the select. You need to run your updates on a different cursor or fetch all the rows before you move on to updating.

edited Jul 21, 2013 at 23:35

answered Jul 21, 2013 at 22:26

icktoofay

130k23 gold badges261 silver badges239 bronze badges

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

5 Comments

baobobs Over a year ago

Using a second cursor makes sense and is easy to apply. However, could you clarify what the where clause should be stating? Something like 'where record count += 1'?

icktoofay Over a year ago

@user1185790: You'll have to find some set of columns to uniquely identify that row. I've updated my answer to clarify that.

baobobs Over a year ago

Ok, I'll accept your answer because I'm sure you're right. Unfortunately, I'm encountering the error message "sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied." when applying the following: cur = conn.cursor(), cur.execute("SELECT field1, field2, field3, field4 FROM TABLE1"), cur1 = conn.cursor(), for row in cur: cur1.execute("UPDATE TABLE1 SET field5 = ? WHERE field1 = ?", (fun(row[0],row[1],row[2],row[3]),)). I'm sure it's some silly issue I'm overlooking, so I'll try to diagnose that problem since it's a separate issue

icktoofay Over a year ago

@user1185790: When you have two ? in the query, you need another thing in the tuple passed as the second argument to execute, e.g., cur1.execute("update table1 set field5 = ? where field1 = ?", (calculate(...), row[0])).

baobobs Over a year ago

Ah! I got it. Thank you so much for your patience. This was wrapped in a tuple and it worked: (fun(int(row[0]),int(row[1]),int(row[2]),int(row[3])),row[0]))

Zberteoc · Accepted Answer · 2014-02-07 14:42:50Z

0

I know this is an old and solved issue but I can't help it:

That is the worse way to update a table with values form its own columns. All you need to do is one single update statement executed in the database:

UPDATE table SET 
field1 = function(params1), 
field2=function(params2),   
field3=function(params3),   
field4=function(params4)
WHERE
<condition>

The where clause is not needed if you want to do that for all the rows in your table. The only thing you need to do is to define a user function within your database, which is pretty similar with what you would do in python.

This way the update will be about ... 1000 times faster, with no exaggeration.

answered Feb 7, 2014 at 14:42

Zberteoc

1

Comments

Mohamed Salah · Accepted Answer · 2021-01-24 12:59:10Z

0

Simply add the function to your database:

conn = sqlite3.connect("database.db")

def fun(a,b,c,d):
    return a + b + c + d

conn.create_function(
cur = conn.cursor('fun', 4, fun)
cur = conn.cursor()
cur.execute("UPDATE table SET field5 = fun(field1, field2, field3, field4)")

answered Jan 24, 2021 at 12:59

Mohamed Salah

931 silver badge8 bronze badges

Collectives™ on Stack Overflow

Updating SQLite table with Python function using table columns as arguments

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related