Python Forum
Search the data to update in a database
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Search the data to update in a database
#1
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
Reply
#2
What type of database?
If sqlite, and this is a one shot deal, you can use sqliteman: https://sourceforge.net/projects/sqliteman/
If another database, they all have admin tools
Reply
#3
(Oct-26-2016, 07:35 PM)Larz60+ Wrote: What type of database?
If sqlite, and this is a one shot deal, you can use sqliteman: https://sourceforge.net/projects/sqliteman/
If another database, they all have admin tools


The type of database I use is sqlite3. Can you post the code of what I should use to find and replace the data in a database of what I have already explained in my first post??
Reply
#4
(Oct-26-2016, 09:36 PM)chris0147 Wrote: Can you post the code of what I should use to find and replace the data in a database of what I have already explained in my first post??
If you want someone to write code for you, post it in the jobs section. Make sure to include how much money your offering. If you want help here, you provide the code, and we will help you shape it to work. But we will not do work for you.
Recommended Tutorials:
Reply
#5
Hi again,

I asked if this was a one shot deal. The reason is that if so, you could make the changes manually with sqliteman.
That being said, are you getting any errors from your code, if so please post.

I'm not sure about sqlite3, but other databases Oracle for one (at least this used to be so) didn't allow changing the id
of a row in place, if that row was indexed by the field you were trying to changed.
You had to do one of two things:
delete the row and re-insert
or
remove the index, make the change, and reload the index.

obviously on a large database the first option is the one you would use.
I'd try it this way:

1. read the row and save the results.
2. delete the row.
3. insert the saved row (with the changes).
Reply
#6
Can't you do something like:
DELETE from programs where program_id=3001
UPDATE programs SET program_id=program_id -1 WHERE program_id is not null
And to fill the new value, you normally use a sequence
Unless noted otherwise, code in my posts should be understood as "coding suggestions", and its use may require more neurones than the two necessary for Ctrl-C/Ctrl-V.
Your one-stop place for all your GIMP needs: gimp-forum.net
Reply
#7
Quote:Oracle for one (at least this used to be so) didn't allow changing the id

of a row in place, if that row was indexed by the field you were trying to change

This used to be the case, Update may do an index update as well as the data now, and probably does allow it.
I think you could do it then as well, but had to do some manual binding.

Working in telecommunications, we avoided Oracle, Sybase or Informix or any other relational database for any call processing because
they were too slow and couldn't process 80+ million calls per day. We used them for billing and such, but not for call processing.
Instead, we had our own home grown random access file system with (hashed) indexes built in.
Reply
#8
(Oct-26-2016, 10:38 PM)Larz60+ Wrote: Hi again,

I asked if this was a one shot deal. The reason is that if so, you could make the changes manually with sqliteman.
That being said, are you getting any errors from your code, if so please post.

I'm not sure about sqlite3, but other databases Oracle for one (at least this used to be so) didn't allow changing the id
of a row in place, if that row was indexed by the field you were trying to changed.
You had to do one of two things:
delete the row and re-insert
or
remove the index, make the change, and reload the index.

obviously on a large database the first option is the one you would use.
I'd try it this way:

1. read the row and save the results.
2. delete the row.
3. insert the saved row (with the changes).

The "id" isn't a primary key since it is absent in the new rows...

Of course, here we just answer the questions asked, but it is likely that these stem from not-so-good design.

A very interesting read
Unless noted otherwise, code in my posts should be understood as "coding suggestions", and its use may require more neurones than the two necessary for Ctrl-C/Ctrl-V.
Your one-stop place for all your GIMP needs: gimp-forum.net
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,306 Nov-08-2022, 07:16 PM
Last Post: marlonbown
  simple html page with update data korenron 3 2,587 Nov-15-2021, 09:31 AM
Last Post: jamesaarr
  Get last row of SQL database and update Turtle 5 3,048 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,688 Sep-22-2021, 08:45 PM
Last Post: Larz60+
  SaltStack: MySQL returner save less data into Database table columns xtc14 2 2,116 Jul-02-2021, 02:19 PM
Last Post: xtc14
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 2,628 Dec-16-2020, 05:26 AM
Last Post: Vokofe
Photo Update database in tkinter shahulvk 3 3,090 Oct-24-2020, 04:48 PM
Last Post: shahulvk
  Looping to read data in database CEC68 1 1,679 Sep-24-2020, 08:54 PM
Last Post: scidam

Forum Jump:

User Panel Messages

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