1

Below is my SQLite Database with two tables.

import sqlite3


conn=sqlite3.connect('calc.db')
conn.execute("""CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT 
    )""")

conn.execute("""CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT 
    )""")

conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('001', 'name1', '')""")
conn.execute("""INSERT INTO sum(id, name, total) \
    VALUES('002', 'name2', '')""")
#
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '1000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('002', 'name2', '2000')""")
conn.execute("""INSERT INTO newtable(id, name, num) \
    VALUES('001', 'name1', '4000')""")


conn.commit()
conn.close()

Sum table has unique id and newtable id is not unique. I want to perform addition in sum table in total column that should be taken the addition value from newtable column when inserting and if id matches. Hoe to do. My columns are text type. If not posible in TEXT type i can change it into integer but it is better to continue in TEXT type if possible. How to do.

3

You can add a trigger to your python script

CREATE TABLE IF NOT EXISTS sum
        (id TEXT UNIQUE,
    name    TEXT,
    total   TEXT 
    )
CREATE TABLE IF NOT EXISTS newtable
        (id TEXT,
    name    TEXT,
    num TEXT 
    )
INSERT INTO sum(id, name, total) 
    VALUES('001', 'name1', '');
INSERT INTO sum(id, name, total) 
    VALUES('002', 'name2', '');
CREATE TRIGGER sum_after_insert
   AFTER INSERT ON newtable
BEGIN
  UPDATE sum SET total = total + NEW.num WHERE id = NEW.id;
END;
INSERT INTO newtable(id, name, num) 
    VALUES('001', 'name1', '1000');
INSERT INTO newtable(id, name, num) 
    VALUES('002', 'name2', '2000');
INSERT INTO newtable(id, name, num) 
    VALUES('001', 'name1', '4000');
SELECT * FROM sum;
id  | name  | total
:-- | :---- | :----
001 | name1 | 5000 
002 | name2 | 2000 

db<>fiddle here

1
  • Its working perfect for me. Thank you – Mano Nov 13 '20 at 12:21
2
CREATE TRIGGER 
AFTER INSERT
ON newtable
FOR EACH ROW
BEGIN
    UPDATE sum 
    SET total = total + NEW.num
    WHERE name = NEW.name;
END

DEMO

1
  • The name column is not unique. If i change the name to id in WHERE clause its working perfect for me. Thank you. – Mano Nov 13 '20 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.