1

I'm trying to build Python models using Elixir representing tables in an existing MySQL database connected via a mysql+pyodbc connection string. Elixir reflects the column structure effortlessly, but doesn't seem to be able to reflect the relationships, so now I'm trying to specify those. I'm having a beast of a time with the syntax, both the declarative and the DSL forms.

As an example, I have a foreign key relationship across two tables (one-to-one) created with the following SQL statements:

ALTER TABLE db.person
    ADD CONSTRAINT fk_person_id_ref_person_program_id
    FOREIGN KEY (person_id) REFERENCES db.person_program(person_id);

ALTER TABLE db.person_program
    ADD CONSTRAINT fk_person_program_id_ref_person_id
    FOREIGN KEY (person_id) REFERENCES db.person(person_id);

In both tables, person_id is the primary key. My sense after reading the documentation (which unfortunately focuses heavily on autocreated databases and not so much on connecting to existing ones) is that the constraint names/indexes are perhaps the problem.

I'm attempting to use a OneToOne or has_one relationship to model the first constraint, and a ManyToOne or belongs_to relationship for the second. In each case I've tried, I've gotten errors, including instructions to use the primaryjoin keyword argument, but this hasn't helped either.

Relatedly, I've got a similar situation with a OneToMany or has_many relationship, defined on the database with the following SQL code:

ALTER TABLE db.person_by_age
    ADD CONSTRAINT fk_person_by_age_id_ref_person_id
    FOREIGN KEY (person_id) REFERENCES db.person(person_id);

Here, person_id is the primary key of the person table, but not of the person_by_age table. This constraint gives similar errors in Elixir to the above.

Has anyone done this before? How can I specify these relationships to use the existing foreign key constraints and indexes already in the database?

Any help is appreciated!

Ben
  • 51,770
  • 36
  • 127
  • 149
CodeAndData
  • 11
  • 5
  • 1) What is the error you are getting? – van Mar 19 '12 at 17:02
  • 2) Two tables that have PKs which are FKs to each other does not smell good in pure SQL as well. Is any of the tables using `autoincrement`? – van Mar 19 '12 at 17:03
  • @van: No, these tables have set PKs -- unfortunately, I can't change the data set too much. For the first scenario, I could combine the tables, but the data in each is qualitatively different data and the latter is queried much less than the former, so I kept them apart for that reason. In the current state: – CodeAndData Mar 21 '12 at 13:50
  • @van: using: `has_one('program', of_kind='PersonProgram', primaryjoin='Person.person_id==PersonProgram.person_id', inverse='person')` I get: `ArgumentError: Column-based expression object expected for argument 'primaryjoin'; got: 'Person.person_id==PersonProgram.person_id', type ` I'll need to recreate prior errors. – CodeAndData Mar 21 '12 at 13:55

0 Answers0