mruocco mruocco - 2 months ago 7
Python Question

sqlite3 INSERT IF NOT EXIST (with Python)

First of all, I am really super-new so I hope that I will be able to post the question correctly. Please tell me if there is any problem.

Now, here is my question: I would like to fill a database with a data, only if it doesn't already exist in it. I searched for this topic and I think I found correct the answer (you can read one example here: ["Insert if not exists" statement in SQLite) but I need to write these simple command line in python.. and that's my problem. (I anticipate that I am quite new in Python too)

So, here is what I did:

self.cur.execute("INSERT INTO ProSolut VALUES('a','b','c')")
self.cur.execute("SELECT * FROM ProSolut")
self.cur.execute("WHERE NOT EXISTS (SELECT * FROM ProSolut WHERE VALUES = ('a','b','c'))")


and here's the error:

[ERROR] behavior.box :_safeCallOfUserMethod:125 _Behavior__lastUploadedChoregrapheBehaviorbehavior_1142022496:/ProSolutDB_11: Traceback (most recent call last): File "/usr/lib/python2.7/site-packages/albehavior.py", line 113, in _safeCallOfUserMethod func(functionArg) File "<string>", line 45, in onInput_onStart OperationalError: near "WHERE": syntax error


so basically I think there is some problem with the bracket "(" in the 3rd string. --> ("OperationalError: near "WHERE": syntax error")

I know that probably it's a stupid error.
If you can help me, I would really appreciate.

Thank you so much

Answer

Assuming a is in a column called "Col1", b is in "Col2" and c is in "Col3", the following should check for the existence of such a row:

self.cur.execute('SELECT * FROM ProSolut WHERE (Col1=? AND Col2=? AND Col3=?)', ('a', 'b', 'c'))
entry = self.cur.fetchone()

if entry is None:
    print 'No entry found'
else:
    print 'Entry found'

This selects all entries in ProSolut that match these values. fetchone then tries to grab a result of this query - if there are no such matches then it returns None.

EDIT: In line with Barmar's comment, to make this insert the values, adapt to the following:

self.cur.execute('SELECT * FROM ProSolut WHERE (Col1=? AND Col2=? AND Col3=?)', ('a', 'b', 'c'))
entry = self.cur.fetchone()

if entry is None:
    self.cur.execute('INSERT INTO ProSolut (Col1, Col2, Col3) VALUES (?,?,?)', ('a', 'b', 'c'))
    print 'New entry added'
else:
    print 'Entry found'

You'll need to make sure you commit() your changes too!

Comments