14

As Mentioned here I created View.

Is following possible to create view class for using with session?

v = Table('viewname', metadata, autoload=True)

class ViewName(object):
    def __init__(self, name):
       self.name = name

mapper(ViewName, v)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Syed Habib M
  • 1,757
  • 1
  • 17
  • 30

2 Answers2

24

Finally I found it.

We can create class for sql view's. Here is 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
        )

That's it. We can access "viewname" table using ViewName class.

Thank you for all who are respond me.

Syed Habib M
  • 1,757
  • 1
  • 17
  • 30
15

You can do this, but you have to define a primary key manually. Assuming that id is a column of v which you want to use as the primary key (as is the case in my original example code), this works:

from sqlalchemy import orm

class ViewName(object):
    def __init__(self, name):
       self.name = name

orm.mapper(ViewName, v, primary_key=[v.c.id])

Session = orm.sessionmaker(bind=engine)
for r in Session().query(ViewName):
    print r.id, r.number

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

EDIT (van in a comment to my answer linked above): Alternatively, you can change the view definiton in my original code (and your question) slightly and write: 

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

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

Community
  • 1
  • 1
stephan
  • 10,104
  • 1
  • 51
  • 64
  • 2
    Apology for this. Correct me if I am wrong. I mixed your code and @van code. The following is works perfect for me. `class ViewName(Base): __table__ = Table( 'viewname', Base.metadata, Column('id',Integer, primary_key=True), autoload=True, autoload_with=engine)` – Syed Habib M Dec 12 '13 at 10:39
  • @SyedHabibM: yes, that works too. You just have to define the primary key somewhere. – stephan Dec 12 '13 at 10:59
  • How does this work in conjunction with create_all/drop_all? Currently drop_all fails since it tries to drop a table when it is in fact a view. – fgblomqvist Apr 14 '18 at 19:13
  • @fgblomqvist: I guess you want to drop the view, then? TProbably the best approach would be to extend `sql.ddl.SchemaDropper` to handle views. This would require some work because some of your views might have been reflected (ie you haven't created them and don't know they are views). As an alternative, a simple hack would be to add the corresponding `DropView` construct and drop the views manually before calling `drop_all` (which obviously somewhat defeats the purpose of `drop_all`). (...) – stephan Apr 15 '18 at 14:55
  • 1
    @fgblomqvist: (... continued) If you are only after views that you have created with `CreateView`, you could also extend `@compiles(DropTable)` to basically check whether it is one of your views and then call `compiler.visit_drop_view(element, **kw)`, and otherwise call `compiler.visit_drop_table(element, **kw)` (where `visit_drop_view` has to be added by you). This said, you will find other cases where the view does not work like expected, because (doh) a view is not a table ;) – stephan Apr 15 '18 at 15:07
  • 1
    @stephan I did indeed end up extending `@compiles(DropTable)` and -- surprise surprise -- `visit_drop_view` already exists! But yeah, what I do is that I set an attr on the Table (is_view) and then check for that. Works like a charm. – fgblomqvist Apr 15 '18 at 23:02