Is it possible to map a view to a class using mapper in SqlAlchemy?

Question

As mentioned here, I created a View.

Can I create a view class for use with a session?

v = Table('viewname', metadata, autoload=True) class ViewName(object): def __init__(self, name): self.name = name mapper(ViewName, v) 

Answer 1

You can do this, but you must define the primary key manually. Assuming id is the v column that you want to use as the primary key (as is the case with the original code example ), this works:

 from sqlalchemy import orm class ViewName(object): def __init__(self, name): self.name = name orm.mapper(ViewName, v, primary_key=[vcid]) Session = orm.sessionmaker(bind=engine) for r in Session().query(ViewName): print r.id, r.number 

To test this, simply insert this snippet at the end of my working example in the answer above. See the documentation for more details (for example, you can use properties to define foreign keys).

EDIT (van in comment to my answer above): Alternatively, you can slightly modify the view in my source code (and question) and write:

 v = Table('viewname', metadata, Column('id', Integer, primary_key=True), autoload=True) 

ie, add the primary key to the table definition already. Then you don't need the primary_key argument in orm.mapper , and the rest of the code in your question works out of the box.

Answer 2

Finally, I found it.

We can create a class to represent sql. Here is a simple example.

 class ViewName(Base): __table__ = Table('viewname', Base.metadata, Column('id', Integer, primary_key=True), Column('foreign_key', Integer, ForeignKey('sometablename.id'), autoload=True, autoload_with=engine ) 

What is it. We can access the table "viewname" using the ViewName class.

Thanks to everyone who answers me.