Psycopg2 数据错误: 编码为 "UTF8" 的字节序列无效: 0xa0

Question

我可以帮助您翻译以下内容，这是关于编程的：「我已经在这个错误上进行了相当多的谷歌搜索，结果发现我正在使用的数据库以不同的编码方式运行。」 「我正在使用的AIX服务器正在运行...」

psql 8.2.4

 server_encoding                 | LATIN1          |      | Client Connection Defaults / Locale and Formatting                | Sets the server (database) character set encoding.

我正在使用的Windows 2008 R2服务器正在运行：

psql（9.3.4）

CREATE DATABASE postgres
WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'English_Australia.1252'
   LC_CTYPE = 'English_Australia.1252'
   CONNECTION LIMIT = -1;

COMMENT ON DATABASE postgres
IS 'default administrative connection database';

现在，当我尝试执行我的Python脚本时，出现以下错误。

Traceback (most recent call last):
File "datamain.py", line 39, in <module>
sys.exit(main())
File "datamain.py", line 33, in main
write_file_to_table("cms_jobdef.txt", "cms_jobdef", con_S104838)
File "datamain.py", line 21, in write_file_to_table
cur.copy_from(f, table, ",")
psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0xa0
CONTEXT:  COPY cms_jobdef, line 15209    

这是我的脚本

import psycopg2
import StringIO
import sys
import pdb

def connect_db(db, usr, pw, hst, prt):
    conn = psycopg2.connect(database=db, user=usr,
    password=pw, host=hst, port=prt)
    return conn

def write_table_to_file(file, table, connection):
    f = open(file, "w")
    cur = connection.cursor()
    cur.copy_to(f, table, ",")
    f.close()
    cur.close()

def write_file_to_table(file, table, connection):
    f = open(file,"r")
    cur = connection.cursor()
    cur.copy_from(f, table, ",")
    f.close()
    cur.close()

def main():
    login = open('login.txt','r')
    con_tctmsv64 = connect_db("x", "y",
    login.readline().strip(),
    "d.domain", "c")
    con_S104838 = connect_db("x", "y", "z", "a", "b")
    try:
        write_table_to_file("cms_jobdef.txt", "cms_jobdef", con_tctmsv64)
        write_file_to_table("cms_jobdef.txt", "cms_jobdef", con_S104838)
    finally:
        con_tctmsv64.close()
        con_S104838.close()

if __name__ == "__main__":
    sys.exit(main())

我已经删除了一些敏感数据。

所以我不确定该怎么继续。据我所知，copy_expert 方法可以通过导出为 UTF8 编码来帮助解决问题。但因为我从中提取数据的服务器运行的是 8.2.4 版本，我认为它不支持 COPY 编码格式。

我认为最好的方法是尝试在 Windows 服务器上重新安装 postgre 数据库，并使用 LATIN1 编码。但是，当我尝试执行此操作时，我会收到以下错误信息。

我很困惑，非常感谢任何帮助！

更新：我通过将默认本地更改为“C”在Windows上安装了Latin1编码的Postgre数据库。然而，这给了我以下错误，似乎不是一个成功/正确的方法。

我还尝试使用 PSQL 的 COPY 函数以二进制编码文件

def write_table_to_file(file, table, connection):
    f = open(file, "w")
    cur = connection.cursor()
    #cur.copy_to(f, table, ",")
    cur.copy_expert("COPY cms_jobdef TO STDOUT WITH BINARY", f)
    f.close()
    cur.close()

def write_file_to_table(file, table, connection):
    f = open(file,"r")
    cur = connection.cursor()
    #cur.copy_from(f, table)
    cur.copy_expert("COPY cms_jobdef FROM STDOUT WITH BINARY", f)
    f.close()
    cur.close()

仍然没有运气，我得到了相同的错误

DataError: invalid byte sequence for encoding "UTF8": 0xa0
CONTEXT:  COPY cms_jobdef, line 15209, column descript

关于Phil的回答，我尝试了这种方法，但仍然没有成功。

import psycopg2
import StringIO
import sys
import pdb
import codecs

def connect_db(db, usr, pw, hst, prt):
    conn = psycopg2.connect(database=db, user=usr,
    password=pw, host=hst, port=prt)
    return conn

def write_table_to_file(file, table, connection):
    f = open(file, "w")
    #fx = codecs.EncodedFile(f,"LATIN1", "UTF8")
    cur = connection.cursor()
    cur.execute("SHOW client_encoding;")
    print cur.fetchone()
    cur.copy_to(f, table)
    #cur.copy_expert("COPY cms_jobdef TO STDOUT WITH BINARY", f)
    f.close()
    cur.close()

def write_file_to_table(file, table, connection):
    f = open(file,"r")
    cur = connection.cursor() 
    cur.execute("SET CLIENT_ENCODING TO 'LATIN1';")
    cur.execute("SHOW client_encoding;")
    print cur.fetchone()
    cur.copy_from(f, table)
    #cur.copy_expert("COPY cms_jobdef FROM STDOUT WITH BINARY", f)
    f.close()
    cur.close()

def main(): 
    login = open('login.txt','r')
    con_tctmsv64 = connect_db("x", "y",
    login.readline().strip(),
    "ctmtest1.int.corp.sun", "5436")
    con_S104838 = connect_db("x", "y", "z", "t", "5432")
    try:
        write_table_to_file("cms_jobdef.txt", "cms_jobdef", con_tctmsv64)
        write_file_to_table("cms_jobdef.txt", "cms_jobdef", con_S104838)
    finally:
        con_tctmsv64.close()
        con_S104838.close()

if __name__ == "__main__":
    sys.exit(main())

输出

In [4]: %run datamain.py
('sql_ascii',)
('LATIN1',)

In [5]: 

这个操作成功完成，但当我运行时出现了问题。

select * from cms_jobdef;

新数据库中没有任何内容

我甚至尝试将文件格式从LATIN1转换为UTF8。仍然没有运气。

奇怪的是，当我只使用postgre的COPY函数手动执行此过程时，它可以正常工作。我不知道为什么。再次感谢您的帮助。

- Justin S

Answer 1

原来有几种解决这个问题的选项。像Phil建议的更改客户端编码的选项是可行的。

cur.execute("SET CLIENT_ENCODING TO 'LATIN1';")

另一个选项是在传输时转换数据。我使用了一个名为codecs的Python模块来实现这一点。

f = open(file, "w")
fx = codecs.EncodedFile(f,"LATIN1", "UTF8")
cur = connection.cursor()
cur.execute("SHOW client_encoding;")
print cur.fetchone()
cur.copy_to(fx, table)

关键句是：

fx = codecs.EncodedFile(f,"LATIN1", "UTF8")

我的主要问题是我没有将更改提交到数据库！我真是太傻了 :)

- Justin S

Answer 2

我正在从SQL_ASCII数据库迁移到UTF8数据库的过程中，遇到了同样的问题。根据这个答案，我只需要在导入脚本的开头添加以下语句：

set client_encoding to 'latin1'

一切似乎已经成功地导入了。

- Phil Cairns

---

嗨Phil，感谢您的回复。我也尝试过了，但没有成功。代码成功执行，但执行后表仍为空。我已更新原始问题以反映这一点。 - Justin S

我很不想问这个问题...你是否提交了你的事务？http://initd.org/psycopg/docs/connection.html说：“默认情况下，Psycopg在执行第一条命令之前打开一个事务：如果没有调用commit()，任何数据操作的效果都将丢失。” - Phil Cairns

感谢你的反馈Phil，你帮了我大忙！ - Justin S