How to use database views in my model?

[andrew b]

Hi I'm trying to use a database view in my model, but I get errors about a primary key since there is none. I want to use a view to make it easier to give the user form where they can assign a project_role to a project_role. I can do it using the primary key ID's from my database, but it's not very meaningful to a user to have to enter ID's instead of names. 

Here's the code I'm trying to use to define my view in the model:

class ProjectRoleView(DeclarativeBase):

    __tablename__ = 'project_to_role_names'

    __table_args__ = {}

    

    #column definitions

    project_name = Column(u'project_name', VARCHAR(45))

    role_name = Column(u'role_name', VARCHAR(45))

Or would it be better to query the DB and do the joins and stuff in python instead of in mysql?

[Carlos Daniel Ruvalcaba Valenzuela]

If you are using TG2 or 1.5 check the SQLAlchemy docs, you can have a
foreign key defined (as an integer) and declare a relationship which
SQLAlchemy can load as needed or even with a join the referenced table
object, for example:

class ProjectRole(DeclarativeBase):
__tablename__ = "ProjectRole"
project_role_id = Column(Integer, primary_key=True)
name = Column(Unicode(64))

class Project(DeclarativeBase):
__tablename__ = "Project"
project_id = Column(Integer, primary_key=True)
project_role_id = Column(Integer, ForeignKey(ProjectRole.project_role_id))
project_role = relationship(ProjectRole)

Thus when you load a Project instance the project_role attribute is an
instance of ProjectRole, and you don't need to do anything more
SQLAlchemy does it for you.

Regards,
Carlos Ruvalcaba

> --
> You received this message because you are subscribed to the Google Groups
> "TurboGears" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to turbogears+...@googlegroups.com.
> To post to this group, send email to turbo...@googlegroups.com.
> Visit this group at http://groups.google.com/group/turbogears?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>