python: psycopg2: psql: Delete row where condition

Question

I'm trying to delete rows from a psql table on a condition.

I want all rows to be deleted if column "TagNaam" equals a variable var_tagnaam.

I've tried the following code and some variants but I can't get it to work. There aren't any errors though.

cur.execute("DELETE FROM opc_actuelewaardentags WHERE 'TagNaam' = %s", (var_tagnaam,))

Is there something wrong with the syntax?

Edit: Maybe it is more clear with additional code, the error might be in the other code?

for i in range(len(taginhoud)):
    (var_tagnaam, var_tagwaarde, var_tagkwaliteit, var_tagtime) = taginhoud[i]
    print (var_tagnaam)

    cur.execute("DELETE FROM opc_actuelewaardentags WHERE 'TagNaam' = %s", (var_tagnaam,))
    conn.commit()

    cur.execute('INSERT INTO opc_actuelewaardentags ("TagNaam", "TagWaarde", "TagKwaliteit", create_date, write_date) VALUES (%s,%s,%s,now(),now())',
                (var_tagnaam, var_tagwaarde, var_tagkwaliteit))   
    conn.commit()

So what I try to do here is:

Retrieve "var_tagnaam" from list "taginhoud".

Then in table opc_actuelewaardentags find all rows where column "Tagnaam" equals the value in "var_tagnaam". (Should be a string)

Then delete those rows where "Tagnaam" = "var_tagnaam". This part doesn't work.

Then insert new rows with data. This part works.

Could this code be wrong to do what I want?

I have tried many things already to solve the upper/lower case problem.

Edit 2:Query in pgadmin worked, trying to do the same thing in python:

I ran this query in pgadmin and it deleted the rows:

delete FROM opc_actuelewaardentags where "TagNaam" = 'Bakkerij.Device1.DB100INT8';

My attempt to make it as similar as possible in python:

var_tagnaam2 = "'"+var_tagnaam+"'"
cur.execute("DELETE FROM opc_actuelewaardentags WHERE \"TagNaam\" = %s", (var_tagnaam2,))
conn.commit()

Tried to escape the double quotes in attempt to make it the same as in pgadmin.

Have you tried commiting changes? Should be something like `cur.commit()` — konart, Jul 22 '15 at 09:03
It seems you need to `commit()` the connection, not the cursor. If this is not working anyway - I suggest reading the psql log. Maybe you have to tell the db what schema to use. — konart, Jul 22 '15 at 09:18
@RobbeM, i am facing the same issue. None of the below solutions work. How did you fix this issue, can you please share ? — Krishna Kumar, Apr 05 '21 at 11:30

Mp0int · Answer 1 · 2015-07-22T11:28:36.553

1

'TagNaam' is not a valid column_name identifier in sql language. You must not use single ~~or double~~ quotes in writing database name, table name or colunm name, but you can use apostrophe (`) .

Invalid:

DELETE FROM opc_actuelewaardentags WHERE 'TagNaam' = 'test';

~~DELETE FROM opc_actuelewaardentags WHERE "TagNaam" = 'test';~~

Valid:

DELETE FROM opc_actuelewaardentags WHERE TagNaam = 'test';
DELETE FROM opc_actuelewaardentags WHERE `TagNaam` = 'test';
DELETE FROM opc_actuelewaardentags WHERE "TagNaam" = 'test';

Update: According to PSQL dosc, double quote is a valid character in table and column names. It is especially used for key words while usinga as a table or column name. So following is valid:

DELETE FROM opc_actuelewaardentags WHERE "TagNaam" = 'test';

More is here...

edited Jul 22 '15 at 11:28

answered Jul 22 '15 at 09:59

Mp0int

18,172
15
83
114

I tried this: cur.execute("DELETE FROM opc_actuelewaardentags WHERE `TagNaam` = 'test'") But gives error: psycopg2.ProgrammingError: column "tagnaam" does not exist LINE 1: DELETE FROM opc_actuelewaardentags WHERE `TagNaam` = 'test' – RobbeM Jul 22 '15 at 11:10
You should put your table name in double quotes, because psql converts it to lowercase by default and your column name has uppercase letters. You whole query should be in triple or single quotes for this. – konart Jul 22 '15 at 12:14

score 0 · Answer 2 · edited May 23 '17 at 11:44

I don't have a psql server, but a mysql server.

For MySQL:

mysql> select * from user where '1' = '1';                    
+------+                                                      
| id   |                                                      
+------+                                                      
|    2 |                                                      
|    1 |                                                      
+------+                                                      
2 rows in set (0.05 sec)                                      

mysql> select * from user;                                    
+------+                                                      
| id   |                                                      
+------+                                                      
|    2 |                                                      
|    1 |                                                      
+------+                                                      
2 rows in set (0.00 sec)                                      

mysql> select * from user where '1' = "1";                    
+------+                                                      
| id   |                                                      
+------+                                                      
|    2 |                                                      
|    1 |                                                      
+------+                                                      
2 rows in set (0.00 sec)                                      

mysql> select * from user where 'id' = "1";                   
Empty set (0.00 sec)        

mysql> select * from user where 'id' = 1;
Empty set, 1 warning (0.02 sec)   

mysql> select * from user where id = 1;

+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.02 sec)


mysql> select * from user where 'id' = "id";                  
+------+                                                      
| id   |                                                      
+------+                                                      
|    2 |                                                      
|    1 |                                                      
+------+                                                      
2 rows in set (0.00 sec)

The SQL grammar should be similar. Therefore,

cur.execute("DELETE FROM opc_actuelewaardentags WHERE 'TagNaam' = %s", (var_tagnaam,))

should be

cur.execute("DELETE FROM opc_actuelewaardentags WHERE TagNaam = %s", (var_tagnaam,))

or

cur.execute("DELETE FROM opc_actuelewaardentags WHERE `TagNaam` = %s", (var_tagnaam,))

Above analyusis is error.

Simple Postgresql Statement - column name does not exists gives the answer.

Thanks for your comment, I think you only removed the quotes around TagNaam? It doesn't work since my column name has upper and lower case letters. That's why I need quotes. It gives error: column "tagnaam" does not exist when I try your code. — RobbeM, Jul 22 '15 at 09:51
[http://stackoverflow.com/questions/5800230/simple-postgresql-statement-column-name-does-not-exists](http://stackoverflow.com/questions/5800230/simple-postgresql-statement-column-name-does-not-exists) should be helpful . — letiantian, Jul 22 '15 at 10:02

MaciejTe · Answer 3 · 2017-06-22T05:26:06.110

RobbeM wrote: Edit 2:Query in pgadmin worked, trying to do the same thing in python

I've had the same symptoms - I could delete table rows using pgadmin or in SQL console, but Python code wouldn't work. The thing was I was accidentally creating cursor before establishing connection with postgreSQL server:

c = db_conn.cursor()
db_conn = psycopg2.connect(conn_string)

So, the solution for me was to create cursor after establishing connection with database:

db_conn = psycopg2.connect(conn_string)
c = db_conn.cursor()

python: psycopg2: psql: Delete row where condition

3 Answers3