sqlite3 INSERT IF NOT EXIST (with Python)
<p>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.</p>

<p>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: [<a href="http://stackoverflow.com/questions/19337029/insert-if-not-exists-statement-in-sqlite]">&quot;Insert if not exists&quot; statement in SQLite</a>) 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)</p>

<p>So, here is what I did:</p>

<pre><code>    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'))")
</code></pre>

<p>and here's the error:</p>

<pre><code>[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 "&lt;string&gt;", line 45, in onInput_onStart OperationalError: near "WHERE": syntax error  
</code></pre>

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

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

<p>Thank you so much</p>

<hr>

<p>E.G.: I forgot to say that I am using the <strong>software Choregraphe</strong>, 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.</p>

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

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

<p>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 <code>WHERE</code> expression (which is either all or none, because the <code>WHERE</code> expression doesn't refer to anything in the row being selected). You just want to select the values by themselves.</p>

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

<p>Replace <code>col1</code>, <code>col2</code>, <code>col3</code> with the actual names of the columns you're filling in.</p>

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

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

python database sqlite sqlite3 insert