CODEWITHSUNDEEP
pythonsqlpython-3.xdatabasesqlite
Mattey
Mattey

Reputation: 69

SQLITE using a python variables with a select statement

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.

Upvotes: 0

Views: 106

Answers (1)

forpas
forpas

Reputation: 164069

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.

Upvotes: 2

Related Questions