class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) name = Column(String(1000)) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) name = Column(String(1000)) class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) b_id = Column(Integer, ForeignKey('b.id')) name = Column(String(1000)) a_id = Column(Integer, ForeignKey('a.id')) b = relationship('B', backref=backref('cb'), foreign_keys = b_id) a = relationship('A', backref=backref('ca'), foreign_keys = a_id) connect = engine.connect() Base.metadata.create_all(bind=engine)
I have created classes to play with creating tables and adding data to a MySQL database with sqlalchemy. The problem I am facing right now is as follows.
If I want to add an entry to table c all I need is code like this.
c_ent = C(name = 'c1') a1 = A(name = 'a1') b1 = B(name = 'b1') a1.ca = [c_ent] b1.cb = [c_ent] list1 = [a1, b1] sess = create_session(name) for i in list1: sess.add(i) sess.commit() sess.close()
This all works the way I need it to and the foreign keys for b_id and a_id in the c entry match those of the corresponding ids in a and b.
However, suppose after committing to the database and closing the session I now decide to add another entry to c, 'c2'. I want this entry to have the same relationship to tables a and b as my first one (so that a_id and b_id are the same as for the entry named 'c1'). However, if I use code similar to the first entry, sqlalchemy will just create duplicate entries in table a and b with the names 'a1' and 'b1' having id numbers of 2 instead of 1. Then, my entry in table c will have the correct id numbers, but they will be linked to the duplicate entries.
I want to be able to add an entry to c however many times I choose and relate it to tables a and b without creating new entries in a and b each time. How can I avoid this redundancy?