getting columns attributes in declarative style with sqlalchemy

[Gabriele]

Hi,

I'm tryed to write my first application using SqlAlchemy. I'm using
declarative style. I need to get the attributes of the columns of my
table. This is an example of my very simple model-class:

class Country(base):
__tablename__ = "bookings_countries"

id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
canceled = sqlalchemy.Column(sqlalchemy.Boolean, nullable=False,
default=False)
description = sqlalchemy.Column(Description)

def __init__(self, canceled, description):
self.canceled = canceled
self.description = description

def __repr__(self):
return "<Country ('%s)>" % (self.description)

I want to populate a wx grid using the name of the fields as labels of
the columns.

I found three different solutions of my problem, but none satisfeid
me:

a) using column_descriptions in query object.

col = (model.Country.canceled, model.Country.description)
q =
self.parent.session.query(*col).order_by(model.Country.description)
l = [column["name"] for column in q.column_descriptions]

in this way, l contains exactly what I need, but I don't like because
I must execute an useless query only for having informations about the
structure of my table. Ok, it's not so big problem, but IMO is not a
very good solution

b) reflecting the table
c) using inspector lib from sqlachemy.engine

I don't like because I have to use directly the name of the table in
the database...

It sounds me better and logical to use my class Country... but I can't
find the simple way for doing that... maybe it's very simple, but...

Someone can help me?

Thanks

Gabriele

 

[tres.bailey]

Hi Gabriele,

I'm not an Alchemy expert, but I have used the ColumnProperty of the model/column objects to solve this problem in the past. So to get the column name for the description column in your example above, you would use the following syntax:

Country.description.property.columns[0].name

And this would avoid having to use additional objects than the one you're working on. The ColumnProperty object (and the Column object attribute it contains) will provide you with information such as column name, type, default vals, primary_key, and nullable. You could also get more generic by iterating through your model object's __table__ attribute and grab each column:

[col.name for col in Country.__table__.columns._all_cols]


More information can be found here:
http://www.sqlalchemy.org/docs/core/schema.html?highlight=schema.column#sqlalchemy.schema.Column
and
http://www.sqlalchemy.org/docs/orm/...erty#sqlalchemy.orm.properties.ColumnProperty

 

[tres.bailey]

Sorry for the repost, if it does in fact repost.

I'm no SQLAlchemy expert, but I have used the Table and Column attribute objects from the model object to solve a similar problem in the past. You can use the following syntax to do it:

[col.name for col in Country.__table__.columns._all_cols]

which should return you a list of ['cancelled', 'description']. You can find more information on the attributes you are using here:
http://www.sqlalchemy.org/docs/core/schema.html?highlight=schema.column#sqlalchemy.schema.Column

and
http://www.sqlalchemy.org/docs/core/schema.html?highlight=schema.table#sqlalchemy.schema.Table