Update sql db table columns using another column value

Question

I have this addresses TABLE of my patients and one the column has as integer the number of days since I have last seen the patients. I need this to be updated using the current date, which is not a problem to get, and another column value from the same TABLE.

I was hoping I could do something like:

      c.execute("""UPDATE addresses SET
      days_since_last_appointment = :day""",
      {
      'day': (datetime.now()-       last_appointment_day)
       })

I'm using Python 3.9 and sqlite3

Any idea?

ValueError: time data '.!entry4' does not match format '%d%m%Y — tish, Dec 14 '21 at 18:21
I think the main issue is to access another column string value while updating one column — tish, Dec 14 '21 at 18:25

score 0 · Answer 1 · answered Dec 14 '21 at 18:19

0

You can't refer to a table column as if it were a Python variable.

You can do the arithmetic in the SQL query itself:

c.execute("""UPDATE addresses
SET days_since_last_appointment = julianday('now') - julianday(last_appointment_day)""")

See Difference between 2 dates in SQLite

answered Dec 14 '21 at 18:19

Barmar

741,623
53
500
612

Thank you, this seems that it's gonna be working, I'm now getting a database is locked error, just saw online that I neeeded to increase the timeout but no idea how do that. Where to put that OPTIONS thing – tish Dec 14 '21 at 18:34
https://stackoverflow.com/questions/151026/how-do-i-unlock-a-sqlite-database – Barmar Dec 14 '21 at 18:35
well mate. Thanks a lot! Have a nice day – tish Dec 14 '21 at 18:47

Update sql db table columns using another column value

1 Answers1