A tricky one worthy of being on the wiki, so I added the recipe for this here:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/RelationshipOnCast It renders CAST when the attribute load occurs and also coerces string to int on the persist side. Let me know if it works out for you. There's a longstanding ticket in trac to make this process more straightforward as well. On Sep 16, 2011, at 1:51 PM, Robert Rollins wrote: > I have a table of survey responces which each correspond to a particular > student, identified by an integer student_id. The database which stores that > student's data, however, has their id in a VARCHAR2 column. The data in that > column is really just an int, but I can't change the Oracle database. I've > tried to relate these two tables using: > > SurveyResponse.student = relationship("Person", > primaryjoin="Person.person_id==SurveyResponse.student_id", > foreign_keys=[SurveyResponse.student_id] > ) > > But I get this error when I try to retrieve the student attribute from a > SurveyResponse instance: > ORA-01722: invalid number > > I can't use a ForeignKey object, because these two tables are in different > databases. That's why I've got the relationship defined so explicitly. > > So, is there any way to tell SQLAlchemy to convert the int "foreign key" on > the SurveyResponse table into a VARCHAR2 to match the primary key on the > Person table? Or is there some other way to make this relationship work? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/o5UECbf9O_4J. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
