How to delete and return values from cursor in python
<p>PostgreSQL allows you to delete rows and return values of columns of the deleted rows using this syntax:</p>

<pre><code>DELETE FROM [table_name] RETURNING [column_names]
</code></pre>

<p>I would like to use this syntax in Python with using <a href="https://github.com/psycopg/psycopg2" rel="nofollow">psycopg2</a> library. So I write:</p>

<pre><code>cur = con.cursor()

cur.execute('DELETE FROM [my_table_name] RETURNING [column_name, let's say id]')
</code></pre>

<p>Now I don't know what to write afterwards. Should it be:</p>

<pre><code>con.commit()
</code></pre>

<p>But this alone does not return any values from cursor. Or should it be:</p>

<pre><code>rows = cur.fetchall()
</code></pre>

<p>, and then iterating through the list of rows?</p>

<p>But will this method actually delete the rows? It seems like commit is missing.</p>

<p>Or may be I should write both, like so:</p>

<pre><code>con.commit()

rows = cur.fetchall()
</code></pre>

<p>But I'm not sure if this is correct. Trying it out could be an option, but if some of you guys know the answer, that would be safer to me. </p>

<p>You query runs inside of transaction (but it's better to double check that <code>autocommit</code> is set to False), that means that all the changes you make are visible only for you (actually, it depends on other transactions settings) until you commit these changes.</p>

<p>Both options work in that case:</p>

<pre><code>&gt;&gt;&gt; con = db()
&gt;&gt;&gt; cur = con.cursor()
&gt;&gt;&gt; cur.execute("DELETE FROM tmp_delete_test WHERE id &lt; 3 RETURNING id;")
&gt;&gt;&gt; result = cur.fetchall()
&gt;&gt;&gt; result
[(1,), (2,)]
&gt;&gt;&gt; con.commit()
&gt;&gt;&gt; cur.execute("DELETE FROM tmp_delete_test WHERE id &lt; 3 RETURNING id;")
&gt;&gt;&gt; result = cur.fetchall()
&gt;&gt;&gt; result
[]
</code></pre>

<p>And commit, fetchall:</p>

<pre><code>&gt;&gt;&gt; cur.execute("DELETE FROM tmp_delete_test WHERE id &lt; 4 RETURNING id;")
&gt;&gt;&gt; con.commit()
&gt;&gt;&gt; result = cur.fetchall()
&gt;&gt;&gt; result
[(3,)]
</code></pre>

<p>So, I'd suggest you to use the second option if you need no be sure that records actually have been deleted, but if you need to do something with that IDs within the same transaction, you need to use the first option.</p>

python postgresql cursor psycopg2 delete-operator