Python Forum

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()

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.

(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.