Python PostgreSQL drop duplicate rows

Question

I have python 3.7.3, psycopg2 version '2.7.6.1 (dt dec pq3 ext lo64)', and PostGreSQL version 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) installed.

The below query works from the terminal and does indeed delete all duplicate rows by column: id in table: mydbschema.table1.

DELETE  FROM  mydbschema.table1  a  USING( SELECT MIN(ctid) as ctid, id FROM mydbschema.table1 GROUP BY id HAVING COUNT(*) > 1) b WHERE a.id = b.id AND a.ctid <> b.ctid;

However, when I want to run it through a python connection and cursor, the deletion does not happen.

import psycopg2
connection = psycopg2.connect(user=conn_details['user'],
                              password=conn_details['password'],
                              host=conn_details['host'],
                              port=conn_details['port'],
                              database=conn_details['database'])
 cursor = connection.cursor()
 cursor.execute("DELETE  FROM  mydbschema.table1  a  USING( SELECT MIN(ctid) as ctid, id FROM mydbschema.table1 GROUP BY id HAVING COUNT(*) > 1) b WHERE a.id = b.id AND a.ctid <> b.ctid;")
 cursor.close()

I know the problem is not due to the connection, as the below subsection of the query works completely fine:

cursor.execute("SELECT MIN(ctid) as ctid, id FROM mydbschema.table1 GROUP BY id HAVING COUNT(*) > 1;")
cursor.fetchall()
Out[3]: [('(0,3)', 3), ('(0,4)', 4), ('(0,2)', 2)]

Am I missing something? Is cursor.execute() not suitable for this type of deletion command?

are you forgetting to `commit` your query? your cursor might not be in auto-commit mode — gold_cy, Sep 22 '19 at 12:49
@aws_apprentice The subsection of the query (at the bottom of the question) returns results, would I additionally need to manually do a `commit` step for the full deletion query above? — Zhubarb, Sep 22 '19 at 12:51
I would `commit` your query before closing the first connection — gold_cy, Sep 22 '19 at 12:52
Indeed, it worked!. I did not realise for database changes to persist you needde to commit. Can you write a short answer and I will accept. — Zhubarb, Sep 22 '19 at 12:54

score 2 · Accepted Answer · answered Sep 22 '19 at 12:56

You need to commit your DELETE query in your first connection prior to running a subsequent query in a new connection to see the results reflected. You can see here that the cursor is not opened in auto-commit mode so a manual commit is required. Conversely you can open the cursor in auto-commit as noted in the docs.

score 1 · Answer 2 · answered Sep 22 '19 at 12:57

You must use commit() method to persist the delete command:

import psycopg2
connection =psycopg2.connect(user=conn_details['user'],
                          password=conn_details['password'],
                          host=conn_details['host'],
                          port=conn_details['port'],
                          database=conn_details['database'])
cursor = connection.cursor()
cursor.execute("DELETE  FROM  mydbschema.table1  a  
USING( SELECT MIN(ctid) as ctid, id FROM 
mydbschema.table1 GROUP BY id HAVING COUNT(*) > 1) b WHERE a.id = b.id AND a.ctid <> b.ctid;")
connection.commit()
cursor.close()

Python PostgreSQL drop duplicate rows

2 Answers2