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.

You are using entirely different SQL commands; the VALUES and FROM clauses are wrong. — CL.
– CL., Commented Sep 30, 2016 at 14:11
Thank you both of you.. but it would be really helpful an example, if you have time :) — mruocco
– mruocco, Commented Sep 30, 2016 at 14:25
Did you intend the SELECT * and the WHERE NOT ... to be part of the same statement? — John Gordon
– John Gordon, Commented Sep 30, 2016 at 14:27
Is a, b, c the primary key of the table? You could use INSERT OR IGNORE. — Barmar
– Barmar, Commented Sep 30, 2016 at 14:39

Barmar · Accepted Answer · 2016-09-30 14:47:25Z

9

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');
    """);

answered Sep 30, 2016 at 14:47

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

asongtoruin Over a year ago

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

mruocco Over a year ago

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

asongtoruin · Accepted Answer · 2016-09-30 17:46:17Z

7

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!

edited Sep 30, 2016 at 17:46

answered Sep 30, 2016 at 14:49

asongtoruin

10.4k3 gold badges42 silver badges52 bronze badges

1 Comment

Barmar Over a year ago

Where is the INSERT statement?

Lucas Lameira · Accepted Answer · 2021-08-12 22:48:25Z

2

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

answered Aug 12, 2021 at 22:48

Lucas Lameira

512 bronze badges

Collectives™ on Stack Overflow

sqlite3 INSERT IF NOT EXIST (with Python)

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related