How to update python SQLite rows while iterating through a table

Question

I'm a newbie at SQLite for python and I'm confused about how to update a row while iterating through a table.

Here is some sample code:

import sqlite3 as lite

points = [
    (1,2,3),
    (4,5,6),
    (7,8,9),
    (10,11,12)
]

point_data = lite.connect('points.db')
cur = point_data.cursor()

cur.execute("DROP TABLE IF EXISTS points")
cur.execute("CREATE TABLE points(x1,y1,z1)")
cur.executemany("INSERT INTO points VALUES(?, ?, ?)", points)

cur.execute("SELECT * FROM points")

while True:
    row = cur.fetchone()
    if row == None:
        break
    row_sum = row[0]+row[1]+row[2]

I would like to know how to insert the row_sum as the fourth column using an UPDATE command. Do I have to define the fourth 'column' when I define the table?

Yes. You can also add column later with a query, but if you are sure you need 4 columns, just define all columns when you create the table. I'm not sure if i understand your question... it's so strange.. — maurelio79
– maurelio79, Commented Jan 14, 2014 at 17:12

Daniel Roseman · Accepted Answer · 2014-01-14 17:13:18Z

3

Yes, but you wouldn't do that in Python.

SQL databases have a fixed structure, you would need to create a total column in the CREATE TABLE statement, or add it via an UPDATE TABLE... ADD COLUMN statement.

But then, instead of SELECT followed by UPDATE, you could just do a single UPDATE:

UPDATE points SET total = (x1 + y1 + z1);

Note however that you might not actually need to store the total in the db at all: you could just calculate it dynamically in exactly the same way when you do the SELECT:

SELECT x1, y1, z1, x1+y1+z1 as total FROM points;

answered Jan 14, 2014 at 17:13

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

2 Comments

HotDogCannon Over a year ago

thanks for this. if the column were already defined, how can I update just one element of that row? i don't want to update all rows. is there a built-in row ID that I can use?

Daniel Roseman Over a year ago

No, you should define a primary key column for that (and then add WHERE id = my_id_number to the update statement). To be honest, this is all really basic SQL stuff (and nothing to do with Python at all): you should go and do an introductory SQL tutorial.

Collectives™ on Stack Overflow

How to update python SQLite rows while iterating through a table

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