How to override a column name in sqlalchemy using reflection and descriptive syntax

Question

Hello I'm trying to port a legacy application to python with sqlalchemy.

The application's existing database has about 300 tables and in every table there is a colum named def such as :

create table accnt (
    code varchar(20)
  , def varchar(50) --for accnt definition
  , ...
)

So when with declarative syntax and reflection I can easily create my class as :

class Accnt(Base):
    __table__ = Table('accnt', metadata, autoload = True, autoload_with=engine)

But when I try to reach def column I eventually get an error. For example :

q = session.query(Accnt)
for row in q:
    print q.def

Because def is a reserved word for python :(

To overcome this issue I can create my class as :

class Accnt(Base):
    __table__ = Table('accnt', metadata, autoload = True, autoload_with=engine)
    __mapper_args__ = {'column_prefix':'_'}

But putting a _ in front of every column name is boring and not fancy.

What I'd like to do is access def column with another name / ( key ?).

Any ideas?

--- Edit --- ( Editing original post as requested by TokenMacGuy )

While I've accepted TokenMacGuy's answer I've tried it before as :

engine = create_engine('firebird://sysdba:[email protected]/d:\\prj\\db2\\makki.fdb?charse‌​t=WIN1254', echo=False) 
metadata = MetaData() 
DbSession = sessionmaker(bind=engine) 
Base = declarative_base() 

class Accnt(Base):
    __table__ = Table('accnt', metadata, autoload = True, autoload_with=engine) 
    _def = Column("def", String(50))

And I've got sqlalchemy.exc.ArgumentError: Can't add additional column 'def' when specifying table error..

The main difference between me and TokenMacGuy is

mine       : _table_ ....
TokenMcGuy : __tablename__ = 'accnt'
             __table_args__ = {'autoload': True}

and metadata binding...

So, why my previous attemp generated an error ?

Answer 1

You can have your cake and eat it too. Define the columns you want to rename; sqlalchemy will automatically infer any columns you don't mention.

>>> from sqlalchemy import *
>>> from sqlalchemy.ext.declarative import declarative_base
>>> 
>>> engine = create_engine("sqlite:///:memory:")
>>> 
>>> engine.execute("""
... create table accnt (
...     id integer primary key,
...     code varchar(20),
...     def varchar(50)
... )
... """)
<sqlalchemy.engine.base.ResultProxy object at 0x2122750>
>>> 
>>> Base = declarative_base()
>>> 
>>> Base.metadata.bind = engine
>>> 
>>> class Accnt(Base):
...     __tablename__ = 'accnt'
...     __table_args__ = {'autoload': True}
...     def_ = Column('def', String)
... 
>>> Accnt.def_
<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x2122e90>
>>> Accnt.code
<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x2127090>
>>> 

EDIT:

By supplying a __table__ argument, you're telling the declarative extension that you already have a properly configured Table that you'd like to use. But that's not true; you want to have the def column referred to by another name in the class. By using __tablename__ and __table_args__, you defer the construction of the table until after you've told declarative how you want to use that table. There's no elegant work-around if you are dead set on using __table__. You can provide a property that aliases the column or you may be able to specify the column as _def = getattr(__table__.c, 'def').

Really, you should just use __tablename__; It's both more convenient and more flexible, and this is a great example of why.

(as an aside, it's most conventional to give alternate identifiers a trailing underscore instead of a leading underscore, use def_ instead of _def; leading underscores usually signify that the name is 'private' or 'an implementation detail', if the name is meant to be public, but looks like a private name, it may cause more confusion than is necessary)