Many-to-One relationship where each side has different typed keys?

[Robert Rollins]

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?

[Michael Bayer]

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.

--
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 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.

[Robert Rollins]

You're a lifesaver!  That method works perfectly for me.  Since the Oracle side is read-only, I didn't need to use the StringAsInt class, but the rest of it worked like a charm.

I look forward to the eventual resolution of that ticket, as this process really is quite convoluted.