PostgreSQL SELECT when integer unique - invalid input syntax

Question

  cur.execute("""SELECT  name, number 
    FROM store
    WHERE number=%s OR name =%s""",
    (number, name))

My problem is that number is INTEGER UNIQUE and when I try to select by number everything is ok, but if I try to select by name I received an error:

invalid input syntax for integer

You should be using parameters, rather than munging the string! — Gordon Linoff
– Gordon Linoff, Commented Mar 20, 2019 at 18:37
Also note that the invalid input syntax for integer error may occur if an empty string is passed as an argument instead of an integer. If that is the problem, int(number) will raise a ValueError on the Python side. — unutbu
– unutbu, Commented Mar 20, 2019 at 18:43
@glaadys: We want to help you but "it doesn't work" is never informative enough. Instead, please tell us what error message you see. — unutbu
– unutbu, Commented Mar 20, 2019 at 18:45

unutbu · Accepted Answer · 2019-03-20 19:08:59Z

0

It sounds like number is sometimes not an integer, such as an empty string. You could handle this on the Python side by assigning number to None whenever int(number) is invalid:

try:
    number = int(number)
except ValueError:
    number = None

cur.execute("""SELECT  name, number 
    FROM store
    WHERE number=%s OR name =%s""",
    (number, name))

cur.execute will handle None as NULL.

in GUI tkinter I would like to have an option to search my database only by providing the name OR number, not providing both

If you want to search by number (first) or by name (second), but never both, then use

import psycopg2
try:
    cur.execute("""SELECT  name, number 
        FROM store
        WHERE number=%s""", (number, ))

except psycopg2.DataError:
    cur.execute("""SELECT  name, number 
        FROM store
        WHERE name=%s""", (name, ))

edited Mar 20, 2019 at 19:08

answered Mar 20, 2019 at 18:57

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL SELECT when integer unique - invalid input syntax

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related