CODEWITHSUNDEEP
sqlpython-3.xsqlite
wasd
wasd

Reputation: 1572

How to correctly update / insert into sqlite db

I have table with columns: A B C D When inserting new row I need to check if B already exists in DB. If B is present in DB I need to change A and D to new value and increase D by 1 otherwise simply insert new row into this DB. What is the most correct way to do this? Should I use 2 DB operations: try to select and if it's present to update or use INSERT or REPLACE or some other way?

I use Python3

Upvotes: 0

Views: 131

Answers (2)

Amir Ziarati
Amir Ziarati

Reputation: 15087

Another way is like below:

UPDATE table SET A = 1, C=3, D=D+1 WHERE B = 2;
 INSERT INTO table(A,B,C,D) values (1, 2,3,4 ) WHERE changes() = 0;

Changes() will be 0 if update dont happen. ;)

As what you asked in your question you want to know if its better to select first and then update or insert depend on select answer.

UPDATE will have almost the same cost as an SELECT + INSERT. Becuase it needs to find the row (read and search) then change the data (write). If it doesnt find the row you want it wont go for write.

So if you SELECT first and then UPDATE or INSERT you will have two state:

1- SELECT (read) + UPDATE (read+ write)

2- SELECT (read) + INSERT (write)

But with the above query you will have two state:

1- UPDATE (read + write)

2- UPDATE (not finding the row so just read) + INSERT (write)

The second state is just like an SELECT along with a INSERT.

As you see the second states are the same in both scenarios but for the first states the above scenario will have one less read.

Upvotes: 0

CL.
CL.

Reputation: 180010

You could do it with a SELECT, but it would be simpler to just try the UPDATE first:

c.execute("UDPATE X SET A = ?, C = ?, D = D + 1 WHERE B = ?", [newA, newC, oldB])
if c.rowcount == 0:
    c.execute("INSERT INTO X(A,B,C,D) VALUES (?,?,?,?)", [...])

Upvotes: 1

Related Questions