On Feb 10, 2010, at 5:35 AM, anusha kadambala wrote:
> 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
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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
