I have created a table called Employees with 3 columns namely names,exp and samples and I am trying to insert values individually as below:
test = Company()
test.details()
DML = '''INSERT INTO Employees (Names,Exp) VALUES(?,?)'''
data = list(zip_longest(test.names,test.exps,fillvalue= None))
self.curr.executemany(DML,data)
self.curr.execute('''UPDATE Employees SET Samples = 'sample1' ''')
self.conn.commit()
Company is the class created in another python file and it has a corresponding function details() which is being called in the current file. Names and Exp columns have list of values stored in test.names and test.exps respectively. When executed the values are properly being inserted but the third column Samples varies for each insertion hence I need to populate it individually. When I execute the above:
# In the db
Names | Exp | Samples
John | 2 | sample1
Cena | 4 | sample1
Tom | 6 | sample1
Since, names and exps are list I am changing the list values in the other file for each insertion and its working as expected. For the above John,Cena,Tom are first list values of test.names. Similarly, 2,4,6 are first list values of test.exps Each time I insert the values for a particular list the sample value remains same as in:
# In the db First insertion
Names | Exp | Samples
John | 2 | sample1
Cena | 4 | sample1
Tom | 6 | sample1
# Second insertion (expected)
Names | Exp | Samples
John | 2 | sample1
Cena | 4 | sample1
Tom | 6 | sample1
Meg | 3 | sample2
Cena | 4 | sample2
Renu | 6 | sample2
But each time I insert the value Names and Exp are perfect but the Samples column value gets replaced by new ones as in:
Names | Exp | Samples
John | 2 | sample2
Cena | 4 | sample2
Tom | 6 | sample2
Meg | 3 | sample2
Cena | 4 | sample2
Renu | 6 | sample2
I also tried with self.curr.execute('''UPDATE Employees SET Samples = 'sample1' where Samples = NULL ''') but no luck. Is there any way I can update sample values without affecting the old values. PS: I do not want to delete or replace the old values.