SQLITE using a python variables with a select statement

Question

I'm finding this issue difficult to solve: I'm trying to use a SQL SELECT statement near a PYTHON VARIABLE , which results in a syntax error. HERE IS MY CODE

item_sql = """
        INSERT or REPLACE INTO item(i_name, i_qty)
        VALUES
        ((?), SELECT s_qty + (?) from item)
        """
self.cur.execute(item_sql, (s_item, s_qty))
self.con.commit()

What I am trying to achieve is; after inserting the i_name and i_qty; then in the next insertion, to sum the previous i_qty to the last inserted i qty values.

Is there a way to resolve this conflict of using a SQL SELECT statement near a PYTHON VAR or another way to achieve my desired result.

thanks.

HI, because i'm inserting in to a sqlite database, in which i'm storing the items and their quantity. — Mattey
– Mattey, Commented Nov 30, 2020 at 12:10

forpas · Accepted Answer · 2020-11-30 12:59:32Z

I suspect that you want to insert a new row to the table but if the i_name already exists then you just want to update i_qty by adding the quantity parameter to the existing quantity.
If this is the case and there is already set a unique constraint or index for the column i_name, then you can do it with UPSERT:

item_sql = """
        INSERT INTO item(i_name, i_qty) VALUES (?, ?)
        ON CONFLICT(i_name) DO UPDATE SET
        i_qty = i_qty + EXCLUDED.i_qty
        """

With UPSERT you can insert a new row to the table, with the statement INSERT INTO ...., but if a unique constraint violation occurs, then the statement after ON CONFLICT(...) DO will be executed.

If you version of SQLite does not support UPSERT, you can execute 2 separate statements:

UPDATE item SET i_qty = i_qty + ? WHERE i_name = ?

If the value of i_name does not exist in the table then nothing will happen, but if it exists then the row will be updated.
And then:

INSERT OR IGNORE INTO item (i_name, i_qty) VALUES (?, ?)

If the value of i_name does not exist in the table then it will be inserted, but if it exists then nothing will happen because of IGNORE.

Hi, tried the code however there seems to be an syntax error, near ON,
it seems my python's sqlite version is 3.14.2, if such these new command will not work right?, btw thanks for the answer :)
i'v just update my python's sql, and the solution you gave worked, thanks for the help

Collectives™ on Stack Overflow

SQLITE using a python variables with a select statement

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related