我的Postgresql搜索在后端有效，但在前端无效

Question

所以我有这个后端代码来搜索书籍

def connect():
    conn = psycopg2.connect(
        "dbname='books' user='user' password='password' host='localhost' port='5432'"
    )
    cur = conn.cursor()
    cur.execute(
        "CREATE TABLE IF NOT EXISTS book (id SERIAL PRIMARY KEY, title text, author text, year integer, isbn integer)"
    )
    conn.commit()
    conn.close()

def search(title=None, author=None, year=None, isbn=None):
    conn = psycopg2.connect(
        "dbname='books' user='user' password='password' host='localhost' port='5432'"
    )
    cur = conn.cursor()
    cur.execute(
        "SELECT * FROM book WHERE title = %s OR author = %s OR year = %s OR isbn = %s",
        (title, author, year, isbn),
    )
    enter code here
    rows = cur.fetchall()
    conn.close()
    return rows

当我在后端运行函数时，如下所示

insert("Green Eggs and Ham", "Dr.Seuss", 2003, 11233440)

它工作得很好。但是，当我在tkinter GUI上运行这段代码时

from tkinter import *
import back

def searchCommand():
    LB.delete(0, END)
    for row in back.search(
        Title_text.get(), Author_text.get(), Year_text.get(), ISBN_text.get()
    ):
        LB.insert(END, row)
window = Tk()

TitleL = Label(window, text="Title")
TitleL.grid(row=0, column=0)

AuthorL = Label(window, text="Author")
AuthorL.grid(row=0, column=2)

YearL = Label(window, text="Year")
YearL.grid(row=1, column=0)

ISBNL = Label(window, text="ISBN")
ISBNL.grid(row=1, column=2)

Title_text = StringVar()
Title_Entry = Entry(window, textvariable=Title_text)
Title_Entry.grid(row=0, column=1)

Author_text = StringVar()
Author_Entry = Entry(window, textvariable=Author_text)
Author_Entry.grid(row=0, column=3)

Year_text = StringVar()
Year_Entry = Entry(window, textvariable=Year_text)
Year_Entry.grid(row=1, column=1)

ISBN_text = StringVar()
ISBN_Entry = Entry(window, textvariable=ISBN_text)
ISBN_Entry.grid(row=1, column=3)

LB = Listbox(window, height=6, width=35)
LB.grid(row=2, column=0, rowspan=6, columnspan=2)

sb1 = Scrollbar(window)
sb1.grid(row=2, column=2, rowspan=6)
LB.configure(yscrollcommand=sb1.set)
sb1.configure(command=LB.yview)

b2 = Button(window, text="Search Entry", width=12, command=searchCommand)
b2.grid(row=3, column=3)

window.mainloop()

我得到了这个错误

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\latin\AppData\Local\Programs\Python\Python39\lib\tkinter\__init__.py", line 1892, in __call__
    return self.func(*args)
  File "C:\Users\latin\Desktop\Python\Personal-Projects\Bookstore\front.py", line 13, in searchCommand
    for row in back.search(
  File "C:\Users\latin\Desktop\Python\Personal-Projects\Bookstore\back.py", line 50, in search
    cur.execute(
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: ""
LINE 1: ...ok WHERE title = 'updat' OR author = '' OR year = '' OR isbn...

我不明白为什么如果我在后端手动执行搜索命令，它会正常工作，但是如果我使用tkinter GUI并使用text变量，我会得到这个错误

Answer 1

由于并非所有字段都将用于搜索，因此您需要动态构建SQL语句：

def search(title=None, author=None, year=None, isbn=None):
    conditions = []
    args = []

    if title:
        conditions.append("title = %s")
        args.append(title)
    if author:
        conditions.append("author = %s")
        args.append(author)
    if year:
        conditions.append("year = %s")
        args.append(year)
    if isbn:
        conditions.append("isbn = %s")
        args.append(isbn)

    if conditions:
        sql = f"SELECT * FROM book WHERE {' OR '.join(conditions)}"
        conn = psycopg2.connect(
            "dbname='books' user='user' password='password' host='localhost' port='5432'"
        )
        cur = conn.cursor()
        cur.execute(sql, args)
        rows = cur.fetchall()
        # do whatever you want on the result
        conn.close()

Answer 2

它说整数："“被赋值为nothing！。

请检查传递的integer参数是否为空，然后将其设置为NULL或设置为0或执行某些操作。

请仔细阅读错误消息。并且请对传入的sql参数执行printOUT。

​

​