Python Forum
problem with select and update the data into the database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
problem with select and update the data into the database
#1
I have got a problem with select and update the data into the database. I want to find an empty data in the database that go above the program id 3001 so when I find the empty data, I want to replace the empty data to start with 3001, then I want to find the next id 3001 to replace it with 3002, find the next id 3003 to replace it with 3002....etc.

Here is an example table for program_id:

    ==========
    |  program_id   |
    |                     |
    |  3001            |
    |  3002            |
    |  3003            |
    |  3004            |
    |  3005            |
    |  3006            |
    |  3007            |
    |  3008            |
    |  3009            |
    |  3010            |



Here is the code:

   
    def update_in_database(self):
         profilePath = xbmc.translatePath(os.path.join('special://userdata/addon_data/script.tvguide', 'source.db'))
         conn = database.connect(profilePath)
         cur = conn.cursor()
         program_id = ''.join(str(x) for x in self.program_id)
         cur.execute('SELECT channel, program_id FROM programs')
         data = cur.fetchone()
    
         if data is not None:
             value = program_id
             for i in range(0,10):
                 if value == program_id:
                     cur.execute("SELECT channel, start_date FROM programs WHERE program_id=?;",(value,))
                     data = cur.fetchone()
                     cur.execute("UPDATE programs set program_id=? WHERE channel=? and stop_date=? ",(value,data[0],data[1]))
                 value = int(value) + 1
             conn.commit()
             conn.close()
             
When I'm calling the update_in_database function, it will update the data into the database, but one of the data will not be update in the database as it will skip to the next data to update in the database.


It will show like this:

    ==========
    |  program_id   |
    |  3001            |
    |  3001            |
    |  3002            |
    |  3003            |
    |  3004            |
    |  3005            |
    |  3006            |
    |  3007            |
    |  3008            |
    |  3009            |
    |  3010            |



It should be like this:

    ==========
    |  program_id   |
    |  3001            |
    |  3002            |
    |  3003            |
    |  3004            |
    |  3005            |
    |  3006            |
    |  3007            |
    |  3008            |
    |  3009            |
    |  3010            |


What I'm expecting the code to do is to find the empty data above the program id 3001 to replace it with 3001, then update the next 9 data to be insert into the database to replace the data, example: find the data 3001, 3002, 3003, 3004, 3005, 3006, 3007, 3008, 3009 to replace it with 3002, 3003, 3004, 3005, 3006, 3007, 3008, 3009, 3010.

Can you please show me an example code how I could do that?
Reply
#2
does anyone know???????????????
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to detect abnormal data in big database python vanphuht91 5 1,064 Jun-27-2023, 11:22 PM
Last Post: Skaperen
  Database that can compress a column, or all data, automatically? Calab 3 1,120 May-22-2023, 03:25 AM
Last Post: Calab
  Basic SQL query using Py: Inserting or querying sqlite3 database not returning data marlonbown 3 1,304 Nov-08-2022, 07:16 PM
Last Post: marlonbown
  glTexImage2D is having problem loading img [update] buzzdarkyear 0 1,320 Jan-12-2022, 04:44 PM
Last Post: buzzdarkyear
  Problem With Database Calls and Load Timbo03 1 2,091 Nov-21-2021, 10:48 AM
Last Post: Timbo03
  simple html page with update data korenron 3 2,586 Nov-15-2021, 09:31 AM
Last Post: jamesaarr
  Get last row of SQL database and update Turtle 5 3,046 Oct-14-2021, 07:06 PM
Last Post: Turtle
  I need help parsing through data and creating a database using beautiful soup username369 1 1,684 Sep-22-2021, 08:45 PM
Last Post: Larz60+
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 2,115 Jul-02-2021, 02:19 PM
Last Post: xtc14
  Looking for data/info on a perticular data-proccesing problem. MvGulik 9 3,780 May-01-2021, 07:43 AM
Last Post: MvGulik

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020