hZVLy1mX

1# Script creates a SQLite connection and database
2# Imports CSV file into SQLite with Count+=1 condition
3# Deletes expired rows based on _time and Count
4# Outputs src_ip results into a file
5# Saves and closes connection
6# Use task scheduler Windows OR cron job linux to run script every hour
7
8# import modules
9import sqlite3
10import csv
11
12
13# Variable for the file output.
14file = open("Output_txt.csv", "w")
15
16# Creates a connection that represent the database1 and cursor to execute SQL commands.
17conn = sqlite3.connect('test_database1.db')
18c = conn.cursor()
19
20
21# Create table on database if not already exists
22def create_database():
23    # Execute() method performs SQL commands.
24    c.execute("CREATE TABLE IF NOT EXISTS result ('src_ip' STR PRIMARY KEY, 'min(_time)' STR, Count INT)")
25create_database()
26
27
28# Import CSV into SQLite
29def data_entry(c):
30    # Open a csv file and read file.   
31    with open('result5.csv', 'r') as f:
32        dr = csv.DictReader(f)
33        to_db = [(i['min(_time)'], i['src_ip']) for i in dr]
34        to_ip = [(i['src_ip']) for i in dr]
35    c.executemany('''UPDATE result SET Count = Count + 1 WHERE src_ip = ?''', [to_ip])
36
37    if c.rowcount == 0:
38        c.executemany("INSERT OR REPLACE INTO result ('min(_time)', src_ip) VALUES (?,?)", to_db)
39    
40    c.execute("UPDATE result SET Count = 1 WHERE Count IS NULL;")
41data_entry(c)
42
43
44# Case statement that deletes rows based on Count=* and based on * days old
45def delete_row_30d(c):
46    # Delete * from <table> where Count is less than * days ago.
47    c.execute('''SELECT * FROM result WHERE Count = 1 AND ("min(_time)" > strftime('%s','now', '-7 day'));''')
48    c.execute('''SELECT * FROM result WHERE Count = 2 AND ("min(_time)" > strftime('%s','now', '-14 day'));''')
49    c.execute('''SELECT * FROM result WHERE Count = 3 AND ("min(_time)" > strftime('%s','now', '-28 day'));''')
50    c.execute('''SELECT * FROM result WHERE ("min(_time)" < strftime('%s','now', '-30 day'));''')
51delete_row_30d(c)
52
53
54# Outputs results to a CSV file - and appends data
55def outputs_to_file(c):
56    c.execute("SELECT src_ip FROM result;")
57    rows = c.fetchall()
58    for row in rows:
59        print(row)
60        file.write(str(row) + "\n")
61outputs_to_file(c)
62
63
64# Saves and close connection
65def save_close_connection():
66    conn.commit()
67    c.close()
68    conn.close()
69save_close_connection()