How to set sqlalchemy in Python when connect to postgresql's table with character ñ in columns

Question

Here is the code I am trying run:

import pandas as p
import locale
locale.setlocale(locale.LC_ALL, '')

Out[24]: 'Spanish_Argentina.1252'

from sqlalchemy import create_engine    
engine = create_engine('postgresql://user:pass@localhost/mdb',client_encoding = 'WIN1252')
p.read_sql("SELECT año FROM nacvivos.nac LIMIT 10",engine)

But a Had this encoding problem:

ProgrammingError: (ProgrammingError) no existe la columna «aã±o» LINE 1: SELECT aÃ±o FROM nacvivos.nac LIMIT 10 ^ 'SELECT a\xc3\xb1o FROM nacvivos.nac LIMIT 10' {}

How should I set configuration in python for deal with this? Ignoring the obvious answer that modify the name of the column will solve the problem. Database in postgresql is win1252 encoding. Thanks in advance.

joris · Accepted Answer · 2015-02-09 09:27:05Z

1

You have to specify the encoding keyword in create_engine (and not client_encoding), with 'cp1252' being the python codec name for 'WIN1252':

engine = create_engine('postgresql://user:pass@localhost/mdb', encoding='cp1252')

and then be sure your query is also encoded:

qry = u"SELECT año FROM nacvivos.nac LIMIT 10".encode('cp1252')
pd.read_sql(qry, engine)

With these two steps, I was able to read in a table with 'año' as a column name.

answered Feb 9, 2015 at 9:27

joris

140k37 gold badges257 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to set sqlalchemy in Python when connect to postgresql's table with character ñ in columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related