python sqlite3 conditional query

Question

Sqlite3 table

CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    p1 INTEGER NOT NULL,
    p2 INTEGER NOT NULL,
    p3 INTEGER NOT NULL
)
INSERT INTO t (1, 11, 12, 13)

Python

def update(id, p1=None, p2=None, p3=None):
    db = self.get_db()
    db.execute('UPDATE t SET p1=?, p2=?, p3=? WHERE id=?', (p1, p2, p3, id))

When I use update(1, 3, 4, 5) it works, but on update(1, 3, 4) it gives me Error: NOT NULL constraint failed: t.p3 what is normal because i should do

if not p3:
    db.execute('UPDATE t SET p1=?, p2=? WHERE id=?', (p1, p2, id))

Is it possible to get it done quite simple ?

for example:

stm = db.prep('UPDATE t SET')
if p1:
    stm.append('p1=?', p1)
if p2:
    stm.append('p2=?', p2)
if p3:
    stm.append('p3=?', p3)
stm.append('WHERE id=?', id)

"Error: NOT NULL constraint failed: t.p3": You only pass tree parameter, update(1, 3, 4), the fourth will be None which is p3 — stovfl
– stovfl, Commented Feb 12, 2020 at 14:44

FiddleStix · Accepted Answer · 2020-02-12 15:08:43Z

1

This would be the easiest method I can think of:

def update(self, object_id, p1=None, p2=None, p3=None):  # don't use id as a variable name
    db = self.get_db()
    # if any of the parameters are None, set the new value to what it was already
    db.execute('UPDATE t SET p1=ifnull(?, p1), p2=ifnull(?, p2), p3=ifnull(?, p3) WHERE id=?', (p1, p2, p3, object_id))

Personally, I would use an ORM instead of raw SQL for security reasons.

answered Feb 12, 2020 at 15:08

FiddleStix

3,89123 silver badges22 bronze badges

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

2 Comments

luzik Over a year ago

Exactly what i was looking for!

luzik Over a year ago

What if there is default value like p1 INTEGER NOT NULL DEFAULT=55. How can I use something like. UPDATE t SET p1=ifnull(?, DEFAULT) ... or maybe UPDATE t SET ifnotnull(?, p1=?). I know that there is no default nor second example, but maybe there is some other magical way :)

Collectives™ on Stack Overflow

python sqlite3 conditional query

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