Basic pySQLite example?

Question

Gang, I am beginning to play around with pySQLite and I'm trying to find an example that illustrates how to query the db for existing records before inserting a new record if it doesn't already exist in the db. I feel I am overlooking a very basic function.

Thanks!

Answer 1

Use the UNIQUE keyword when creating the table, and use INSERT OR INGORE to insert only if the record is "new" (unique):

connection=sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('CREATE TABLE foo (bar INTEGER UNIQUE, baz INTEGER)')

Here we insert the row once:

cursor.execute('INSERT INTO foo (bar,baz) VALUES (?, ?)',(1,2))

Trying to insert the row again fails:

try:
    cursor.execute('INSERT INTO foo (bar,baz) VALUES (?, ?)',(1,2))
except sqlite3.IntegrityError as err:
    print(err)
    # sqlite3.IntegrityError: column bar is not unique

INSERT OR IGNORE inserts the record only if it UNIQUE constraint is passed:

cursor.execute('INSERT OR IGNORE INTO foo (bar,baz) VALUES (?, ?)',(1,3))

cursor.execute('SELECT * from foo')
data=cursor.fetchall()
print(data)
# [(1, 2)]    

To create a UNIQUE index on multiple fields, use something like

cursor.execute('''
    CREATE TABLE foo (bar INTEGER, baz INTEGER, bing INTEGER, UNIQUE (bar, baz))''')

Here are links to info on

1. INSERT OR IGNORE
2. UNIQUE constraints