Encoding error with sqlalchemy and postgresql

Question

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:

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:

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:

# 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

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

gae python ascii codec cant decode byte

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

That's a whole lot of code. Could you try and localize your problem more, and edit our question to make it more tractable? — millimoose, Feb 09 '13 at 00:30
No, that's where the error manifests. I mean try to debug stuff to see what gets called in the end to see what is getting decoded using `ascii` and why? — millimoose, Feb 09 '13 at 00:38
just in this line the error manifiest return dbsession.query(IssueType).all() until that point the only thing that happens are called function without passing parameters, since it is obtained all records of the table — jdurango, Feb 09 '13 at 00:53
Somewhere before you use the `DBSession` it must have gotten a call like `DBSession.configure(bind=engine)` or something like this (unless I am confusing frameworks). Sure the `engine` has an `encoding='utf-8'` but could you please double check it does? — Clemens Klein-Robbenhaar, Feb 09 '13 at 01:04
I used this DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) — jdurango, Feb 09 '13 at 01:16
@MartijnPieters here is [traceback](http://pastebin.com/ZWkWHRNV) — jdurango, Feb 09 '13 at 13:14
You should [edit] your question and add that traceback there. — Martijn Pieters, Feb 09 '13 at 13:18
What database are you using? MySQL? This is the database client (*outside* of SQLAlchemy). — Martijn Pieters, Feb 09 '13 at 13:20
Ah, from an insert (now edited out) you inserted a byte string, not a unicode value. Insert `u'Teléfono'` instead of `'Teléfono'` perhaps? — Martijn Pieters, Feb 09 '13 at 13:22
@MartijnPieters the database is postgres and when I do u'Teléfono' get this error [Traceback](http://pastebin.com/vEYb0RUn) — jdurango, Feb 09 '13 at 13:29
@jdurango: Why then does your code sample include `mysql_engine` and `mysql_charset`? — Martijn Pieters, Feb 09 '13 at 13:32
@MartijnPieters Oh! I forgot to change that, initially was working with mysql and then I go to lock with postgres; I will remove that part to run it again to see what happens — jdurango, Feb 09 '13 at 13:39

Martijn Pieters · Accepted Answer · 2014-04-10T14:09:58.310

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

By default, the psycopg2 driver uses the psycopg2.extensions.UNICODE extension, 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.conf file, 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_encoding parameter:
engine = create_engine("postgresql://user:pass@host/dbname", client_encoding='utf8')
This overrides the encoding specified in the Postgresql client configuration.

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

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

Thank you very much, the solution was change the encoding in the postgresql.conf — jdurango, Feb 09 '13 at 14:04
you can also set **postgresql://user:pass@host/dbname?client_encoding=utf8** — Bahadir Cambel, Mar 07 '14 at 14:49

score 1 · Answer 2 · answered Mar 08 '18 at 09:42

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

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")

Encoding error with sqlalchemy and postgresql

2 Answers2

Linked