Ошибка при импорте данных unique constraint failed

I am trying to create a database which allows users to create ‘to do’ lists and fill them with items to complete. However, when inserting data into the tables it gives me a UNIQUE constraint failed error and I don’t know how to solve it. This is my code for creating the database and inserting data.

CREATE TABLE user (
    user_id integer  NOT NULL   PRIMARY KEY,
    first_name varchar(15)  NOT NULL,
    title varchar(5)  NOT NULL,
    username varchar(15)  NOT NULL,
    password varchar(20)  NOT NULL,
    email varchar(50)  NOT NULL,
    bio text  NOT NULL
);


CREATE TABLE list (
    list_id integer  NOT NULL   PRIMARY KEY,
    list_name varchar(10)  NOT NULL,
    user_user_id integer  NOT NULL,
    FOREIGN KEY (user_user_id) REFERENCES user(user_id)
);


CREATE TABLE item (
    item_id integer  NOT NULL   PRIMARY KEY,
    item text  NOT NULL,
    completed boolean  NOT NULL,
    list_list_id integer  NOT NULL,
    FOREIGN KEY (list_list_id) REFERENCES list(list_id)
);


-- Data:
INSERT INTO user VALUES (1, "Name1", "Title1", "Username1", "Password1", "Email1", "Bio1");
INSERT INTO user VALUES (2, "Name2", "Title2", "Username2", "Password2", "Email2", "Bio2");
INSERT INTO user VALUES (3, "Name3", "Title3", "Username3", "Password3", "Email3", "Bio3");

INSERT INTO list VALUES (1, "user1-list1", 1);
INSERT INTO list VALUES (2, "user1-list2", 1);
INSERT INTO list VALUES (3, "user1-list3", 1);
INSERT INTO list VALUES (1, "user2-list1", 2);
INSERT INTO list VALUES (1, "user3-list1", 3);
INSERT INTO list VALUES (2, "user3-list2", 3);

INSERT INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);

I have copied the errors I receive below:

Error: near line 43: UNIQUE constraint failed: list.list_id
Error: near line 44: UNIQUE constraint failed: list.list_id
Error: near line 45: UNIQUE constraint failed: list.list_id
Error: near line 49: UNIQUE constraint failed: item.item_id
Error: near line 50: UNIQUE constraint failed: item.item_id
Error: near line 51: UNIQUE constraint failed: item.item_id
Error: near line 52: UNIQUE constraint failed: item.item_id
Error: near line 53: UNIQUE constraint failed: item.item_id
Error: near line 54: UNIQUE constraint failed: item.item_id
Error: near line 55: UNIQUE constraint failed: item.item_id

Any help would be appreciated!

Я создала несколько софтов все они должны работать с одной БД. Два из них работали нормально, позже один стал ругаться на запрос на обновление данных в БД с ошибкой constraint failed
UNIQUE constraint failed: moz_cookies.name, moz_cookies.host, moz_cookies.path, moz_cookies.originAttributes. Кто сталкивался, что посоветуете? Код прилагаю:
SQLiteCommand sqLiteCommand = new SQLiteCommand();
SQLiteConnection qLiteConnection = new SQLiteConnection(@»DataSource= » + profileCookies);

sqLiteCommand.CommandText =
«UPDATE moz_cookies SET Id=’»+cookie.Id+»‘,BaseDomain = ‘» + cookie.BaseDomain + «‘, » +
«originAttributes=’» + cookie.OriginAttributes + «‘,» +
«name=’» + cookie.Name + «‘, value=’» + cookie.Value + «‘, » +
«host=’» + cookie.Host + «‘,path=’» + cookie.Path + «‘, » +
«expiry=’» + cookie.Expiry + «‘, lastAccessed=’» + cookie.LastAccessed + «‘, » +
«creationTime=’» + cookie.CreationTime + «‘, isSecure=’» + cookie.IsSecure + «‘, » +
«isHttpOnly=’» + cookie.IsHttpOnly + «‘, appId=’» + cookie.AppID + «‘, » +
«inBrowserElement=’» + cookie.InBrowserElement + «‘»;
qLiteConnection.Open();
sqLiteCommand.Connection = SqLiteConnection;
sqLiteCommand.ExecuteNonQuery();
qLiteConnection.Close();

