0 Replies - 102 Views - Last Post: 24 July 2014 - 06:20 PM Rate Topic: -----

#1 mark103  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 42
  • Joined: 13-April 08

find the matches data from database

Posted 24 July 2014 - 06:20 PM

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?

This post has been edited by mark103: 24 July 2014 - 06:24 PM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1