python - 使用带有 POSTGRESQL 的 psycopg2 在 Python 中为 SELECT 查询的可选参数更正函数定义

Question

我想在一个函数中使用可选的过滤器参数从我的 PostgreSQL 数据库中进行 SELECT，但我很难获得正确的语法。

def search(title: str = '', author: str = "", year: int, isbn: int):
    conn = psycopg2.connect(
        " dbname='mydb' user='postgres' password='pwd' host='localhost' port='5432' ")
    curs = conn.cursor()
    curs.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",
                 (title, author, year, isbn))
    rows = curs.fetchall()
    conn.close()
    return rows

然后我想通过只将一个参数(作者姓名)传递给函数来进行搜索，并让 SELECT 语句能够忽略/跳过未传递的字段。当我执行

print(search(author="John"))

输出是

Traceback (most recent call last):
  File "backend.py", line 48, in <module>
    print(search(author="John"))
  File "backend.py", line 39, in search
    (title, author, year, isbn))
psycopg2.errors.UndefinedFunction: operator does not exist: text =?
LINE 1: SELECT * FROM book WHERE title=? OR author=? OR year=? OR is...
                                      ^
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.

我知道这与 SELECT 语句的数据类型和数据库列类型之间的不匹配有关，并且该错误反射(reflect)了没有用于比较这两种数据类型的 = 运算符。但是我该如何定义我的函数参数和 SELECT 语句来让我的查询工作呢？

Answer 1

psycopg2 使用 %s 作为字符串替换的占位符，而不是 ?。尝试改变 ?到 %s，它应该可以工作。

编辑:@Parfait 提出了一个关于你的 where 逻辑的好点。改变？ to %s 将修复您的语法错误，但仍然无法修复逻辑。处理此问题的一种常见方法是将所有参数默认为 None 并将查询更改为如下所示:

curs.execute("""
    SELECT * FROM BOOK
    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})

这不是最高效的方法，但它可能是最简单的方法。