使用 sqlalchemy 和 postgresql 编码错误

Encoding error with sqlalchemy and postgresql

提问by jdurango

I'm using pyramid for a web application with a postgres database, wtforms, sqlalchemy and jinja2 and I'm having this error when the application try to get the issues types from database to fill a select field with wtforms:

我正在将金字塔用于带有 postgres 数据库、wtforms、sqlalchemy 和 jinja2 的 Web 应用程序，当应用程序尝试从数据库中获取问题类型以使用 wtforms 填充选择字段时出现此错误：

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

this is the issue types table into model.py:

这是model.py中的问题类型表：

class Mixin(object):
    id = Column(Integer, primary_key=True, autoincrement=True)
    created = Column(DateTime())
    modified = Column(DateTime())

    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }
    __mapper_args__ = {'extension': BaseExtension()}

class IssueType(Mixin, Base):
    __tablename__ = "ma_issue_types"
    name = Column(Unicode(40), nullable=False)

    def __init__(self, name):
        self.name = name

Into bd I have this:

进入 bd 我有这个：

# select name from ma_issue_types where id = 3;
name    
------------
Teléfono

this is the part where the error occurs

这是发生错误的部分

# -*- coding: utf-8 -*-

from issuemall.models import DBSession, IssueType


class IssueTypeDao(object):

    def getAll(self):
        dbsession = DBSession()
        return dbsession.query(IssueType).all() #HERE THROWS THE ERROR

this is the Traceback

这是回溯

Traceback (most recent call last):
  File "/issueMall/issuemall/controller/issueRegisterController.py", line 16, in issue_register
    form = IssueRegisterForm(request.POST)
  File "/env/lib/python2.7/site-packages/wtforms/form.py", line 178, in __call__
    return type.__call__(cls, *args, **kwargs)
  File "/env/lib/python2.7/site-packages/wtforms/form.py", line 224, in __init__
    super(Form, self).__init__(self._unbound_fields, prefix=prefix)
  File "/env/lib/python2.7/site-packages/wtforms/form.py", line 39, in __init__
    field = unbound_field.bind(form=self, name=name, prefix=prefix, translations=translations)
  File "/env/lib/python2.7/site-packages/wtforms/fields/core.py", line 301, in bind
    return self.field_class(_form=form, _prefix=prefix, _name=name, _translations=translations, *self.args, **dict(self.kwargs, **kwargs))
  File "/issueMall/issuemall/form/generalForm.py", line 11, in __init__
    types = issueTypeDao.getAll()
  File "/issueMall/issuemall/dao/master/issueTypeDao.py", line 11, in getAll
    return self.__dbsession.query(IssueType).all()
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2115, in all
    return list(self)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2341, in instances
    fetch = cursor.fetchall()
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 3205, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 3172, in _fetchall_impl
    return self.cursor.fetchall()
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 5: ordinal not in range(128)

I try this, but it did not work ascii as default encoding in python

我尝试了这个，但它在 python 中作为默认编码没有使用 ascii

and I try something like this, but it did not work

我尝试了这样的事情，但没有用

gae python ascii codec cant decode byte

gae python ascii 编解码器无法解码字节

return dbsession.query(IssueType.id, IssueType.name.encode('utf-8')).all() #or decode('utf-8')

回答by Martijn Pieters

You need to configure Psycopg2's client encoding. See the SQLAlchemy documentation:

您需要配置 Psycopg2 的客户端编码。请参阅SQLAlchemy 文档：

By default, the psycopg2 driver uses the psycopg2.extensions.UNICODEextension, such that the DBAPI receives and returns all strings as Python Unicode objects directly - SQLAlchemy passes these values through without change. Psycopg2 here will encode/decode string values based on the current “client encoding” setting; by default this is the value in the postgresql.conffile, which often defaults to SQL_ASCII. Typically, this can be changed to utf-8, as a more useful default:

#client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

A second way to affect the client encoding is to set it within Psycopg2 locally. SQLAlchemy will call psycopg2's set_client_encoding()method (see: http://initd.org/psycopg/docs/connection.html#connection.set_client_encoding) on all new connections based on the value passed to create_engine()using the client_encodingparameter:

engine = create_engine("postgresql://user:pass@host/dbname", client_encoding='utf8')

This overrides the encoding specified in the Postgresql client configuration.

默认情况下，psycopg2 驱动程序使用psycopg2.extensions.UNICODE扩展，以便 DBAPI 直接接收和返回所有字符串作为 Python Unicode 对象 - SQLAlchemy 不加更改地传递这些值。此处的 Psycopg2 将根据当前的“客户端编码”设置对字符串值进行编码/解码；默认情况下，这是postgresql.conf文件中的值，通常默认为SQL_ASCII. 通常，这可以更改为utf-8，作为更有用的默认值：

#client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

影响客户端编码的第二种方法是在本地 Psycopg2 中设置它。SQLAlchemy 将根据传递给使用参数的值在所有新连接上调用 psycopg2 的set_client_encoding()方法（参见：http: //initd.org/psycopg/docs/connection.html#connection.set_client_encoding）：create_engine()client_encoding

engine = create_engine("postgresql://user:pass@host/dbname", client_encoding='utf8')

这会覆盖 Postgresql 客户端配置中指定的编码。

The client_encodingparameter can be specified as a query string in the engine URL:

该client_encoding参数可以指定为引擎 URL 中的查询字符串：

 postgresql://user:pass@host/dbname?client_encoding=utf8

回答by W.Perrin

I use mysql and set the charset like this. It works for me.

我使用 mysql 并像这样设置字符集。这个对我有用。

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

db_url = {
    'database': 'db_name',
    'drivername': 'mysql',
    'username': 'username',
    'password': 'mypassword',
    'host': '127.0.0.1',
    'query': {'charset': 'utf8'},
}

engine = create_engine(URL(**db_url), encoding="utf8")