How to update row by row using python function in sqlite3?

Question

This question is duplicated I think, but I couldn't understand other answers...

Original table looks like this.

NAME    AGE SMOKE
John    25  None
Alice   23  None
Ken     26  None

I will update SMOKE column in these rows,
But I need to use output of function which was coded in python check_smoke(). If I input name to check_smoke(), then it returns "Smoke" or "Not Smoke".
So final table would look like below:

NAME    AGE SMOKE
John    25  Smoke
Alice   23  Not Smoke
Ken     26  Not Smoke

I'm using sqlite3 and python3. How can I do it? Thank you for help!

Easy way is to create a user defined function you can use in SQL statements. See the python sqlite3 module documentation for details and an example. — Shawn
– Shawn, Commented Mar 23, 2020 at 21:33
@Shawn Actually that function is external library and so complicated(connect to online and get data) that i cannot implement in SQL... Thank you for answering, but is there any other way? — berry2971
– berry2971, Commented Mar 23, 2020 at 21:38
If you can call it from python, you can wrap it in a SQL user defined function. — Shawn
– Shawn, Commented Mar 23, 2020 at 21:59
@Shawn Ah I see. I think I have to read documentation again. Thank you. — berry2971
– berry2971, Commented Mar 23, 2020 at 22:58

Serge Ballesta · Accepted Answer · 2020-03-23 22:07:13Z

1

You could use 1 cursor to select rows and another one to update them.

Assuming that the name of the table is smk (replace it by your actual name) and that con is an established connection to the database, you could do:

curs = con.cursor()
curs2 = con.cursor()
batch = 64            # size of a batch of records
curs.execute("SELECT DISTINCT name FROM smk")
while True:
    names = curs.fetchmany(batch)                             # extract a bunch of rows
    if len(names) == 0: break
    curs2.executemany('UPDATE smk SET smoke=? WHERE name=?',  #  and update them
              [(check_smoke(name[0]), name[0]) for name in names])
con.commit()

answered Mar 23, 2020 at 22:07

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

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

2 Comments

berry2971 Over a year ago

Perfect answer. Thank you.

Semmel Over a year ago

I'm also using this method. But it really feels like it has to be a better - more pythonic - way.

Collectives™ on Stack Overflow

How to update row by row using python function in sqlite3?

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