psycopg2“TypeError：在字符串格式化过程中并非转换所有参数”

I'm trying to insert binary data (a whirlpool hash) into a PG table and am getting an error:

我正在尝试将二进制数据(漩涡哈希)插入PG表中并收到错误:

TypeError: not all arguments converted during string formatting

code:

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

I tried adding conn.Binary("identity_hash") to the variable before insertion, but get the same error.

我尝试在插入之前将conn.Binary(“identity_hash”)添加到变量,但得到相同的错误。

The identity_hash column is a bytea.

identity_hash列是bytea。

Any ideas?

3 个解决方案

#1

Have you taken a look at the "examples/binary.py" script in the psycopg2 source distribution? It works fine here. It looks a bit different than your excerpt:

您是否看过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)

#2

The problem you have is that you are passing the object as second parameter: the second parameters should be either a tuple or a dict. There is no shortcut as in the % string operator.

您遇到的问题是您将对象作为第二个参数传递:第二个参数应该是元组或字典。没有%字符串运算符中的快捷方式。

You should do:

你应该做:

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

#3

Encountered the same problem and found that this is actually covered in their FAQ

遇到同样的问题,发现这实际上已经包含在他们的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.

我尝试执行一个查询,但它失败了,错误并非在字符串格式化期间转换所有参数(或对象不支持索引)。为什么? Psycopg总是要求将位置参数作为序列传递,即使查询采用单个参数也是如此。请记住,要在Python中创建单个项目元组,您需要一个逗号!请参阅将参数传递给SQL查询。

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

#1