creating views declarative base is confusing

[anusha kadambala]

hello all,

I want to create view based on the following tables

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,scoped_session,relation,backref

engine = create_engine('postgresql:///try', echo=False)
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self,name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password
       
    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

users_table = User.__table__


class Office(Base):
    __tablename__ = 'office'
    cid = Column(Integer, primary_key=True)
    name = Column(String)
    org = Column(String)
  

    def __init__(self,name, org):
        self.name = name
        self.org = org
       
    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.org)

office_table = Office.__table__
metadata = Base.metadata
metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine))
Session.add_all([User('wendy', 'Wendy Williams', 'foobar'),User('mary', 'Mary Contrary', 'xxg527'),User('fred', 'Fred Flinstone', 'blah'),Office('wendy','Comet'),Office('kk','Comet')])
Session.commit()

I want to write a view class in which i can select user.name and office.name where user.id = office.id which reflects in the database.

I got something like this which i got when i google but i didnt understand how it happens actually

http://groups.google.com/group/sqlalchemy/browse_thread/thread/cd4455178d886e73



--


Njoy the share of Freedom :)
Anusha Kadambala

[Michael Bayer]

the quickest way is to just map to a join:

j = users_table.join(office_table, users_table.c.id==office_table.c.cid)

class UserOffice(Base):

    __table__ = j

    

    # disambiguate "office.name" from "users.name"

    office_name = j.c.office_name

    

print Session.query(UserOffice).filter(UserOffice.name=='wendy').all()

print Session.query(UserOffice).filter(UserOffice.office_name=='mary').all()

if you want to do a real CREATE VIEW, we have that new recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views , but the mapper part of it would still look like:

class UserOffice(Base):

    __table__ = myview

   # ...

http://groups.google.com/group/sqlalchemy/browse_thread/thread/cd4455178d886e73



--


Njoy the share of Freedom :)
Anusha Kadambala

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.