CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Jason Drain
Jason Drain

Reputation: 85

Using sqlalchemy to define relationships in MySQL

I am in the process of working with sqlalchemy and MySQL to build a database. I am currently having trouble defining a specific relationship between two tables.

class Experiment_Type(Base):
    __tablename__='experiment_types'

    id = Column(Integer, primary_key=True)
    type = Column(String(100))


class Experiments(Base):
    __tablename__ = 'experiments'

    id = Column(Integer, primary_key=True)
    type = Column(String(100))
    sub_id = Column(Integer, ForeignKey('experiment_types.id'))

    experiments = relationship('Experiments',
    primaryjoin="and_(Experiment_Type.id == Experiments.sub_id,
    'Experiments.type' == 'Experiment_Type.type')",
    backref=backref('link'))

What I want to do is have values of sub_id in experiments match the id in experiment_types based on type (if an entry in experiment_types of type = 'type1' has id = 1, then an entry in experiments with type = 'type1' should have a sub_id = 1). I am not even sure if this is the best way to approach defining the relationship in this situation so any advice is welcome.

The current error message is this:

sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition '0 = 1' on relationship Experiments.experiments. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

Upvotes: 1

Views: 1047

Answers (1)

reptilicus
reptilicus

Reputation: 10397

The whole point of setting up relationships in relational dbs is to not have to duplicate data across tables. Just do something like this:

class ExperimentType(Base):
    __tablename__='experiment_types'

    id = Column(Integer, primary_key=True)
    name = Column(String(100))


class Experiments(Base):
    __tablename__ = 'experiments'

    id = Column(Integer, primary_key=True)
    description = Column(String(100))
    type_id = Column(Integer, ForeignKey('experiment_types.id'))

    type = relationship("ExperimentType")

Then, if you do need to display the experiment type stuff later, can access it with something like: 

exp = session.query(Experiment).first()
print exp.type.name

Upvotes: 1

Related Questions