Mar-30-2019, 02:58 PM
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.
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()