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?

When I was younger, I defined a database with tables and columns. What is the table, what are the columns, and how are your values related with that all ? — Serge Ballesta
– Serge Ballesta, Commented Jan 3, 2015 at 16:27
@SergeBallesta the name of table is called programs and the column is called channel. I've got 1104 rows in a database but it will be random as it will depends on how many rows I store in a database. — Rob
– Rob, Commented Jan 3, 2015 at 16:35
@Rob I don't think your goal is clearly stated. Could you provide an example of your desired output? — Bob Haffner
– Bob Haffner, Commented Jan 3, 2015 at 17:32
@Rob You want to count something per channel, correct? If that's the case, add what that would look like. — Bob Haffner
– Bob Haffner, Commented Jan 3, 2015 at 17:38

Nick Bailey · Accepted Answer · 2015-01-05 00:29:20Z

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.

edited Jan 5, 2015 at 0:29

answered Jan 3, 2015 at 18:54

Nick Bailey

3,1922 gold badges13 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Over a year ago

Thank you very much for this as you are almost correct but there are few things that are wrong. On the code it give me the result that one output value are missing which is 1 and the output value 1105 is not needed because it is the last row at the end of the database. I want to get the value that start with 1, then add 69 in each time until it get to the last value at end of the database, example: 1036. Please see in my updated question that I have put the output result with the one that are missing and the last output value which is not needed.

Rob Over a year ago

I'm waiting for your answer.

Nick Bailey Over a year ago

Why exactly do you want to get this result? It would be much easier to answer the question if we had some understanding of what you want to do with this information? Is it a homework assignment?

Collectives™ on Stack Overflow

How to count the values of rows in a database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related