I am new to SQLAlchemy, I am trying to build a practice project using SQLAlchemy. I have created the database containing tables with the following relationship. Now my questions are :
- How to INSERT data into the tables as they are interdependent?
- Does this form a loop in database design?
Is the looping database design, a bad practice? How to resolve this if its a bad practice?
Department.manager_ssn ==> Employee.SSN
and
Employee.department_id ==> Department.deptid
and following is the current version of code creating this exact database.
# Department table
class Departments(Base):
__tablename__ = "Departments"
# Attricutes
Dname= Column(String(15), nullable=False)
Dnumber= Column(Integer, primary_key=True)
Mgr_SSN= Column(Integer, ForeignKey('Employees.ssn'), nullable=False)
employees = relationship("Employees")
# Employee table
class Employees(Base):
__tablename__ = "Employees"
# Attributes
Fname = Column(String(30), nullable=False)
Minit = Column(String(15), nullable=False)
Lname = Column(String(15), nullable=False)
SSN = Column(Integer, primary_key=True)
Bdate = Column(Date, nullable=False)
Address = Column(String(15), nullable=False)
Sex = Column(String(1), default='F', nullable=False)
Salary = Column(Integer, nullable=False)
Dno = Column(Integer, ForeignKey('Departments.Dnumber'), nullable=False)
departments = relationship("Departments")
Please provide the solution in SQLAlchemy only and not in flask-sqlalchemy or flask-migrate and I am using Python 3.6.
You can avoid such circular reference design altogether by
You can find the manager of the department using