psycopg2.DataError: 无效的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。当我尝试这样做时，出现了下面的错误。

psql error

所以我现在很困惑，任何帮助都将非常感谢！

更新：我在Windows上安装了LATIN1编码的Postgre数据库，通过将默认本地设置为'C'。不过，这样做给我带来了下面的错误，看起来不是一个成功或正确的方法。

enter image description here

我还尝试使用PSQL的COPY功能将文件编码为BINARY。

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;

新数据库里什么都没有

enter image description here

我甚至尝试将文件格式从LATIN1转换为UTF8，但仍然没有成功。

奇怪的是，当我手动使用Postgre的COPY功能时，这个过程是有效的。我不知道为什么。再次请求任何帮助，非常感谢。

错误处理 postgresql psycopg2 数据库迁移数据导入数据库编码 utf8 latin1

psycopg2.DataError: 无效的UTF8编码字节序列：0xa0

2 个回答

撰写回答