of which I only desire to edit a small number.

One issue is that a table can have what is called an auto-increment field. This is a field which is (putting it basically) a record number for that table. It can either be a simple auto-increment field, or an advanced one. With a simple auto increment field, lets say you add a record number 100. Then delete it. If you add another new record, that will now be record 100. With an advanced auto-increment field, that new record will actually be 101. SQLite knows it used 100 previously, so it won’t use it again.

The reason I’m wobbling on about this, is that if you deleted 100 records from your ‘orders’ table and re-imported, they’ll either start again at 1, or they could continue from 101. This could potentially break the rest of your database — another record could be looking at record 45, but that record no longer exists — its now 145. This all depends on your schema.

Obviously, take backups of your databases before deleting / changing anything!

Yes I am trying to overwrite the existing table

In DB4S you can export just one table to SQL, do what you need to do, delete that table in DB4S and re-import just that table again from the SQL file.

If you delete just the records from the table, you could be there a while — thats a very slow process. Its much quicker to delete the table and reimport it from the SQL file.

SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.

In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.

This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.

By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.

But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.

One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.

Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.

The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.

Sample Table

Let’s create a simple table and add one row.

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName, 
    Price
);

INSERT INTO Products VALUES (1, 'Hammer', 8.00);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       

We currently have one row, with a ProductId of 1.

Now we can run through the various scenarios of inserting data into that table that violates the primary key constraint.

Example 1 – Abort (Default Behaviour)

As mentioned, the default behaviour for SQLite is to abort the INSERT operation and return an error.

INSERT INTO Products VALUES (1, 'Wrench', 12.50);

Result:

Error: UNIQUE constraint failed: Products.ProductId

An error was returned and nothing was inserted.

This is the equivalent of using the OR ABORT option.

INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 12.50);

Result:

Error: UNIQUE constraint failed: Products.ProductId

We can verify that nothing was inserted by running a SELECT statement against the table.

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       

We can see that the table only contains the original row.

Example 2 – Ignore

One alternative is to have SQLite ignore the offending row. In other words, it will skip over the row and continue processing subsequent rows.

To do this within your INSERT statement, use OR IGNORE.

The effect of this is that the INSERT operation succeeds, but without any rows that violate the primary key constraint.

INSERT OR IGNORE INTO Products VALUES 
  (1, 'Hammer', 12.00),
  (2, 'Nails', 2.50),
  (3, 'Saw', 10.50),
  (1, 'Wrench', 22.50),
  (5, 'Chisel', 23.00),
  (6, 'Bandage', 120.00);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       
2           Nails        2.5       
3           Saw          10.5      
5           Chisel       23.0      
6           Bandage      120.0     

In this case I tried to insert two new rows with an ID that already existed in the table, so both of those rows were skipped.

Example 3 – Replace

Another option you have is to replace the original row with the new row.

In other words, you will overwrite the existing data with your new data.

To do this, use OR REPLACE.

INSERT OR REPLACE INTO Products VALUES 
  (1, 'Hammer', 12.00),
  (2, 'Nails', 2.50),
  (3, 'Saw', 10.50),
  (1, 'Wrench', 22.50),
  (5, 'Chisel', 23.00),
  (6, 'Bandage', 120.00);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Wrench       22.5      
2           Nails        2.5       
3           Saw          10.5      
5           Chisel       23.0      
6           Bandage      120.0     

In this case most rows were the same, so they contain the same data after the INSERT operation. However, we can see that the first row has been updated to use the values in my INSERT statement.

We can also see that it used the second set of values (seeing as two shared the same ProductId).

So the effect is kind of like an UPDATE statement and INSERT statement combined.

Example 4 – Rollback

Another option is to use the ROLLBACK option.

This aborts the current SQL statement with an SQLITE_CONSTRAINT error and rolls back the current transaction. If no transaction is active (other than the implied transaction that is created on every command) then it works the same as the ABORT algorithm.

It pays to be mindful of how this option works. Here’s an example that uses multiple INSERT OR ROLLBACK statements within a transaction.

DELETE FROM Products;

BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
  
SELECT * FROM Products;

Here’s the full output from my terminal when I run this:

