getting columns attributes in declarative style with sqlalchemy

Discussion in 'Python' started by Gabriele, Oct 28, 2011.

  1. Gabriele

    Gabriele Guest

    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
     
    Gabriele, Oct 28, 2011
    #1
    1. Advertisements

  2. Gabriele

    tres.bailey Guest

    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, Nov 1, 2011
    #2
    1. Advertisements

  3. Gabriele

    tres.bailey Guest

    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
     
    tres.bailey, Nov 1, 2011
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.