1

I am new to sqlite3. I imported in SQLite through python an exported CSV file listing IP addresses from Splunk and I plan on increasing the count column on my database every time a similar IP address gets recognized.

What I had in mind was to use SQLite CASE statement, documentation, update statement, etc. I tried:

SELECT * CASE WHEN src_ip = src_ip THEN UPDATE table SET Count = Count + 1;

also tried,

UPDATE table SET Count = Count + 1 WHERE src_ip = src_ip;

I know I'm wrong I can't quite figure this problem out for days. Here's what my sqlite3 database looks like in cmd prompt:

sqlite> select * from result;
1537536602|2002:8672:d515::8672:d515|
1537288499|150.135.165.114|
1537536602|2002:8672:d515::8672:d515|
1537288499|150.135.165.114|
sqlite>
sqlite> .schema
CREATE TABLE result (_time STR, 'src_ip' STR, Count INT);
sqlite>

I will continue to look for solution. I appreciate any feedback!

acb452
  • 63
  • 1
  • 8
  • You want the IP column time be unique? What about the timestamp column? How should that be treated with duplicate IPs? It's probably a better design to just store all the data in separate rows and compute the counts of IPs at runtime so you don't lose the timestamps. – Shawn Oct 12 '18 at 16:28
  • @Shawn I appreciate your reply, I plan on using timestamp for deleting rows after certain time - expiration after 30 days unless its found again and count increases from null to 1 and will add expiration date to 45+ days and so on up to 3 counts - I got deleting rows part figured out. I want to know how to increase count column of the row +1 every time it finds similar IP address when I run the script again. – acb452 Oct 12 '18 at 17:03
  • This seems like a duplicate of https://stackoverflow.com/questions/2717590/sqlite-insert-on-duplicate-key-update where multiple correct answer are suggested or linked. – szmate1618 Nov 01 '18 at 10:25
  • @szmate1618 thank you for your response! I checked the link. The "..on conflict() do update set Count = Count + 1;" worked for a minute on my script and now for some reason it's not working anymore. I am getting a syntax error operationError: near "on". All my syntaxes are correct, my script seem to not like "on conflict" clause I don't know why. I am looking into this. You didn't have any problems with the "on conflict" statement? – acb452 Nov 02 '18 at 21:57
  • That's strange. What version of SQLite are you using? It only works on 3.24.0 and newer. – szmate1618 Nov 02 '18 at 22:34
  • @szmate1618 I'm sorry for late reply, been trying to figure this problem out. Yes SQLite version is 3.24.0. I am now getting an error after typing this statement: 'INSERT INTO result (_time, src_ip) SELECT Count,0 FROM (VALUES (?,?)) ON CONFLICT (src_ip) DO UPDATE SET Count = result.Count + 1;' I got from answer [link](https://stackoverflow.com/questions/36886134/postgres-conflict-handling-with-multiple-unique-constraints) (Error: near "Do" syntax error) I am looking into this. Appreciate the help! – acb452 Nov 09 '18 at 15:33
  • 1
    No problem. But this does not look like valid SQL. Do you mean `INSERT INTO result (_time, src_ip, count) VALUES (12121,'1.1.1.1', 1) ON CONFLICT (src_ip) DO UPDATE SET count = count + 1;` – szmate1618 Nov 09 '18 at 15:41
  • 1
    @szmate1618 I got it to work, thank you! Yes the syntax was incorrect and I had to change null values from my script to 0. Now my issue lies in the python script, I am getting a syntax error on my script but not when I use command line the statement works! – acb452 Nov 09 '18 at 17:58

1 Answers1

0

Could you not just autoincrement a counter as you insert records?

https://www.sqlite.org/autoinc.html

eatmeimadanish
  • 3,809
  • 1
  • 14
  • 20