Comparison to update table in PostgreSQL with psycopg2 doesn't work

Question

I want to update a table in PostgreSQL using Python's psycopg2. For every row, I want to compare if steine > 4 and if boden is equal to Ss, Su, Tt or Leer to exclude these from my following calculations by setting weienauschluss = True and thus the column weizen_ok to False.

However, only the comparison of steine is working, while the boden comparison doesn't. Is something wrong with the comparison? I tried setting it up in various ways, but none of them seem to work.

The data type of steine is integer and of boden is string.

Here is my code:

import psycopg2

conn = psycopg2.connect(*****)
cur = conn.cursor()

sql = "select min(id), max(id) from bsd_horizonte_test;"
cur.execute(sql)
gidsextent = cur.fetchall()
minimum = gidsextent[0][0]
maximum = gidsextent[0][1]


for gid in range(minimum,maximum+1):
    weizenausschluss = False
    sql = "select grobbod_k, boart from bsd_horizonte_test where id = " + str(gid) + ";"
    cur.execute(sql)
    data = cur.fetchall()
    if len(data) > 0:
        steine = data[0][0]
        boden = data[0][1]
        if steine > 4:
            weizenausschluss = True
        if boden == "Ss" or boden == "Su" or boden == "Tt" or boden == "Leer":
            weizenausschluss = True
        if weizenausschluss == False:
            sql = "update bsd_horizonte_test set weizen_ok = True where id = " + str(gid) + ";"
            cur.execute(sql)
            conn.commit()
        else:
            sql = "update bsd_horizonte_test set weizen_ok = False where id = " + str(gid) + ";"
            cur.execute(sql)
            conn.commit()
    print(gid)
        
#Close communication with the database
conn.commit()
cur.close()
conn.close()

Thanks!

Stefanov.sm · Accepted Answer · 2020-11-08 19:26:21Z

1

Why not let Postgresql do the job in a single query? Just cur.execute this query and it will do it all.

update bsd_horizonte_test 
set weizen_ok = not (steine > 4 or boden in ('Su', 'Tt', 'Leer'))
-- where id between (select min(id) from bsd_horizonte_test) and (select max(id) from bsd_horizonte_test)
;

And btw why loop all the values b/w min(id) and max(id)? These are ALL the possible values of id so you can safely comment the third query line. I have only written it in order to be literally equivalent to your logic.

edited Nov 8, 2020 at 19:26

answered Nov 8, 2020 at 19:16

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparison to update table in PostgreSQL with psycopg2 doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related