get table columns from sqlAlchemy table model

Question

I have a table where I would like to fetch all the column names however after browsing the interwebs I could not find a way that works. This is what my table looks like:

class myTable(Base):
    __tablename__ = 'myTable'

    col1 = Column(Integer, primary_key=True)
    col2 = Column(Unicode(10))
    col3 = Column(Integer)

    col4 = Column(Numeric(10, 6))
    col5 = Column(Numeric(6,3))
    col6 = Column(Numeric(6,3))

    child = relationship('tChild',
                          backref=backref('children'))

I would like to be able to print all the column names from a for loop. ex:

"col1", "col2", "col3".... etc

This is pretty easy with regular sql but I can't seem to figure out how to do it using sqlAlchemy table models

Answer 1

You get all of the columns from __table__.columns:

myTable.__table__.columns

or

myTable.__table__.c

The columns would be in format myTable.col1 (table name is included). If you want just column names, get the .key for each column:

[column.key for column in myTable.__table__.columns]

Answer 2

Below is a general as_dict implementation for an sqlalchemy table based on @ChaimG answer. And an additional example of a __repr__ that is implemented using it.

from orm.base import Base


class MyTable(Base):
    __tablename__ = 'table_name'

    # Columns go here.....

    def as_dict(self):
        """
        Helper function that allows traversing the table's instance columns as key values

        :return: (key, value) iterator
        """
        for key in self.__table__.columns.keys():
            value = self.__getattribute__(key)
            yield key, value

    def __repr__(self):
        """
        General __repr__ implementation for an sqlalchemy table
        """
        values = []
        for key, value in self.as_dict():
            key_value_str = f"{key}={value}"
            values.append(key_value_str)

        values_str = ", ".join(values)
        cls_name = self.__class__.__name__
        return f"<{cls_name}({values_str})>"

Answer 3

Since this question is pretty high on search engines, I want to add, that if you are using core, you can get the columns using

your_table.c

If you are using ORM like OP, the accepted answer works.

Answer 4

You get all of the columns from __table__.columns:

That is true, but you should avoid accessing "protected" and "private" members of objects. Your linter should complain if you try to do this.

The proper way to access a tables columns is though SQLAlchemy's Runtime Inspection API. From the docs:

the return value of inspect() is guaranteed to obey a documented API, thus allowing third party tools which build on top of SQLAlchemy configurations to be constructed in a forwards-compatible way.

You can use it like this:

from sqlalchemy import inspect

# ###########################################
# Inspect a Mapped Ojbect
#  https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#orm-mapper-inspection-mapper
# ###########################################
mapped_object = inspect(myTable)
mapped_object.columns.items()
[('col1',
  Column('col1', Integer(), table=<myTable>, primary_key=True, nullable=False)),
 ('col2', Column('col2', Unicode(length=10), table=<myTable>)),
 ('col3', Column('col3', Integer(), table=<myTable>)),
 ('col4', Column('col4', Numeric(precision=10, scale=6), table=<myTable>)),
 ('col5', Column('col5', Numeric(precision=6, scale=3), table=<myTable>)),
 ('col6', Column('col6', Numeric(precision=6, scale=3), table=<myTable>))]

[column.key for column in mapped_object.columns]
['col1', 'col2', 'col3', 'col4', 'col5', 'col6']

# ###########################################
# Inspect a Mapped Instance
#  https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html#orm-mapper-inspection-instancestate
# ###########################################
my_table = myTable(...)
mapped_instance = inspect(my_table)

# Notice: This collection include 'child'.  The columns from the mapped object did not.
mapped_instance.attrs.items()
[('child', <sqlalchemy.orm.state.AttributeState at 0xffff9c748130>),
 ('col1', <sqlalchemy.orm.state.AttributeState at 0xffff9c7481f0>),
 ('col2', <sqlalchemy.orm.state.AttributeState at 0xffff9c748190>),
 ('col3', <sqlalchemy.orm.state.AttributeState at 0xffff9c7482b0>),
 ('col4', <sqlalchemy.orm.state.AttributeState at 0xffff9c748100>),
 ('col5', <sqlalchemy.orm.state.AttributeState at 0xffff9c748160>),
 ('col6', <sqlalchemy.orm.state.AttributeState at 0xffff9c748370>)]

# Notice: You can get the same collection as the mapped object returned by going through the mapper.
mapped_instance.mapper.columns.items()
[('col1',
  Column('col1', Integer(), table=<myTable>, primary_key=True, nullable=False)),
 ('col2', Column('col2', Unicode(length=10), table=<myTable>)),
 ('col3', Column('col3', Integer(), table=<myTable>)),
 ('col4', Column('col4', Numeric(precision=10, scale=6), table=<myTable>)),
 ('col5', Column('col5', Numeric(precision=6, scale=3), table=<myTable>)),
 ('col6', Column('col6', Numeric(precision=6, scale=3), table=<myTable>))]

Answer 5

Understanding that columns are class members, they are therefore stored in the __dict__ attribute of the class. Therefore, myTable.__dict__.keys() will give you a list of columns, in addition to other class members.

This is helpful for desiring to know the members / methods of any class you are working with.