尝试使用SQLAlchemy将字符串插入PostgresQL BYTEA列时出现UTF-8错误

Question

我在PostgresQL表中有一个BYTEA类型的列。模型类将该列定义为LargeBinary字段，文档称“当创建表时，二进制类型生成BLOB或BYTEA，并且还使用每个DB-API提供的可调用的二进制函数来转换传入的值。”

我有一个Python字符串，想将其插入到这个表中。

Python字符串如下：

'\x83\x8a\x13,\x96G\xfd9ae\xc2\xaa\xc3syn\xd1\x94b\x1cq\xfa\xeby$\xf8\xfe\xfe\xc5\xb1\xf5\xb5Q\xaf\xc3i\xe3\xe4\x02+\x00ke\xf5\x9c\xcbA8\x8c\x89\x13\x00\x07T\xeb3\xbcp\x1b\xff\xd0\x00I\xb9'

我这段SQLAlchemy代码的相关片段如下：

    migrate_engine.execute(
        """
        UPDATE table
        SET x=%(x)s
        WHERE id=%(id)s
        """,
        x=the_string_above,
        id='1')

我遇到了错误：

。

sqlalchemy.exc.DataError: (DataError) invalid byte sequence for encoding "UTF8": 0x83
'\n            UPDATE table\n            SET x=%(x)s\n            WHERE id=%(id)s\n            ' {'x': '\x83\x8a\x13,\x96G\xfd9ae\xc2\xaa\xc3syn\xd1\x94b\x1cq\xfa\xeby$\xf8\xfe\xfe\xc5\xb1\xf5\xb5Q\xaf\xc3i\xe3\xe4\x02+\x00ke\xf5\x9c\xcbA8\x8c\x89\x13\x00\x07T\xeb3\xbcp\x1b\xff\xd0\x00I\xb9', 'id': '1',}

如果我进入pgadmin3控制台并直接输入UPDATE命令，则更新正常工作。错误明显来自SQLAlchemy。该字符串是有效的Python2字符串。列具有BYTEA类型。在没有SQLAlchemy的情况下，查询可以正常工作。有人能看出为什么Python认为这个字节字符串是UTF-8吗？

- Ray Toal

---

不知道，如果将它变成原始字符串会发生什么？就像r"\x83\x8a..."这样。 - reptilicus

那么斜杠将会在字符串中。我遇到了与单个字符字符串相同的问题，例如'\xa9'（版权符号）。某个地方正在尝试将其解码为UTF-8。堆栈跟踪中的最后一行显示“sqlalchemy/engine/default.py”，第331行，在do_execute中。 - Ray Toal

是占位符替换在起作用吗？ """ 字符串可能是 UTF-8，然后您尝试使用简单的字符串操作嵌入非 UTF-8 数据。您可能需要手动处理二进制数据的 ASCII 化以此方式进行操作（或者也许有一个 %-code 可以为您完成，我不是 Python 专家，抱歉）。PostgreSQL 支持几种不同的编码方案来嵌入字符串中的二进制数据，可以尝试其中一种。 - mu is too short

可能是客户端编码设置的问题吗？我只是随口说一下... - reptilicus

@muistooshort 可能是这样。对于普通字符串插值，情况并非如此，例如 len("""abc%sdef""" % '\xa9') 会产生预期的结果7，没有错误。但是 execute 方法可能会执行自己的插值方式。目前，额外的 base64 编码是一个足够的解决方法，但它会减慢速度 :( - Ray Toal

你能检查一下查询日志，看看更新操作是否只是得到了一个单独的大字符串，而不是带有“真正”占位符和绑定参数的内容吗？我相当确定你需要对那个 bytea 值进行 十六进制编码或转义编码 才能使其有效，我认为原始的 \x00 空字节在 SQL 中是无效的。是否有一种针对二进制数据的 %-代码可以将其进行十六进制编码呢？ - mu is too short

Answer 1

尝试将数据包装在 buffer 中：

migrate_engine.execute(
    """
    UPDATE table
    SET x=%(x)s
    WHERE id=%(id)s
    """,
    x=buffer(the_string_above),
    id='1')

- Adam Flanagan