Updating records 1 to n on an SQLite table

[KevinBrown]

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