时间戳

首页 > 解决方案 > 将postgresql与python一起使用时，如何将我作为参数放入搜索函数的数据值的所有行值返回

python - 将postgresql与python一起使用时，如何将我作为参数放入搜索函数的数据值的所有行值返回

问题描述

        import psycopg2
        
        def connect_table():
            connect= psycopg2.connect("dbname= 'bookshop' user='postgres' password='0973' host='localhost' port='5432'")
            cursor= connect.cursor()    
            cursor.execute("CREATE TABLE IF NOT EXISTS books (ID SERIAL PRIMARY KEY, Title TEXT, Author TEXT, Year INTEGER, ISBN INTEGER UNIQUE)")
            connect.commit()     
            connect.close()
    
    def insert(Title,Author,Year,ISBN):
        connect= psycopg2.connect("dbname= 'bookshop' user='postgres' password='0973' host='localhost' port='5432'")
        cursor= connect.cursor() 
        cursor.execute("INSERT INTO books (Title, Author, Year, ISBN) VALUES (%s,%s,%s,%s)", (Title,Author,Year,ISBN))
        connect.commit()     
        connect.close()
    
    
    def view():
        connect= psycopg2.connect("dbname= 'bookshop' user='postgres' password='0973' host='localhost' port='5432'")
        cursor= connect.cursor()
        cursor.execute("SELECT * from books")
        rows= cursor.fetchall()      #Fetches all rows of a query result, returns it as a list of tuples
        connect.close()
        return rows
    
    def search(Title='',Author='',Year='',ISBN=''):
        connect= psycopg2.connect("dbname= 'bookshop' user='postgres' password='0973' host='localhost' port='5432'")
        cursor= connect.cursor()
        cursor.execute("SELECT * from books WHERE Title=%s OR Author=%s OR Year=%s OR ISBN=%s", (Title, Author, Year, ISBN))
        rows= cursor.fetchall()      #Fetches all rows of a query result, returns it as a list of tuples
        connect.close()
        return rows
    
print(search(Author= 'Tsu Chi'))

上面是我的代码，下面是我以这种方式调用搜索函数时遇到的错误print(search(Author= 'Tsu Chi')) 。我希望能够搜索单个数据值，它将返回与其关联的行...

Traceback (most recent call last):
  File "c:/Users/daniel/Desktop/backend.py", line 77, in <module>
    print(search(Author= 'Tsu Chi'))
  File "c:/Users/daniel/Desktop/backend.py", line 36, in search
    cursor.execute("SELECT * from books WHERE Title=%s OR Author=%s OR Year=%s OR ISBN=%s", (Title, Author, Year, ISBN))
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: ""
LINE 1: ... books WHERE Title='' OR Author='Tsu Chi' OR Year='' OR ISBN...

标签： pythonpostgresql

解决方案

似乎至少有一个字段（标题、作者、年份或 isbn）是数据类型int。您为函数参数定义的默认值导致 psycopg2 将字符串值传递给所有绑定变量。

附带说明一下，通常最好保持一个连接打开并将其重用于所有函数调用。

推荐阅读