psycopg2“TypeError:不是所有在字符串格式化期间转换的参数”

Question

我试图将二进制数据（惠而浦散列）插入到PG表中，得到一个错误：

TypeError: not all arguments converted during string formatting 

代码：

cur.execute("""
    INSERT INTO
        sessions
        (identity_hash, posted_on)
    VALUES
        (%s, NOW())
""", identity_hash) 

我尝试在插入之前将conn.Binary（“identity_hash”）添加到变量中，但得到了相同的错误。

identity_hash列是一个bytea。

有什么想法吗？

Answer 1

遇到同样的问题，发现这实际上包含在他们的FAQ

I try to execute a query but it fails with the error not all arguments converted during string formatting (or object does not support indexing). Why? Psycopg always require positional arguments to be passed as a sequence, even when the query takes a single parameter. And remember that to make a single item tuple in Python you need a comma! See Passing parameters to SQL queries.

cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

Answer 2

问题是，您将对象作为第二个参数传递：第二个参数应该是元组或dict。没有%string运算符中的快捷方式。

你应该：

cur.execute("""
    INSERT INTO
        sessions
        (identity_hash, posted_on)
    VALUES
        (%s, NOW())
""", (identity_hash,))

Answer 3

你看过psycopg2源发行版中的“examples/binary.py”脚本吗？在这里工作很好。它看起来有点不同于你的摘录：

data1 = {'id':1, 'name':'somehackers.jpg',
     'img':psycopg2.Binary(open('somehackers.jpg').read())}

curs.execute("""INSERT INTO test_binary
              VALUES (%(id)s, %(name)s, %(img)s)""", data1)