Accented column names, introspection and mapping

Question

I would like to access a legacy MSSQL database using SQLAlchemy. With basic schema inspection I could already list the columns of the tables I'm interested in. Unfortunately, these column names sometimes contain accented letters (e.g. "Magánszemély", "LevelezésiCímIrányítószám").

My only requirement is to be able to query this DB.

I've listed some database names for you using the following commands

def inspect_komplex_table():
    table = Table('D_Allomanylista_Komplex_V', meta, autoload=True, autoload_with=engine)
    return table

def get_columns():
    keys = inspect_komplex_table().columns.keys()
    keys.sort()
    txt = '\n'.join(keys)
    open('column_names.txt', 'w').write(txt.encode('utf8'))

This gives a (long) list in the column_names.txt file with lines:

...
JutÉrvKezd
KEZDET
KTVSZAM
KamaraiTagszám
Képviselők
Lejarat
LevelezésiCímIrányítószám
LevelezésiCímUtca
LevelezésiCímVáros
...

I've tried to create a basic mapping without the accented columns first

class BiztositasokModel(object):

    def __init__(self, UgyfKod):
        self.ugyfelkod = UgyfKod

where UgyfKod is one the columns from the introspection, but

mapper(BiztositasokModel, inspect_komplex_table())

fails with UnicodeEncodeError

Could someone give me an idea on how to handle such a DB?

score 0 · Answer 1 · edited May 23 '17 at 12:11

I've managed to find two answers, one using the declarative syntax, the other using the classical mapping.

Both implement a way to change the default naming scheme of the mapping columns to Python object properties.

The classical mapping answer is: SQLAlchemy mapping table with non-ascii columns to class

The answer using a declarative syntax: http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#automating-column-naming-schemes-from-reflected-tables

Accented column names, introspection and mapping

1 Answers1