Python Forum
Move the data up to the next row in a database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Move the data up to the next row in a database
#1
Hi all,
I need some help with my code, I am using the code to write data to input them in the database where I will move the data down from the same row as the other data.

Example:

=======================================================
name           |   start_date   |    stop_date      |   program_id     |  programme_title         |
=======================================================
channel 1                                                          3001                  some name 1
channel 1                                                          3002                  some name 2
channel 1                                                          3003                  some name 3


Here is what it will move the program_id to the next row that go down:

=======================================================
name           |   start_date   |    stop_date      |   program_id     |  programme_title         |
=======================================================
channel 1                                                                                   some name
channel 1                                                          3001                  some name 2
channel 1                                                          3002                  some name 3


This is the code I use:

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("UPDATE programs set program_id=? WHERE program_id=?",('',value))
             else:
                 cur.execute("UPDATE programs set program_id=? WHERE program_id=?",(value-1,value))
             value = int(value) + 1
         cur.execute("SELECT channel , stop_date FROM programs WHERE program_id=?;",(value-2,))
         data = cur.fetchone()
         try:
             cur.execute("UPDATE programs set program_id=? WHERE channel=? and start_date=? ",(value-1,data[0],data[1]))
         except:
             pass
         conn.commit()
         conn.close()
I want to know how I can move the data up in the opposite than the code I use as I want to move the data up?

Example:

=======================================================
name           |   start_date   |    stop_date      |   program_id     |  programme_title         |
=======================================================
channel 1                                                                                   some name
channel 1                                                          3001                  some name 2
channel 1                                                          3002                  some name 3



Here is what it will move the program_id to the next row that go up:

=======================================================
name           |   start_date   |    stop_date      |   program_id     |  programme_title         |
=======================================================
channel 1                                                          3001                  some name 1
channel 1                                                          3002                  some name 2
channel 1                                                          3003                  some name 3


​​​​​​​
Do you know how I could do that?
Reply
#2
Are they always sequential like that?  Because I think just writing sql will replace the entire python snippet: update programs set program_id=program_id-1
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
  shutil.move make data corrupt kucingkembar 0 743 Feb-01-2023, 01:30 PM
Last Post: kucingkembar
  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
  Move a particular row in pandas data frame to last row klllmmm 0 3,646 Dec-27-2021, 09:11 AM
Last Post: klllmmm
  I need help parsing through data and creating a database using beautiful soup username369 1 1,682 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
  Looping to read data in database CEC68 1 1,679 Sep-24-2020, 08:54 PM
Last Post: scidam
  Get database used data space from pyodbc susja 1 2,202 Aug-14-2020, 02:01 PM
Last Post: susja
  sqlite3 database does not save data across restarting the program SheeppOSU 1 3,405 Jul-24-2020, 05:53 AM
Last Post: SheeppOSU

Forum Jump:

User Panel Messages

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