Search the data to update in a database

[chris0147]

Hi all,
I need some help, I want to search for the data into the database and replace for each string. I want to find for a data like 3001, I want to set the data to a empty string and find the next data 3002 to replace it with 3001. Then I want to find the next data 3003 to replace it with 3002, find the next data 3004 to replace it with 3003, find the next data 3005 to replace it with 3004 and so on until find the last data 3010 to replace it with 3009. After the 3009 data is updated in the database, the next row that come after the 3009 data where it have a empty data, I want to input the 3010 data.

def update_in_database(self):
     profilePath = xbmc.translatePath(os.path.join('special://userdata/addon_data/script', 'source.db'))
     conn1 = database.connect(profilePath)
     cur1 = conn1.cursor()

     for program_id in self.program_id:
         cur.execute('SELECT channel, program_id FROM programs WHERE program_id=?;', (program_id))
         data = cur.fetchone()

         if data is not None:
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['', ['3001'])
             #program_id = int(program_id) + 1
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3001', '3002'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3002', '3003'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3003', '3004'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3004', '3005'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3005', '3006'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3006', '3007'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3007', '3008'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3008', '3009'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3009', '3010'])
             cur.execute('UPDATE programs SET program_id=? WHERE program_id=?', ['3010', ''])
             con.commit()

Here is the contains for the self.program_id list:

['3001', '3011', '3021', '3031', '3041', '3051', '3061']

Here is the screenshot of the database:

[Image: ZUK6ATG.jpg]


The reason why I want to search for the data and replace each of them, so I can use the valve to find the data in the same row as the id to get the information I want.

Example: I want to find the data that start from 3001 to replace it with a empty string, then find the next data 3002 to replace it with 3001, find the next data 3003 to replace it with 3004 and so on then until to 3010 to replace it with 3009. So when the update is finish, I can use the id 3009 to search for the stop_date data to extract the data of 20161019130000 or extract to get the program title Bargain Hunt.

Can you please show me an example of how I can write in a better way than using in my own code?

Thanks in advance