0

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.

9
  • You must give a value for parameter $src_ip Commented Dec 8, 2018 at 0:09
  • 2
    Try it like in this Answer Update two record with same identify value Commented Dec 8, 2018 at 8:50
  • @stovfl Thanks! I get the concept. His query 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 Commented Dec 9, 2018 at 4:02
  • 1
    @acb452: Edit your Question and show your attemp adapting the concept from Update two record with same identify value. Your WHERE src_ip LIKE src_ip is faulty, read 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. Commented Dec 9, 2018 at 8:51
  • 1
    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 Commented Dec 12, 2018 at 18:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.