sqlite if exist update else insert in python

Question

I am trying to update Count=Count+1 if src_ip already exists, else insert into table.

I was able to do it with UPSERT successfully:

INSERT INTO result('min(_time)', src_ip) VALUES(?,?)ON CONFLICT(src_ip) DO UPDATE SET Count=Count+1;

I am looking for another way to do this without using UPSERT. I tried this :

# Import csv to SQLite    
with open('result5.csv', 'r') as f:
    dr = csv.DictReader(f)
    for i in dr:
        c.execute("UPDATE result SET Count = Count + 1 WHERE src_ip = ?", (i['src_ip'],))
        if c.rowcount == 0:
            c.execute("INSERT INTO result ('min(_time)', src_ip, Count) VALUES (?,?,1)",
                      (i['min(_time)'], i['src_ip']))

The script works with no errors. But I am not getting the result I want either. It does not return anything sadly. I am not sure what I am doing wrong here, why can't I UPDATE or INSERT?

Thank you.

Try it **like** in this Answer [Update two record with same identify value](https://stackoverflow.com/a/50203435/7414759) — stovfl, Dec 08 '18 at 08:50
@stovfl Thanks! I get the concept. His [query](https://stackoverflow.com/questions/50203341/python-sqlite-update-two-record-with-same-identify-value/50203435#50203435) has `WHERE CLIENT_IP = ?', ("192.168.10.111",)` the issue with my script is I am trying to prevent duplicates into database. I want to update `Count=Count+1` based on `WHERE src_ip LIKE src_ip` or `WHERE src_ip=?", ('src_ip',)` This is where I am stuck. I am trying to avoid duplicate `src_ip` getting into my database, instead `count=count+1` if duplicate is found so I know it has been entered twice and not wasting rows — acb452, Dec 09 '18 at 04:02
@acb452: [Edit] your Question and show your attemp adapting the concept from [Update two record with same identify value](https://stackoverflow.com/a/50203435/7414759). Your `WHERE src_ip LIKE src_ip` is faulty, read [sql-statement-with-like-from-variable](https://stackoverflow.com/questions/6123590/sql-statement-with-like-from-variable), and it **does NOT** help to avoid duplicates. [Edit] your Question and explain in detail **why** do you want to use `LIKE`. — stovfl, Dec 09 '18 at 08:51
@stovfl Ok I updated my question. I dont think `LIKE` would work, so I created variable that pulls `src_ip` from CSV and if exists it `UPDATES` if not it `INSERTS`. I am getting an error on bindings supplied, any suggestions? — acb452, Dec 12 '18 at 17:25
@MichaelButscher I removed `LIKE` clause, now trying to fix `UPDATE` statement that updates existing data in CSV that is in database, else inserts. — acb452, Dec 12 '18 at 17:31
There are couple problems here. - (**using `dr` twice, `to_ip` is already a list, using `executemany` but want to use a condition**) - It's to broad for SO, setup a [GitHub Gist](https://gist.github.com/) — stovfl, Dec 12 '18 at 18:08
@stovfl Ok I created [GitHub Gist](https://gist.github.com/ACB452/dd033013a424a423cabd20564bdb7d09) with the script. The bug is in line 28, I will look into your [example](https://stackoverflow.com/questions/50203341/python-sqlite-update-two-record-with-same-identify-value/50203435#50203435) I have tried that too earlier, will look into it again, was having issues with `?` with its parameters — acb452, Dec 12 '18 at 18:40
@stovfl I updated script and this one does not return errors. It does not return `UPDATE` or `INSERT` statements either, I seem to have passed the parameters to `DictReader` correctly. Any suggestions? — acb452, Dec 13 '18 at 18:02

sqlite if exist update else insert in python

0 Answers0