如何使用SQLAlchemy将数据从SQL_ASCII复制到UTF8的PostgreSQL数据库？

Question

我正在写一个数据库数据迁移工具，主要用的是SQLAlchemy的表达语言。

我的源数据库可能是UTF8编码，也可能是SQL_ASCII编码。而我的目标数据库一定是UTF8编码。

我在SQLAlchemy 0.6.6中使用的是psycopg2驱动。

我的大致迁移过程是这样的：

for t in target_tables:
    log.info("Migrating data from %s", t.fullname)
    source = self.source_md.tables[self.source_schema + "." + t.name]
    for row in source.select().execute():
        with sql_logging(logging.INFO):
            conn.execute(t.insert(), row)

如果我在引擎上什么编码相关的设置都不做，当我遍历select()的结果时，就会出现这样的情况：

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 1: ordinal not in range(128)

如果我在引擎上设置了use_native_unicode=True, encoding='utf-8'，那么当我尝试插入新行时，就会出现这样的情况：

sqlalchemy.exc.DataError: (DataError) invalid byte sequence for encoding "UTF8": 0xeb6d20
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
 'INSERT INTO project_ghtests_survey000005.employees (first_name, employee_id) VALUES (%(first_name)s, %(employee_id)s)' {'first_name': 'Art\xebm', 'employee_id': '1234'}

更新细节

为了让查询更快，这里是使用的软件堆栈：

• 源数据库编码：SQL_ASCII
• 目标数据库编码：UTF8
• Python版本：2.7
• SQLAlchemy版本：0.6.6
• psycopg2版本：2.2.2
• PostgreSQL服务器版本：8.2

Answer 1

既然UTF-8和UTF-8是向后兼容的，那为什么还要用SQL_ASCII而不是UTF8呢？

我觉得你的编码问题可能更像是latin1或者类似的编码，而不是ASCII和UTF8之间的问题。

Answer 2

结果发现，解决办法是把连接的字符编码设置为 'latin1'。

我通过使用一个叫做 PoolListener 的东西来实现这个设置，代码如下：

class EncodingListener(PoolListener):

    def connect(self, dbapi_con, con_record):
        with closing(dbapi_con.cursor()) as cur:
            cur.execute('show client_encoding')
            encoding = cur.fetchone()[0]

        if encoding.upper() == 'UTF8':
            return

        dbapi_con.set_client_encoding('latin1')