ctengiz ctengiz - 1 month ago 7
Python Question

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

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:masterkey@\\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

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 ?


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>


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)