Hi all,

I need your help, I'm working on my python script to get the list of channels and programs when I pulled the data from sqlite3 database.

I'm using the variable of channel_per_page to get the range from 0 to 7 for channels that I want to pull the data from the database. I can get the list of 7 channels which you can see the results.

Result for 7 channels:

17:02:10 T:6400  NOTICE: 101 ABC FAMILY
17:02:10 T:6400  NOTICE: 102 CBS
17:02:10 T:6400  NOTICE: 103 CNN USA
17:02:10 T:6400  NOTICE: 105 ESPN USA
17:02:10 T:6400  NOTICE: 106 Fox News
17:02:10 T:6400  NOTICE: 107 Animal Planet
17:02:10 T:6400  NOTICE: 108 USA Network 

Now I want to find the list of programs in the same rows as the matches 7 channels so i can print the list of programs.

When I try this code:

import xbmc
import StringIO
import sqlite3
CHANNELS_PER_PAGE = 7

#Pull the data from the database
channelList = list()
database_path = xbmc.translatePath(os.path.join('special://userdata/addon_data/script.tvguide', 'source.db'))

if os.path.exists(database_path):
   #get the channels list
   cur.execute('SELECT channel FROM programs WHERE channel GROUP BY channel')

   for row in cur:
       channels = row[0].encode('ascii')
       channelList.append(channels)


       # set the channels text
       for index in range(0, CHANNELS_PER_PAGE):
           channel = channelList[index]


           if channel is not None:
              self.getControl(4110 + index).setLabel(channel)



             #get the programs list
             cur.execute('SELECT channel, title, start_date, stop_date FROM programs WHERE channel')
             #startTime = 0
             #endTime = startTime + datetime.timedelta(hours=2)
             programList = list()


             for row in cur:
                 program = row[1].encode('ascii'), str(row[2]), str(row[3])
                 programList.append(program)                        
             print programList[index]

It will get the list of whole programs from abc family to bravo7 where you can see the results for the full channels:

Result for full channels:

17:02:10 T:6400  NOTICE: 101 ABC FAMILY
17:02:10 T:6400  NOTICE: 102 CBS
17:02:10 T:6400  NOTICE: 103 CNN USA
17:02:10 T:6400  NOTICE: 105 ESPN USA
17:02:10 T:6400  NOTICE: 106 Fox News
17:02:10 T:6400  NOTICE: 107 Animal Planet
17:02:10 T:6400  NOTICE: 108 USA Network
17:02:10 T:6400  NOTICE: 110 SPIKE
17:02:10 T:6400  NOTICE: 111 BRAVO USA
17:02:10 T:6400  NOTICE: 112 BRAVO1
17:02:10 T:6400  NOTICE: 113 BRAVO2
17:02:10 T:6400  NOTICE: 114 BRAVO3
17:02:10 T:6400  NOTICE: 115 BRAVO4
17:02:10 T:6400  NOTICE: 116 BRAVO5
17:02:10 T:6400  NOTICE: 117 BRAVO6
17:02:10 T:6400  NOTICE: 118 BRAVO7

Here is the result for every program for every channel:

