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.

Answer 1

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()

Answer 2

'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...

Answer 3

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.