Loop delete rows based on Count and * of days

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!

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

It looks like you're assigning count the same thing every iteration of the for loop.
– dustinos3
Nov 19 '18 at 18:19

I don't understand your second example code -- it does a SELECT and then another SELECT in a loop -- it never tries to DELETE, which is what you said you want.
– John Gordon
Nov 19 '18 at 18:20

Please fix cursor.fetchall as it does not receive parameters.
– Parfait
Nov 20 '18 at 22:57

add a comment |

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!

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

It looks like you're assigning count the same thing every iteration of the for loop.
– dustinos3
Nov 19 '18 at 18:19

I don't understand your second example code -- it does a SELECT and then another SELECT in a loop -- it never tries to DELETE, which is what you said you want.
– John Gordon
Nov 19 '18 at 18:20

Please fix cursor.fetchall as it does not receive parameters.
– Parfait
Nov 20 '18 at 22:57

add a comment |

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!

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

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!

python python-3.x sqlite sqlite3

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

edited Nov 30 '18 at 20:23

asked Nov 19 '18 at 18:16

acb452

236

asked Nov 19 '18 at 18:16

acb452

236

asked Nov 19 '18 at 18:16

acb452

236

It looks like you're assigning count the same thing every iteration of the for loop.
– dustinos3
Nov 19 '18 at 18:19

I don't understand your second example code -- it does a SELECT and then another SELECT in a loop -- it never tries to DELETE, which is what you said you want.
– John Gordon
Nov 19 '18 at 18:20

Please fix cursor.fetchall as it does not receive parameters.
– Parfait
Nov 20 '18 at 22:57

add a comment |

It looks like you're assigning count the same thing every iteration of the for loop.
– dustinos3
Nov 19 '18 at 18:19

I don't understand your second example code -- it does a SELECT and then another SELECT in a loop -- it never tries to DELETE, which is what you said you want.
– John Gordon
Nov 19 '18 at 18:20

Please fix cursor.fetchall as it does not receive parameters.
– Parfait
Nov 20 '18 at 22:57

It looks like you're assigning count the same thing every iteration of the for loop.
– dustinos3
Nov 19 '18 at 18:19

I don't understand your second example code -- it does a SELECT and then another SELECT in a loop -- it never tries to DELETE, which is what you said you want.
– John Gordon
Nov 19 '18 at 18:20

Please fix cursor.fetchall as it does not receive parameters.
– Parfait
Nov 20 '18 at 22:57

add a comment |

2 Answers
2

active

oldest

votes

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()

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

1

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

1

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

|
show 2 more comments

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

answered Dec 6 '18 at 9:02

stovfl

7,3953931

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380487%2floop-delete-rows-based-on-count-and-of-days%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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()

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

1

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

1

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

|
show 2 more comments

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()

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

1

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

1

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

|
show 2 more comments

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()

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

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()

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

answered Nov 20 '18 at 23:07

Parfait

49.8k84269

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

1

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

1

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

|
show 2 more comments

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

1

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

1

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

Thanks! So the Count gets incremented 0,1, 2, 3,... and so on, as well with days? Sorry I'm new with python :/ the code outputs an 'IndexError: tuple index out of range' at 'days = '-{} day'.format(row[7])' I am looking for a solution, appreciate any help!
– acb452
Nov 26 '18 at 17:26

Count does not increment but uses what ever is the first column in your SELECT * FROM ... query and similarly the day value will derive from 8th column of same query. Please post a few rows of this result to see what loop fetches.
– Parfait
Nov 26 '18 at 17:40

appreciate the response! I work part time at university so my response can be late. Ok, I am getting an index error therefore could not get a result from the script. my select statement is "SELECT * FROM result;" I also need to increment Count so I put Count = i[1]. I am trying to delete rows based on Count and days like stated in the first commands I posted in the question. still looking into this. I appreciate the help!!
– acb452
Nov 30 '18 at 16:43

Please edit your post with sample data of result table for a Minimal, Complete, and Verifiable example.
– Parfait
Nov 30 '18 at 18:21

@acb452: You have to use Count = i[2] and days = '-{} day'.format(i[1])
– stovfl
Dec 3 '18 at 18:03

|
show 2 more comments

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

answered Dec 6 '18 at 9:02

stovfl

7,3953931

add a comment |

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

answered Dec 6 '18 at 9:02

stovfl

7,3953931

add a comment |

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

answered Dec 6 '18 at 9:02

stovfl

7,3953931

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

answered Dec 6 '18 at 9:02

stovfl

7,3953931

answered Dec 6 '18 at 9:02

stovfl

7,3953931

answered Dec 6 '18 at 9:02

stovfl

7,3953931

answered Dec 6 '18 at 9:02

stovfl

7,3953931

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu