CODEWITHSUNDEEP
pythonsqlalchemy
David S.
David S.

Reputation: 11200

How to get Column reference from declarative table in SqlAlchemy?

I use the declarative style to define my tables with SQLAlchemy.

Base = declarative_base()

class MyTable(Base):
    id = Column('someone_put_a_strange_for_id', Integer)

Now, when I define ForeignKey in the class, I need to use

MyTable.__table__.c.someone_put_a_strange_for_id

to refer to this id Column attribute. Is there any way I can use

MyTable.id.some_magic

to get this Column attribute?

Upvotes: 0

Views: 1814

Answers (1)

zzzeek
zzzeek

Reputation: 75317

There are two easy options here.

The simplest is, just refer to the attribute on the class!

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'mt'
    id = Column('someone_put_a_strange_for_id', Integer, primary_key=True)
    bar = Column(Integer)

fk = ForeignKeyConstraint(['bar'], [MyTable.id])

This is not actually the Column object, however in virtually all situations, SQLAlchemy will look at this object for a special attribute called __clause_element__() which will pull out the actual Column. In modern SQLAlchemy versions this should work just about everywhere, post a bug report if there's someplace it's not working.

The other is to put a .key on the column:

class MyTable(Base):
    id = Column('someone_put_a_strange_for_id', Integer, key='id')

Above, that column is now table.c.id in all in-Python cases, the "name" is only on the SQL side. So if you are dealing with table.c directly, there you go.

There's still more options! The mapper that's on your class also has its own .c attribute, you can get at it this way also:

from sqlalchemy import inspect

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'mt'
    id = Column('someone_put_a_strange_for_id', Integer, primary_key=True)

inspect(MyTable).c.id

Upvotes: 1

Related Questions