Nsryjdtyk

I am trying to delete rows in SQLite based on Count and * of days.

Instead of writing multiple SQL commands like this:

DELETE * FROM result WHERE Count = 1 AND ("min(_time)" < strftime('%s','now', '-7 day'));

DELETE * FROM result WHERE Count = 2 AND ("min(_time)" < strftime('%s','now', '-14 day'))

DELETE * FROM result WHERE Count = 3 AND ("min(_time)" < strftime('%s','now', '-28 day'));

and so on...

I am trying to find a way to use for loop where it increments Count and the * days

I tried this:

data = c1.execute('''SELECT * FROM result;''')



for row in data:

    Count = row[1]

    days = row[7]

    c1.execute('''DELETE * FROM result WHERE Count = ? AND ("min(_time)" < strftime('%s','now', '-? day'));''', (Count, days))

I am getting a IndexError: tuple index out of range under days = row[7].

I looked up the error and changed the number see if it would fix it, still indexerror.

Sample data of the result table looks like this: "select * from result;" Columns exported from Splunk: src_ip, 'min(_time)', Count

183.78.180.27|1542266993|1

185.189.150.42|1542246169|1

185.189.150.61|1542271436|1

2002:8672:d515::8672:d515|1537536602|3

150.135.165.114|1537288499|3

Am I getting the concept for the script correct? Any suggestions on the indexerror? Thank you!

Consider parameterizing the entire interval date, '-7 day', '-14 day', ... since qmark placeholders should not be quoted. Then, pass the built string into .execute() call:

delete_sql = '''DELETE FROM result 

                WHERE [Count] = ? 

                AND ("min(_time)" < strftime('%s', 'now', ?));

             '''



data = c1.execute("SELECT * FROM ...")



for i in data.fetchall():

    Count = i[0]

    days = '-{} day'.format(i[7])



    c1.execute(delete_sql, (Count, days))

    conn.commit()

Question: I am trying to find a way to use for loop where it increments Count and the * days

Increment Count and days:

    day = -7        

    for count in range(1, 4):

        print("WHERE Count = {} AND '{} day')".format(count, day))

        day = day * 2

Output:
Note: This is not ready to use for a SQL statement!

WHERE Count = 1 AND '-7 day')

WHERE Count = 2 AND '-14 day')

WHERE Count = 3 AND '-28 day')

Tested with Python:3.5.3