How to override column name in sqlalchemy using reflective and descriptive syntax

Question

Hello, I am trying to port an outdated application to python with sqlalchemy.

The existing application database has about 300 tables, and each table has a class called def, for example:

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 like:

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

But when I try to find the def column, I end up getting an error. For instance:

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

Since def is a reserved word for python: (

To overcome this problem, I can create my class as:

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

But putting _ in front of each column name is boring and not fantasizing.

What I would like to do is access the def column with a different name / (key?).

Any ideas?

--- Edit --- (Editing the original message at the request of TokenMacGuy )

While I accepted the TokenMacGuy answer, I tried it before:

 engine = create_engine('firebird://sysdba: masterkey@127.0.0.1 /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 have sqlalchemy.exc.ArgumentError: cannot add extra 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 did my previous attemp cause an error?

Answer 1

You can get your cake and eat it too. Define the columns you want to rename; sqlalchemy will automatically output any columns that you did not 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:

__table__ argument, you tell the declarative extension that you already have a properly configured Table that you want to use. But this is not so; you want to have a def column referenced by another name in the class. Using __tablename__ and __table_args__ , you __table_args__ construction of the table until you tell the declarative how you want to use this table. There is no elegant work if you are dead when using __table__ . You can specify a property that will _def = getattr(__table__.c, 'def') column, or you can specify the column as _def = getattr(__table__.c, 'def') .

Indeed, you should just use __tablename__ ; It is more convenient and flexible, and it is a great example of why.

(alternatively, the most traditional for alternative identifiers is underscore instead of top underscore, use def_ instead of _def ; leading underscores usually mean that the name is 'private' or 'implementation detail' if the name should be public but looks like a personal name, this may cause more confusion than necessary)

Answer 2

You can define your table as follows:

 mymetadata = MetaData() Base = declarative_base(metadata=mymetadata) class Accnt(Base): __tablename__ = 'accnt' code = Column(String(20)) def_ = Column(String(50)) 

Answer 3

It may be too much brute force, but another approach is to use the sqlacodegen library to automatically create all models for your database, then change them manually or configure sqlacodegen to create models using your conventions.It supports the mapping of reserved words to other characters.

See https://pypi.python.org/pypi/sqlacodegen . Good tool.