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.

Solution

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