Basic pySQLite example?

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!

Solution

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