Python Forum
Updating records 1 to n on an SQLite table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Updating records 1 to n on an SQLite table
#1
The attached script is to update an SQLite table (clipboard) with a new item in the following manner.


There a maximum number of rows of 50.
Records 49 to 2 are saved to 'shuffled' down one row.
The new record is updated into row = 2
Row = 1 is always maintained as a blank record.


I have used an iterative process but is this a good solution for example for 300 rows?

Should I be minimizing the connections an iterations to SQLite and be processing the bulk data with a script ?

I would appreciate any comments or advice on other more elegant methods.


def AddToClpBrdShuffleDown():

    sqlite_file = 'mydatabase.sqlite'     # name of the sqlite database file
    table_name1 = 'VendClpBrd'            # name of the table
          
    id_col  = 'rowid'
    Vend_01 = 'Vend_01'


    # Connecting to the database file
    conn = sqlite3.connect('/Users/Kevin/Documents/mydatabase.sqlite')
    c = conn.cursor()
    
       #  NB NB +++  stp start        reversed(range( 1,5)) yeilds decreasing 4,3,2,1
       #  NB NB +++  start stop ,step range(5,0,-1) yields  decreasing   5,4,3,2,1
       #  NB NB +++  start stop       range(1,5) yeilds increasing 1,2,3,4

       #  Save 49 > 50  48 > 49 etc to 1 to 2   make 1 blank
    for MRcrd in reversed(range( 1,49)):
        
           
           #  Check if MRcrd ( primary key ) exists
           c.execute("SELECT * FROM {tn} WHERE {idf}= ?".format(tn=table_name1,idf = id_col), (MRcrd,))
           id_exists = c.fetchone()

           if MRcrd == 1 :   # Ensure record 1 is blank
              CBVend = ''
              CBRcrd = MRcrd
              c.execute("UPDATE {tn} SET {c01}= ? WHERE {idf}=?". format(tn=table_name1, c01 = Vend_01, idf = id_col),(CBVend,CBRcrd))


           if id_exists:     # Save 49 to 50 etc
            CBVend =(id_exists[0])   # Obtain Vendor data from MRcrd
            CBRcrd = MRcrd + 1       # Index + 1 to save to next primary key
           
            c.execute("UPDATE {tn} SET {c01}= ? WHERE {idf}=?". format(tn=table_name1, c01 = Vend_01, idf = id_col),(CBVend,CBRcrd))

            conn.commit()
            
           else:
            print ('Does not exit ')
            conn.close()
Reply
#2
Is there some reason you are using SQL for this? This sort of thing I would process in memory rather than in a database.

If you have to use SQL for some reason, I use an auto increment column. Then you can add however much data you need to, and then do one mass delete after adding data based on the value of the auto increment column.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#3
(Mar-30-2019, 03:17 PM)ichabod801 Wrote: Is there some reason you are using SQL for this?

At the end of a work session users need to retain many long lists of vendors, parts, clients purchase order etc. It makes a lot of sense to have such data saved. Previously using earlier software I would have used I/O random access files to store these clipboards.

In general I only need to add one new item at a time ( part , vendor , client etc) and I would like this new item to be near top of list (row 2). Additionally I often want to have items that I added last week near the top of the list, whereas items added 4-5 weeks ago would be well down a 300 item list.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Sqlite not updating angus1964 21 2,317 Jul-07-2023, 06:26 PM
Last Post: deanhystad
  Strategy on updating edits back to data table and object variables hammer 0 1,163 Dec-11-2021, 02:58 PM
Last Post: hammer
  UPDATE SQLITE TABLE - Copy a fields content to another field. andrewarles 14 4,246 May-08-2021, 04:58 PM
Last Post: ibreeden
  Unable to Update SQLite Table sambanerjee 5 2,865 Sep-30-2020, 12:21 PM
Last Post: Larz60+
  How to create db table with SQLite and SQLAlchemy?? marcello86 1 2,272 Sep-02-2020, 03:05 PM
Last Post: marcello86
  how to use items combobox in table name sqlite in python hampython 1 2,624 May-24-2020, 02:17 AM
Last Post: Larz60+
  HELP: Combine records in a table with missing start or end date Techy_RN 1 1,606 Dec-16-2019, 08:51 PM
Last Post: Clunk_Head
  sqlite: INSERT using a variable for table name DT2000 3 6,520 Feb-23-2019, 06:28 AM
Last Post: DT2000

Forum Jump:

User Panel Messages

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