17:05:58 T:5984  NOTICE: ('The Middle -  The Ditch', '20140520170000', 
'20140520173000')
17:05:58 T:5984  NOTICE: ('The Goonies', '20140520173000', '20140520200000')
17:05:58 T:5984  NOTICE: ('Pirates of the Caribbean: On Stranger Tides', 
'20140520200000', '20140520230000')
17:05:58 T:5984  NOTICE: ('The 700 Club', '20140520230000', 
'20140521000000')
17:05:58 T:5984  NOTICE: ('The Fresh Prince of Bel-Air -  Day Damn One', 
'20140521000000', '20140521003000')
17:05:58 T:5984  NOTICE: ('The Fresh Prince of Bel-Air -  Lucky Charm', 
'20140521003000', '20140521010000')
17:05:58 T:5984  NOTICE: ('The Fresh Prince of Bel-Air -  The Ethnic Tip', 
'20140521010000', '20140521013000')
17:05:58 T:5984  NOTICE: ('The Fresh Prince of Bel-Air -  The Young and the 
Restless', '20140521013000', '20140521020000')
17:05:58 T:5984  NOTICE: ('Summer Sexy With T25!', '20140521020000', 
'20140521023000')
17:05:58 T:5984  NOTICE: ('Paid Programming', '20140521023000', 
'20140521030000')
17:05:58 T:5984  NOTICE: ('The 700 Club', '20140521030000', 
'20140521040000')
17:05:58 T:5984  NOTICE: ("Shaun T's Focus T25", '20140521040000', 
'20140521043000')
17:05:58 T:5984  NOTICE: ('Sleep Better!', '20140521043000', 
'20140521050000')
17:05:58 T:5984  NOTICE: ('Joseph Prince', '20140521050000', 
'20140521053000')
17:05:58 T:5984  NOTICE: ('Life Today With James Robison -  Christine Caine 
-  FOY, MOR 1', '20140521053000', '20140521060000')
17:05:58 T:5984  NOTICE: ('Joyce Meyer: Enjoying Everyday Life', 
'20140521060000', '20140521063000')
17:05:58 T:5984  NOTICE: ('Amazing Facts Presents', '20140521063000', 
'20140521070000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Who Wants It More?", 
'20140521070000', '20140521073000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Fez Gets the Girl", 
'20140521073000', '20140521080000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Dine and Dash", 
'20140521080000', '20140521083000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Radio Daze", '20140521083000', 
'20140521090000')
17:05:58 T:5984  NOTICE: ('700 Club Special Programming', '20140521090000', 
'20140521110000')
17:05:58 T:5984  NOTICE: ('Gilmore Girls -  A Deep-Fried Korean 
Thanksgiving', '20140521110000', '20140521120000')
17:05:58 T:5984  NOTICE: ('8 Simple Rules -  Princetown Girl', 
'20140521120000', '20140521123000')
17:05:58 T:5984  NOTICE: ('8 Simple Rules -  A Very C.J. Christmas', 
'20140521123000', '20140521130000')
17:05:58 T:5984  NOTICE: ('Reba -  And the Grammy Goes To ...', 
'20140521130000', '20140521133000')
17:05:58 T:5984  NOTICE: ('Reba -  The Wall', '20140521133000', 
'20140521140000')
17:05:58 T:5984  NOTICE: ('Reba -  The Best Defense', '20140521140000', 
'20140521143000')
17:05:58 T:5984  NOTICE: ('Reba -  For Sale, Cheap', '20140521143000', 
'20140521150000')
17:05:58 T:5984  NOTICE: ('Boy Meets World -  State of the Unions', 
'20140521150000', '20140521153000')
17:05:58 T:5984  NOTICE: ('Boy Meets World -  Show Me the Love', 
'20140521153000', '20140521160000')
17:05:58 T:5984  NOTICE: ('Boy Meets World -  For Love and Apartments', 
'20140521160000', '20140521163000')
17:05:58 T:5984  NOTICE: ("Boy Meets World -  Angela's Men", 
'20140521163000', '20140521170000')
17:05:58 T:5984  NOTICE: ('The Middle -  The Cheerleader', 
'20140521170000', '20140521173000')
17:05:58 T:5984  NOTICE: ('The Middle -  The Block Party', 
'20140521173000', '20140521180000')
17:05:58 T:5984  NOTICE: ('The Middle -  The Floating Anniversary', 
'20140521180000', '20140521183000')
17:05:58 T:5984  NOTICE: ('The Middle -  The Trip', '20140521183000', 
'20140521190000')
17:05:58 T:5984  NOTICE: ('Melissa & Joey -  Right Time, Right Place', 
'20140521190000', '20140521193000')
17:05:58 T:5984  NOTICE: ("Melissa & Joey -  Don't Look Back in Anger", 
'20140521193000', '20140521200000')
17:05:58 T:5984  NOTICE: ('Melissa & Joey -  Accidents Will Happen', 
'20140521200000', '20140521203000')
17:05:58 T:5984  NOTICE: ('Baby Daddy -  Send in the Clowns', 
'20140521203000', '20140521210000')
17:05:58 T:5984  NOTICE: ('Liar Liar', '20140521210000', '20140521230000')
17:05:58 T:5984  NOTICE: ('The 700 Club', '20140521230000', 
'20140522000000')
17:05:58 T:5984  NOTICE: ('Baby Daddy -  Flirty Dancing', '20140522000000', 
'20140522003000')
17:05:58 T:5984  NOTICE: ('Baby Daddy -  Send in the Clowns', 
'20140522003000', '20140522010000')
17:05:58 T:5984  NOTICE: ("Melissa & Joey -  Don't Look Back in Anger", 
'20140522010000', '20140522013000')
17:05:58 T:5984  NOTICE: ('Melissa & Joey -  Accidents Will Happen', 
'20140522013000', '20140522020000')
17:05:58 T:5984  NOTICE: ('21 Day Fix', '20140522020000', '20140522023000')
17:05:58 T:5984  NOTICE: ("Dr. Ordon's Secret!", '20140522023000', 
'20140522030000')
17:05:58 T:5984  NOTICE: ('The 700 Club', '20140522030000', 
'20140522040000')
17:05:58 T:5984  NOTICE: ('Airbrushed Beauty', '20140522040000', 
'20140522043000')
17:05:58 T:5984  NOTICE: ("Shaun T's Focus T25", '20140522043000', 
'20140522050000')
17:05:58 T:5984  NOTICE: ('Joseph Prince', '20140522050000', 
'20140522053000')
17:05:58 T:5984  NOTICE: ('Life Today With James Robison -  Rebekah Lyons', 
'20140522053000', '20140522060000')
17:05:58 T:5984  NOTICE: ('Joyce Meyer: Enjoying Everyday Life', 
'20140522060000', '20140522063000')
17:05:58 T:5984  NOTICE: ('Time of Grace With Pastor Mark Jeske', 
'20140522063000', '20140522070000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Donna's Panties", 
'20140522070000', '20140522073000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Romantic Weekend", 
'20140522073000', '20140522080000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  Kitty's Birthday (That's 
Today?!)", '20140522080000', '20140522083000')
17:05:58 T:5984  NOTICE: ("That '70s Show -  The Trials of Michael Kelso", 
'20140522083000', '20140522090000')
17:05:58 T:5984  NOTICE: ('700 Club Special Programming', '20140522090000', 
'20140522110000')
17:05:58 T:5984  NOTICE: ("Gilmore Girls -  That'll Do Pig", 
'20140522110000', '20140522120000')
17:05:58 T:5984  NOTICE: ('8 Simple Rules -  The Sub', '20140522120000', 
'20140522123000')
17:05:58 T:5984  NOTICE: ("8 Simple Rules -  C.J.'s Temptation", 
'20140522123000', '20140522130000')
17:05:58 T:5984  NOTICE: ('Reba -  The Will', '20140522130000', 
'20140522133000')
17:05:58 T:5984  NOTICE: ('Reba -  Location, Location, Location', 
'20140522133000', '20140522140000')
17:05:58 T:5984  NOTICE: ('Reba -  Your Place or Mine', '20140522140000', 
'20140522143000')
17:05:58 T:5984  NOTICE: ("Reba -  She's Leaving Home, Bye, Bye", 
'20140522143000', '20140522150000')
17:05:58 T:5984  NOTICE: ('Boy Meets World -  No Such Thing as a Sure 
Thing', '20140522150000', '20140522153000')
17:05:58 T:5984  NOTICE: ("Let's Make a Deal", '20140520170000', 
'20140520180000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140520180000', 
'20140520203000')
17:05:58 T:5984  NOTICE: ('CBS Evening News With Scott Pelley', 
'20140520203000', '20140520210000')
17:05:58 T:5984  NOTICE: ('Judge Judy Primetime', '20140520210000', 
'20140520220000')
17:05:58 T:5984  NOTICE: ('ACM Presents: An All-Star Salute to the Troops', 
'20140520220000', '20140521000000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521000000', 
'20140521003500')
17:05:58 T:5984  NOTICE: ('Late Show With David Letterman', 
'20140521003500', '20140521013700')
17:05:58 T:5984  NOTICE: ('The Late Late Show With Craig Ferguson', 
'20140521013700', '20140521023700')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521023700', 
'20140521030000')
17:05:58 T:5984  NOTICE: ('Up to the Minute', '20140521030000', 
'20140521060000')
17:05:58 T:5984  NOTICE: ('CBS Morning News', '20140521060000', 
'20140521063000')
17:05:58 T:5984  NOTICE: ('CBS Morning News', '20140521063000', 
'20140521070000')
17:05:58 T:5984  NOTICE: ('CBS Morning News', '20140521070000', 
'20140521073000')
17:05:58 T:5984  NOTICE: ('CBS Morning News', '20140521073000', 
'20140521080000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521080000', 
'20140521090000')
17:05:58 T:5984  NOTICE: ('CBS This Morning', '20140521090000', 
'20140521110000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521110000', 
'20140521130000')
17:05:58 T:5984  NOTICE: ('The Price Is Right', '20140521130000', 
'20140521140000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521140000', 
'20140521143000')
17:05:58 T:5984  NOTICE: ('The Young and the Restless', '20140521143000', 
'20140521153000')
17:05:58 T:5984  NOTICE: ('The Bold and the Beautiful', '20140521153000', 
'20140521160000')
17:05:58 T:5984  NOTICE: ('The Talk', '20140521160000', '20140521170000')
17:05:58 T:5984  NOTICE: ("Let's Make a Deal", '20140521170000', 
'20140521180000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140521180000', 
'20140521203000')
17:05:58 T:5984  NOTICE: ('CBS Evening News With Scott Pelley', 
'20140521203000', '20140521210000')
17:05:58 T:5984  NOTICE: ("Survivor -  It's Do or Die", '20140521210000', 
'20140521230000')
17:05:58 T:5984  NOTICE: ('Survivor -  Reunion Special', '20140521230000', 
'20140522000000')
17:05:58 T:5984  NOTICE: ('Local Programming', '20140522000000', 
'20140522003500')
17:05:58 T:5984  NOTICE: ('Late Show With David Letterman', 
'20140522003500', '20140522013700')
17:05:58 T:5984  NOTICE: ('The Late Late Show With Craig Ferguson', 
'20140522013700', '20140522023700')
and so on...

Do you know how I can find the programs from the sqlite3 database where it will matches the 7 channels using the index so I would not have to get every program from every channel?

Edited 2 Years Ago by mark103

This is a horrible post, you have made here.
1. Your code does not run. Bad indent.
2. Are you sure, we need all the data from the database to answer your question?
3. You did not give us the python version you are using.
4. You did not give us the structure of the table(s) and indices you are using. You are asking how to use an index, we do not know anything about.
5. Your select statement : "SELECT channel, title, start_date, stop_date FROM programs WHERE channel" is semantically and probably sytaxtically incorrect. This is not a python problem.

No wonder no one answers....

This article has been dead for over six months. Start a new discussion instead.