sqlite> BEGIN TRANSACTION;
sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
Error: UNIQUE constraint failed: Products.ProductId
sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
sqlite> COMMIT;
Error: cannot commit - no transaction is active
sqlite>   
sqlite> SELECT * FROM Products;
ProductId   ProductName  Price     
----------  -----------  ----------
5           Chisel       23.0      
6           Bandage      120.0     
sqlite> 

Basically what here happened is that, it has gotten as far as the constraint violation, then rolled back the transaction. Then the next two lines were processed and then the COMMIT keyword was encountered. By then, the transaction had already been rolled back and so we got another error telling us that no transaction was active.

Here’s what happens if I remove it from the transaction.

DELETE FROM Products;

INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
  
SELECT * FROM Products;

Here’s the full output from my terminal when I run this:

sqlite> DELETE FROM Products;
sqlite> 
sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
Error: UNIQUE constraint failed: Products.ProductId
sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
sqlite>   
sqlite> SELECT * FROM Products;
ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       
2           Nails        2.5       
3           Saw          10.5      
5           Chisel       23.0      
6           Bandage      120.0     
sqlite>

In this case, it worked like ABORT.

To demonstrate this, here’s the same statement using ABORT instead of ROLLBACK.

DELETE FROM Products;

INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
  
SELECT * FROM Products;

Here’s the full output from my terminal when I run this:

sqlite> DELETE FROM Products;
sqlite> 
sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00);
sqlite> INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50);
sqlite> INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50);
sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50);
Error: UNIQUE constraint failed: Products.ProductId
sqlite> INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
sqlite> INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
sqlite>   
sqlite> SELECT * FROM Products;
ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       
2           Nails        2.5       
3           Saw          10.5      
5           Chisel       23.0      
6           Bandage      120.0     
sqlite> 

The Fail Option

The FAIL option aborts the current SQL statement with an SQLITE_CONSTRAINT error. But this option does not back out prior changes of the SQL statement that failed nor does it end the transaction.

DELETE FROM Products;

INSERT OR FAIL INTO Products VALUES 
  (1, 'Hammer', 8.00),
  (2, 'Nails', 2.50),
  (3, 'Saw', 10.50),
  (1, 'Wrench', 22.50),
  (5, 'Chisel', 23.00),
  (6, 'Bandage', 120.00);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Hammer       8.0       
2           Nails        2.5       
3           Saw          10.5      

I have a database structured like this.

CREATE TABLE "work_order" ( "id" INTEGER, "ServiceTitle" TEXT UNIQUE, 
"Link" TEXT UNIQUE, "Status" TEXT, PRIMARY KEY("id") );

When I try enter some values I get an error.

"sqlite3.IntegrityError: UNIQUE constraint failed: work_order.Link"

Then the program terminates.

Process finished with exit code 1

