Encoding error with sqlalchemy and postgresql
Solution 1
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 thepostgresql.conf
file, which often defaults toSQL_ASCII
. Typically, this can be changed toutf-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 tocreate_engine()
using theclient_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
Solution 2
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")
Related videos on Youtube
Comments
-
jdurango about 4 years
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')
-
Montre over 11 yearsNo, 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?
-
-
jdurango over 11 yearsThank you very much, the solution was change the encoding in the postgresql.conf
-
Bahadir Cambel about 10 yearsyou can also set postgresql://user:pass@host/dbname?client_encoding=utf8