Re: [Sqlalchemy-tickets] [sqlalchemy] #2856: ORM mapping of reflected col names that are not ASCII (was: Oracle error with reflect on table with cyrillic column names in)

[sqlalchemy <mi...@zz...>]

#2856: ORM mapping of reflected col names that are not ASCII
-------------------------+-------------------------------------------------
      Reporter:  teddy   |      Owner:  zzzeek
          Type:  defect  |     Status:  closed
      Priority:  medium  |  Milestone:
     Component:          |   Severity:  no triage selected yet
  declarative            |   Keywords:  oracle, cyrillic, declarative,
    Resolution:          |  reflexion
  worksforme             |
Progress State:          |
  completed/closed       |
-------------------------+-------------------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => worksforme
 * status_field:  awaiting triage => completed/closed


Comment:

 OK when i first saw this I assumed the oracle dialect couldn't reflect
 those names, but it seems like it is, so wow on that.

 Second issue, note that the ORM is an optional component here.  The
 reflection is working fine as you're getting a `Table` with the names you
 want.    The issue is that you're mapping that `Table` to a Python class,
 a process which by default links the columns to same-named attributes on a
 class - and Python 2.x does not accept non-ASCII identifiers - this is not
 a SQLAlchemy limitation, it's a Python 2.x one.

 So if you wanted to do #1, e.g. `CyrTest.Поле1.`, you could use Python 3
 (and you'd definitely want to use 0.9, which is released as beta1 so far,
 for the best Py3K compat), otherwise it's not possible.  In Python 3 the
 reflection + mapping works as is.

 Now using a system like a transliteration library, great, that's not a
 built-in for SQLAlchemy, you do in fact need to go with the "key"
 approach.  We provide the "column_reflect" event to most effectively
 redefine keys as needed.

 Below is an example program against SQLite illustrating both a Python 3
 and a Python 2 approach.   I don't have a transliteration library
 available so I am using `"attr_%s" % hexlify(name.encode('utf-8')` here,
 replace that with whatever you'd like.

 If the reflection is working then this is as far as we can go, so I'm
 closing this as "worksforme".

 {{{
 #!python
 #!coding: utf-8

 from sqlalchemy import create_engine, Table
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import Session
 import sys

 e = create_engine("sqlite://", echo=True)

 e.execute(u"""
     CREATE TABLE test (

         "ИД" INTEGER PRIMARY KEY,
         "ПОЛЕ1" VARCHAR(20),
         "ЕЩЕПОЛЕ" VARCHAR(20)
     )
 """)

 e.execute(u"""
     INSERT INTO test ("ИД", "ПОЛЕ1", "ЕЩЕПОЛЕ") VALUES (1, 'v1', 'v2')
 """)


 py3 = sys.version_info >= (3, )

 if not py3:
     from sqlalchemy import event
     import binascii

     @event.listens_for(Table, "column_reflect")
     def column_reflect(inspector, table, column_info):
         # do whatever encoding/transliteration you'd like here
         column_info['key'] = "attr_%s" %
 binascii.hexlify(column_info['name'].encode('utf-8'))


 Base = declarative_base()

 class Foo(Base):
     __table__ = Table('test', Base.metadata, autoload=True,
 autoload_with=e)

 s = Session(e)
 f1 = s.query(Foo).first()

 if py3:
     # using exec so this script runs in py2k as well,
     # you can use non-ascii identifiers in py3k.
     exec("print(f1.ПОЛЕ1)")
 else:
     # our hexlified (or transliteterated, or whatever)
     print(f1.attr_d09fd09ed09bd09531)
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2856#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python