Even though the value I entered into the Link column is unique.

 conn = sqlite3.connect('WO.db', timeout=10)
 c = conn.cursor()

 c.execute("insert into work_order (ServiceTitle, Link, Status) values 
 (?,?,?)", ('oo', 'll', 'rr'))

 conn.commit()
 conn.close()

The new record still gets inserted to the database exactly as I wanted. I just get this error on pycharm.

I tried inserting the primary key ID also. It’s not auto increment so I can enter it manually so long as it is unique.

conn = sqlite3.connect('WO.db', timeout=10)
c = conn.cursor()

 c.execute("insert into work_order (id, ServiceTitle, Link, Status) values 
(?,?,?,?)", (7, 'kkkk', 'eeeee', 'qqqqq'))

conn.commit()
conn.close()

Again I get an error in pycharm but different this time. The record still gets inserted into the database again though exactly as I wanted. The process then terminates again.

sqlite3.IntegrityError: UNIQUE constraint failed: work_order.id

Process finished with exit code 1.

Any suggestions as to why I’m getting this error even though all of the values i’m entering into these columns are unique values not present in the database?

UPDATE*** I created a new table and removed the unique constraints. I now get no error but the records enter the table twice. Which is what the problem was when there was Unique constraints. I don’t know why there is two records being inserted though.

I have a database structured like this.

CREATE TABLE "work_order" ( "id" INTEGER, "ServiceTitle" TEXT UNIQUE, 
"Link" TEXT UNIQUE, "Status" TEXT, PRIMARY KEY("id") );

When I try enter some values I get an error.

"sqlite3.IntegrityError: UNIQUE constraint failed: work_order.Link"

Then the program terminates.

Process finished with exit code 1

Even though the value I entered into the Link column is unique.

 conn = sqlite3.connect('WO.db', timeout=10)
 c = conn.cursor()

 c.execute("insert into work_order (ServiceTitle, Link, Status) values 
 (?,?,?)", ('oo', 'll', 'rr'))

 conn.commit()
 conn.close()

The new record still gets inserted to the database exactly as I wanted. I just get this error on pycharm.

I tried inserting the primary key ID also. It’s not auto increment so I can enter it manually so long as it is unique.

conn = sqlite3.connect('WO.db', timeout=10)
c = conn.cursor()

 c.execute("insert into work_order (id, ServiceTitle, Link, Status) values 
(?,?,?,?)", (7, 'kkkk', 'eeeee', 'qqqqq'))

conn.commit()
conn.close()

Again I get an error in pycharm but different this time. The record still gets inserted into the database again though exactly as I wanted. The process then terminates again.

sqlite3.IntegrityError: UNIQUE constraint failed: work_order.id

Process finished with exit code 1.

Any suggestions as to why I’m getting this error even though all of the values i’m entering into these columns are unique values not present in the database?

UPDATE*** I created a new table and removed the unique constraints. I now get no error but the records enter the table twice. Which is what the problem was when there was Unique constraints. I don’t know why there is two records being inserted though.

abort at 13 in [INSERT INTO event(totalminutesfrom,dayofweek,title,location,totalminutesto,id) VALUES (?,?,?,?,?,?)]: UNIQUE constraint failed: event.id
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: Error inserting totalminutesfrom=694 dayofweek=null title=qxs location=Eded & Mariz totalminutesto=0 id=0
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: event.id (code 1555)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at com.example.siddhi.timetablelayout.AddEventActivity.onClick(AddEventActivity.java:217)

db.insert(TABLE, null, values);

   db.addEvent(new EventData(eventTitle,dayOfWeek,totalMinutesFrom, totalMinutesTo,location));

public class EventTableHelper extends SQLiteOpenHelper {


    private static final String TABLE = "event";
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";
    private static final String KEY_TOTAL_MINUTES_FROM = "totalminutesfrom";
    private static final String KEY_TOTAL_MINUTES_TO = "totalminutesto";



    public EventTableHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
        //3rd argument to be passed is CursorFactory instance
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        //createTable(db);
    }

    public void createTable(SQLiteDatabase db){
        String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
                + KEY_DAY_OF_WEEK +"TEXT" + KEY_TOTAL_MINUTES_FROM +"INTEGER"
                + KEY_TOTAL_MINUTES_TO + "INTEGER" +  KEY_LOCATION + "TEXT" +  ")";

        db.execSQL(CREATE_EVENTS_TABLE);

    }
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
       // db.execSQL("DROP TABLE IF EXISTS " + TABLE);

      //  createTable(db);

        // Create tables again
        //onCreate(db);
    }

    // code to add the new contact
    public void addEvent(EventData event) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, event.getId());
        values.put(KEY_TITLE,event.getTitle()); // Contact Name
        values.put(KEY_DAY_OF_WEEK,event.getDayofWeek());
        values.put(KEY_TOTAL_MINUTES_FROM,event.getFromMinutes());
        values.put(KEY_TOTAL_MINUTES_TO,event.getToMinutes());
        values.put(KEY_LOCATION,event.getLocation());
        // Inserting Row
        db.insert(TABLE, null, values);
        //2nd argument is String containing nullColumnHack
        db.close(); // Closing database connection
    }

    // code to get the single contact
   EventData getEvent(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE, new String[] { KEY_ID,
                        KEY_TITLE, KEY_DAY_OF_WEEK, KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
       EventData eventData = new EventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2),
               cursor.getInt(3),cursor.getInt(4),cursor.getString(5));

        return eventData;
    }



    // code to get all contacts in a list view
    public List<EventData> getAllEvents() {
        List<EventData> conList = new ArrayList<EventData>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                EventData event = new EventData();

                event.setId(Integer.parseInt(cursor.getString(0)));
                event.setTitle(cursor.getString(1));
                event.setDayofWeek(cursor.getString(2));
                event.setFromMinutes(cursor.getInt(3));
                event.setToMinutes(cursor.getInt(4));
                event.setLocation(cursor.getString(5));
                // Adding contact to list
                conList.add(event);
            } while (cursor.moveToNext());
        }

        // return contact list
        return conList;
    }
}

