sqlite3 INSERT IF NOT EXIST (with Python)

Question

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

---

E.G.: I forgot to say that I am using the software Choregraphe, which uses the Python language to construct all the functional blocks. That means that, even if the language is basically Python, sometimes the semantic is not perfectly the same. I hope that this post can help someone in the future.

Solution 1

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!

Solution 2

First, you need to combine everything into a single self.cur.execute() call. Each call to this must be a complete query, they're not concatenated to each other.

Second, you can't have both VALUES and SELECT as the source of data in an INSERT query, it has to be one or the other.

Third, you don't want to select from your table as the source of the data, as that will insert a new row for every row in the table that matches the WHERE expression (which is either all or none, because the WHERE expression doesn't refer to anything in the row being selected). You just want to select the values by themselves.

this.cur.execute("""
    INSERT INTO ProSolut (col1, col2, col3)
    SELECT 'a', 'b', 'c'
    WHERE NOT EXISTS (SELECT * FROM ProSolut WHERE col1 = 'a', col2 = 'b', col3 = 'c';
    """)

Replace col1, col2, col3 with the actual names of the columns you're filling in.

If any or all of the columns are a unique key in the table, you could just use INSERT OR IGNORE:

this.cur.execute("""
    INSERT OR IGNORE INTO ProSolut (col1, col2, col3)
    VALUES ('a', 'b', 'c');
    """);

Solution 3

this is my way out of this problem

    for x in y:
        try:
            cursor.execute("INSERT INTO table VALUES (?, ?)", (x["key1"], x["key2"]))
        except sqlite3.Error as error:
            print("Next, ", error)

I don't know if it is a good way to solve this, but it worked for me. It will throw an error every time the primary key is not unique

Comments

[asongtoruin]

This appeared as I was constructing my answer and looks much more comprehensive.

[Barmar]

Where is the INSERT statement?

[Admin]

the problem is that maybe in Choregraphe it's a little bit different.. but I will check how much I can adapt this code, thank you so much