Python sqlite3: INSERT into table WHERE NOT EXISTS, using ? substitution parameter

Question

I'm creating a table of descriptions from a list of not necessarily unique descriptions. I would like the table to contain only distinct descriptions, so while inserting descriptions into the table, I need to check to see if they already exist. My code(simplified) looks something like as follows:

cur.execute(''' CREATE TABLE descriptions
            (id INTEGER PRIMARY KEY AUTOINCREMENT, desc TEXT)''')

descripts  = ["d1", "d2", "d3", "d4", "d3", "d1", "d5", "d6", "d7", "d2"]
cur.executemany('''
    INSERT INTO descriptions(desc) 
    VALUES (?) 
    WHERE NOT EXISTS (
        SELECT * 
        FROM descriptions as d 
        WHERE d.desc=?) 
        ''', zip(descripts, descripts))

The result is OperationalError: near "WHERE": syntax error, and I'm not sure exactly where I'm going wrong.

Just a note: I realize I could solve this using a set() structure in python, but for academic reasons this is not permitted.

Thanks

Antoine Fontaine · Accepted Answer · 2016-10-06 15:45:36Z

2

To replace VALUES by SELECT should work

cursor.executemany('''
    INSERT INTO descriptions(desc)
    SELECT (?)
    WHERE NOT EXISTS (
        SELECT *
        FROM descriptions as d
        WHERE d.desc=?)''', 
    zip(descripts, descripts))

answered Oct 6, 2016 at 15:45

Antoine Fontaine

8128 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python sqlite3: INSERT into table WHERE NOT EXISTS, using ? substitution parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related