CREATE TABLE TEST (client_id TEXT unique, remote_id INT unique)

Я слежу за этой документацией о том, как использовать django-import-export:

https://django-import-export.readthedocs.io/en/latest/getting_started.html#declaring-fields

---

У меня есть таблица Excel, которая выглядит как показано ниже

---

Я хочу хранить данные в этой модели:

class ExcelData(models.Model):
    var1 = models.CharField(max_length=200)
    var2 = models.CharField(max_length=200,unique=True)
    var3 = models.CharField(max_length=200)
    var4 = models.CharField(max_length=200)

---

Вот как далеко я зашел:

@admin.register(ExcelData)
class ViewAdmin(ImportExportModelAdmin):
    exclude = ('id',)

class ExcelDataResource(resources.ModelResource):
    var1 = Field(attribute='var1', column_name='Name')
    var2 = Field(attribute='var2', column_name='SAP_ID')
    var3 = Field(attribute='var3', column_name='Abbreviation')
    var4 = Field(attribute='var4', column_name='Max. Capa')

    class Meta:
        model = ExcelData
        import_id_fields = ('var2',)
        exclude = ('id',)

---

Вот что я получаю:

---

Вот CSV-файл:

http://www.sharecsv.com/s/9d1112398cv.com/s/9d1112398c/s/9d1112392c8 >

---

---

Когда я пытаюсь импортировать несколько строк следующим образом:

---

Я получаю такую ​​ошибку:

---

Line number: 2 - UNIQUE constraint failed: myapp_exceldata.var2
b, e, h, k
Traceback (most recent call last):
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendssqlite3base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "D:Users...envlibsite-packagesimport_exportresources.py", line 527, in import_row
self.save_instance(instance, using_transactions, dry_run)
File "D:Users...envlibsite-packagesimport_exportresources.py", line 320, in save_instance
instance.save()
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 746, in save
force_update=force_update, update_fields=update_fields)
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 784, in save_base
force_update, using, update_fields,
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 887, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 926, in _do_insert
using=using, raw=raw,
File "D:Users...envlibsite-packagesdjangodbmodelsmanager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "D:Users...envlibsite-packagesdjangodbmodelsquery.py", line 1204, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "D:Users...envlibsite-packagesdjangodbmodelssqlcompiler.py", line 1384, in execute_sql
cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 100, in execute
return super().execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbutils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendssqlite3base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

Line number: 3 - UNIQUE constraint failed: myapp_exceldata.var2
c, f, i, l
Traceback (most recent call last):
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendssqlite3base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "D:Users...envlibsite-packagesimport_exportresources.py", line 527, in import_row
self.save_instance(instance, using_transactions, dry_run)
File "D:Users...envlibsite-packagesimport_exportresources.py", line 320, in save_instance
instance.save()
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 746, in save
force_update=force_update, update_fields=update_fields)
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 784, in save_base
force_update, using, update_fields,
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 887, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "D:Users...envlibsite-packagesdjangodbmodelsbase.py", line 926, in _do_insert
using=using, raw=raw,
File "D:Users...envlibsite-packagesdjangodbmodelsmanager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "D:Users...envlibsite-packagesdjangodbmodelsquery.py", line 1204, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "D:Users...envlibsite-packagesdjangodbmodelssqlcompiler.py", line 1384, in execute_sql
cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 100, in execute
return super().execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbutils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "D:Users...envlibsite-packagesdjangodbbackendsutils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "D:Users...envlibsite-packagesdjangodbbackendssqlite3base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: UNIQUE constraint failed: myapp_exceldata.var2

---

Вот CSV-файл:

http://www.sharecsv.com/s/3f169eb14full_ru.html/sharecsv.com/s/3f09eb149f03f09eb149f03f03d03d03d06d5 >

---

Спасибо за любые предложения