Basic pySQLite example?

Question

Gang, I am starting to play with pySQLite, and I'm trying to find an example that illustrates how to query db for existing records before inserting a new record if it does not already exist in db. I feel that I do not notice a very elementary 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 line once:

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

Re-attempting to re-install the line 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 a record only if a 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 for multiple fields, use something like

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

Here are links to information on

• INSERT OR IGNORE
• UNIQUE restrictions