Using sqlalchemy to define relationships in MySQL

Question

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.

reptilicus · Accepted Answer · 2015-05-08 19:26:39Z

1

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

edited May 8, 2015 at 19:26

answered May 8, 2015 at 19:20

reptilicus

10.4k6 gold badges59 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using sqlalchemy to define relationships in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related