dimanche 8 novembre 2020

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

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!

Aucun commentaire:

Enregistrer un commentaire