无法将PostgreSQL文本列转换为bytea [英] Unable to convert PostgreSQL text column to bytea

问题描述

在我的应用程序中，我使用带有 text列的postgresql数据库表来存储
腌制的python对象。
作为数据库驱动程序，我使用的是psycopg2，直到现在，我仅将python字符串（不是unicode-objects）传递给数据库，并从数据库中检索了字符串。直到我最近决定使String-handling更好/更正确的方法并将以下构造添加到我的DB层后，这基本上可以正常工作：

In my application I am using a postgresql database table with a "text" column to store pickled python objects. As database driver I'm using psycopg2 and until now I only passed python-strings (not unicode-objects) to the DB and retrieved strings from the DB. This basically worked fine until I recently decided to make String-handling the better/correct way and added the following construct to my DB-layer:

psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

这基本上可以在我的应用程序中的任何地方正常工作，并且我现在尽可能使用Unicode对象。

This basically works fine everywhere in my application and I'm using unicode-objects where possible now.

但是对于这种特殊情况如果文本列中包含已腌制的对象，则会带来麻烦。我以这种方式使它在测试系统中正常工作：

But for this special case with the text-column containing the pickled objects it makes troubles. I got it working in my test-system this way:

• 检索数据：
  SELECT数据:: bytea，来自mytable的参数


• 写入数据：
  execute（ UPDATE mytable SET data =％s ，（psycopg2.Binary（cPickle.dumps（x）），））

• retrieving the data: SELECT data::bytea, params FROM mytable
• writing the data: execute("UPDATE mytable SET data=%s", (psycopg2.Binary(cPickle.dumps(x)),) )

...但是不幸的是，我在生产系统中的某些列的SELECT上遇到错误：

... but unfortunately I'm getting errors with the SELECT for some columns in the production-system:

psycopg2.DataError: invalid input syntax for type bytea

当我尝试在psql shell中运行查询时，也会发生此错误。

This error also happens when I try to run the query in the psql shell.

基本上，我打算将列从文本转换为 bytea，但是上面的错误
也阻止了我执行此转换。

Basically I'm planning to convert the column from "text" to "bytea", but the error above also prevents me from doing this conversion.

据我所知，（当将列检索为纯python字符串时）字符串中只有ord（c）<= 127的字符。

As far as I can see, (when retrieving the column as pure python string) there are only characters with ord(c)<=127 in the string.

推荐答案

问题是将文本强制转换为 bytea 并不意味着将字符串中的字节打包为 bytea 值，而是使用字符串，并将其解释为 bytea 类型的转义输入值。这样就行不通了，主要是因为泡菜数据中包含许多反斜杠，而 bytea 是专门反斜杠的。

The problem is that casting text to bytea doesn't mean, take the bytes in the string and assemble them as a bytea value, but instead take the string and interpret it as an escaped input value to the bytea type. So that won't work, mainly because pickle data contains lots of backslashes, which bytea interprets specially.

尝试而是这样：

SELECT convert_to(data, 'LATIN1') ...

这会将字符串转换为采用LATIN1编码的字节序列（ bytea 值）。对您来说，确切的编码无关紧要，因为它全都是ASCII（但是没有 ASCII 编码）。

This converts the string into a byte sequence (bytea value) in the LATIN1 encoding. For you, the exact encoding doesn't matter, because it's all ASCII (but there is no ASCII encoding).

这篇关于无法将PostgreSQL文本列转换为bytea的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！