0

I have looked around for the solution to my problem a lot and haven't found anything. The problem is with inserting a new record into a database table that has an auto increment primary key. Below is the different scenarios and behaviors that I'm getting. Sample code.

model = QSqlTableModel(db = db, table = 'table')
record = model.record()
record.setGenerated('id', False)
record.setValue('name', 'a_name')
model.insertRecord(-1, record)

Scenario 1: Pre-populated table with some rows.

| id | name |
|----|------|
| 1  | name1|
| 2  | name2|

behavior 1: insertRecord does not add a new record to the table. If I remove the setGenerated flag and manually specify an id [record.setValue('id', 3)] I can insert a new record.

Scenario 2: Pre-populated table with some rows and first id not starting at 1.

| id | name |
|----|------|
| 3  | name1|
| 4  | name2|

behavior 2: first insertRecord adds a new record with id = 1, the second insertRecord adds another record with id = 2 . After that insertRecord does not add any new records. It will try to add a new record with id = 3, but id = 3 already exist and it won't jump to the next available id (id = 5). Again if I remove the setGenerated flag and manually specify the next available id [record.setValue('id', 5)] it works.

Scenario 3:

| id | name |
|----|------|

behavior 3: Empty table without any rows. insertRecord will start at id = 1 and continues to add new records with no problem.

How can I fix this problem without having to manually specify an auto incremented id?

eyllanesc
  • 235,170
  • 19
  • 170
  • 241
nanda
  • 71
  • 6
  • why do you `record.setGenerated('id', False)`? – mugiseyebrows Mar 27 '21 at 14:15
  • record.SetGenerated(field, False) is used for handling auto incement fields. By default the insertRecord adds a new row with empty values in all cells. Then you have to pass the values to fill the cells. For the auto generated field you want to tell it not to add a value into the cell and allow the database to handle it. I've also tried to use record.remove(0) to remove the auto increment field and also tried to pass a null value record.setValue('id', '') but neither works. – nanda Mar 27 '21 at 14:36
  • @nanda Try: `record.remove(record.indexOf('id'))` and get rid of `setGenerated`. – ekhumoro Mar 27 '21 at 14:39
  • @ ekhumoro : I've tried this as well but it doesn't work. – nanda Mar 27 '21 at 14:44
  • @nanda You could publish the SQL command with which you create the table and what database you are using (also the version of DB, PyQt and OS) – eyllanesc Mar 27 '21 at 16:58
  • I was able to solve the problem. I had to solve the problem at the database level. The primary key column was "out of sync". Because I moved the table from sqlite to PostgeSQL (already had some id values) and the database was trying to start from 1. Once I fixed the primary key sequence the problem was resolved. – nanda Mar 27 '21 at 19:54

0 Answers0