How to count the values of rows in a database

Question

I'm working on my python script as I'm want to create the variable to count on the values to find out how many 69 rows I have got in a database and then counts on the values to find out how many values I have got to make a single value.

Here is for example: When I connect to a database and select a channel, I want to get the row value that start with 1, then add 69 to make it 70. Add another 69 to make 139, add another 69 to make 208 and so on, I want to continue to add up in each time until I get the last 69 rows in a database. Then I want to count on each value how many values I have make to get the single value.

Example:

>> 1
>> 70
>> 139
>> 208
>> 277
>> 346
>> 415
>> 484
>> 553
>> 622

I'm counting on those values 1, 70, 139 208, 277, 346, 415, 484, 553 and 622 to make 10 in total. That's because I have the value 1 which is one value I have got, I have the value 70 which is two values I have got and so on.

Here is the code:

#get the programs list
cur = con.cursor()
cur.execute('SELECT channel FROM programs')
programs = cur.fetchall()
start_pos = 375    # indent for first program
channels_index =  69 + 1   # count how many rows I have got in a database

I want to count the 69 rows to make a value, because I have got 69 rows of each data that I stored in a database.

Here is an example of what my database looks like:

ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
ABC FAMILY
...etc until 69
CBS
CBS
CBS
CBS
CBS
CBS
CBS
CBS
CBS
CBS
...etc until 69

EDIT: Here is a list of channels from database:

18:29:44 T:5836  NOTICE: [(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), (u'101 ABC FAMILY ',), 
(u'101 ABC FAMILY ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), (u'102 CBS ',), 
(u'102 CBS ')]

Here is the result:

 <<<<<<<< the value `1` is missing
19:43:08 T:6208  NOTICE: 70
19:43:08 T:6208  NOTICE: 139
19:43:08 T:6208  NOTICE: 208
19:43:08 T:6208  NOTICE: 277
19:43:08 T:6208  NOTICE: 346
19:43:08 T:6208  NOTICE: 415
19:43:08 T:6208  NOTICE: 484
19:43:08 T:6208  NOTICE: 553
19:43:08 T:6208  NOTICE: 622
19:43:08 T:6208  NOTICE: 691
19:43:08 T:6208  NOTICE: 760
19:43:08 T:6208  NOTICE: 829
19:43:08 T:6208  NOTICE: 898
19:43:08 T:6208  NOTICE: 967
19:43:08 T:6208  NOTICE: 1036
19:43:08 T:6208  NOTICE: 1105 <<<<<<<< not needed

Can you please help me how I can count on the rows in a database that start with row 1 then add up to 69 each time until I get the last 69 rows in a database and then counts on how many values I have to make a single value?

Answer 1

You should never fetchall from a table and then iterate over it in Python if you can avoid it. Use SQL aggregation. It will be much faster.

For example:

#Initialize a running total. For some reason you asked to initialize it to one, but note that that will give you a final total that is 1 greater than the number of 
running_total = 1
#Query will select the sum of
query = "SELECT Channel, COUNT(*) AS number_of_programs FROM Programs GROUP BY Channel"
cursor = con.cursor()
cursor.execute(query)
for result in cursor.fetchall():
    print running_total
    #Add the number of programs for the channel to your running total. You access it as the second element of the returned record, because we selected number_of_programs second in our query.
    print 'Channel {0} has {1} programs. Current program count is {2}'.format(result[0],result[1],running_total)
    running_total += result[1] 

Of course, if you actually know that every single channel has exactly 69 programs, you could just write.

query = "SELECT COUNT(*) FROM programs"
cursor.execute(query)
x = cursor.fetchone()[0]
running_total = 1 #Again, you want to start counting at 1 for some reason
while running_total < x:
    running_total += 69
    print running_total

But I don't think that's what you want.