CODEWITHSUNDEEP
pythonsqlstringpostgresqlwhere-clause
rshar
rshar

Reputation: 1477

Fetching rows if two columns have identical string or column is empty from postgres table in python

I have following table in Postgres11.0

col1  col2       col3          col4
1     drug       Galactose     Galactose
2     biological Galactose_1   Galactose
3     drug       Galactose     Galactose

I am working on a python script which can compare two column values per row (col3 and col4), if they are equal and also fetching rows when col4 is empty. I am able to connect to the remote host and the database table and can fetch the rows as well. But the comparison operator giving syntax error. I am new to python so I am not able to find the error in my code.

q = "select * from %s.table;" % (staging_schema)
cur.execute(q)
print("Selecting rows from test table using cursor.fetchall")
row = cur.fetchall()
countdown = len(row)

for drug in row:
    if (drug[2] == drug[3]) or (drug[3] == '')
        print(countdown, drug[0], drug[1], drug[2], drug[3])
        countdown -= 1
    else
        print('different string')

PS: I also tried drug[3].isnull().

Upvotes: 0

Views: 27

Answers (1)

GMB
GMB

Reputation: 222582

It is simpler and more efficient to let the database do the work, rather than fetching the whole table and then iterate and filter in the application code.

You seem to want:

select *
from mytable
where col3 = col4 or col4 is null

This assumes that you mean null when you say empty. If you mean empty string, then:

where col3 = col4 or col4 = ''

Or just handle both:

where col3 = col4 or col4 is null or col4 = ''

Upvotes: 1

Related Questions