Re: [Sqlalchemy-tickets] [sqlalchemy] #2856: ORM mapping of reflected col names that are not ASCII
Brought to you by:
zzzeek
|
From: sqlalchemy <mi...@zz...> - 2013-10-31 19:00:49
|
#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
|