psycopg2.Data Error: invalid input syntax for integer:" "

Question

Hi..i tried to search for an entry in my table that holds both text and integer values..my code works well on sqlite3 database...but throws Data Error on Postgresql database..

import psycopg2
class database:

    def __init__(self):

        self.con=psycopg2.connect("dbname='book_store' user='postgres' password='5283' host='localhost' port='5432' ")
        self.cur=self.con.cursor()
        self.cur.execute("CREATE TABLE if not exists books(id SERIAL PRIMARY KEY,title TEXT NOT NULL UNIQUE,author TEXT NOT NULL,year integer NOT NULL,isbn integer NOT NULL UNIQUE)")
        self.con.commit()

    def insert(self,title,author,year,isbn):
      try:
        self.cur.execute("INSERT INTO books(title,author,year,isbn) VALUES(%s,%s,%s,%s)",(title,author,year,isbn))
        self.con.commit()
      except:
          #print("already exists..")
          pass

    def view(self):
        self.cur.execute("SELECT * FROM books")
        rows=self.cur.fetchall()
        print(rows)

    def search(self,title="",author="",year="",isbn=""):
        self.cur.execute("SELECT * FROM books WHERE title=%s or author=%s or year=%s or isbn=%s",(title,author,year,isbn))
        row=self.cur.ferchall()
        print(row)

db=database()
#db.insert("The Naughty","AparnaKumar",1995,234567654)
db.view()
db.search(year=1995)

ISBNs are either 10 or 13 characters in length, which the 10-digit version having the potential to start with a zero. Not sure using integer for the data type in your table is a good idea. — Nick
– Nick, Commented Dec 7, 2017 at 17:51

Clodoaldo Neto · Accepted Answer · 2017-12-07 17:50:02Z

1

This is what you want:

def search(self, title=None, author=None, year=None, isbn=None):
    self.cursor.execute("""
        select * 
        from books 
        where 
            (title = %(title)s or %(title)s is null)
            and
            (author = %(author)s or %(author)s is null)
            and
            (year = %(year)s or %(year)s is null)
            and
            (isbn = %(isbn)s or %(isbn)s is null)
        """,
        {'title': title, 'author': author, 'year': year, 'isbn': isbn}
    )

answered Dec 7, 2017 at 17:50

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Nick Over a year ago

That would likely solve the issue, but maybe explain why? Otherwise this is a code-only answer.

Collectives™ on Stack Overflow

psycopg2.Data Error: invalid input syntax for integer:" "

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related