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.
