Home psycopg2.Data Error: invalid input syntax for integer:" "
Reply: 1

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

kiruthika
1#
kiruthika Published in 2017-12-07 17:29:20Z

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)

Clodoaldo Neto
2#
Clodoaldo Neto Reply to 2017-12-07 17:50:02Z

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}
    )
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298311 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO