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'))")
[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
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
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!