how to build a index table(python dict like) in python with sqlite3

Question

Suppose I have one string list may have duplicated items:

A
B
C
A
A
C
D
E
F
F

I want to make a list can assign an unique index for each item, looks like:

1   A
2   B
3   C
4   D
5   E
6   F

now I created sqlite3 database with below SQL statement:

CREATE TABLE aa ( myid INTEGER PRIMARY KEY AUTOINCREMENT,
                  name STRING, 
                  UNIQUE (myid) ON CONFLICT FAIL,
                  UNIQUE (name) ON CONFLICT FAIL);

The plan is insert each row into the database in python.

My question is how to handle the error when conflict do happened when insert in python module sqlite3? For example: the program will printing a warning message which item is conflicted and continue next insert action when inserting in python?

Thanks

Answer 1

You could use a set, to filter double entries in your list and just insert the values:

>>> set(['A', 'B', 'C', 'D', 'A', 'A', 'A', 1, 2, 1, 3, object(), object()])
set(['A', 1, 'C', 'B', 'D', 2, <object object at 0x100274590>, 3, <object object
 at 0x1002746a0>])

or use a try-except clause on each commit to check, whether the value is already in the database.

Answer 2

Why not try it out and check which Exception is raised? That's not too hard I think, but...

try:
    conn.excecute('...')
except IntegrityError:
    pass