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.

• khushi: why exactly do you want to use sql here?
• Mattey: HI, because i'm inserting in to a sqlite database, in which i'm storing the items and their quantity.

Answer 1

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.

• Mattey: Hi, tried the code however there seems to be an syntax error, near ON,
• Mattey: 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 :)
• forpas: You need version 3.24.0
• forpas: @Mattey check my edited answer for an alternative.
• Mattey: i'v just update my python's sql, and the solution you gave worked, thanks for the help