i work on a new database for school-administration. i work with mysql for a long time now but i'm quite new to python and sqlalchemy. first i designed the (giant) database, then generated the classes using flask-sqlacodegen... The problem: i've two tables: person and staff. in person, all persons are stored, each dataset has a mod_user. in staff only company-staff are stored. a mod_user has to be staff...
This are my staff/person classes:
#connect_test.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://XXX:XXX@localhost/schooldb_model'
db = SQLAlchemy(app)
class TPerson(db.Model):
__tablename__ = 't_person'
idt_person = db.Column(db.Integer, primary_key=True, info='ID der Person')
salutation = db.Column(db.String(10), nullable=False, server_default=db.FetchedValue(),
info='Anrede - Geschlecht der Person ist daraus ersichtlich')
firstname = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Vorname')
lastname = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Nachname')
birthdate = db.Column(db.Date, info='Geburtsdatum - wird 1900-01-01 gesetzt wenn nicht bekannt')
birthplace = db.Column(db.String(45), info='Geburtsort')
cityzenship = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Staatsbürgerschaft')
familystatus = db.Column(db.String(30), nullable=False, server_default=db.FetchedValue())
dmtx_idt = db.Column(db.LargeBinary)
qr_idt = db.Column(db.LargeBinary)
mod_timestamp = db.Column(db.DateTime, nullable=False, index=True, server_default=db.FetchedValue(),
info='Timestamp of modification')
mod_user = db.Column(db.ForeignKey('t_staff.t_person_idt_person'), nullable=False, index=True)
t_staff = db.relationship('TStaff', primaryjoin='TPerson.mod_user == TStaff.t_person_idt_person',
backref='t_people')
#mod_user = db.Column(db.Integer, nullable=False, index=True, server_default=db.FetchedValue())
class TStaff(TPerson):
__tablename__ = 't_staff'
t_person_idt_person = db.Column(db.ForeignKey('t_person.idt_person'), primary_key=True, info='Person id')
begin = db.Column(db.Date, nullable=False, info='Ist Mitarbeitr seit.')
end = db.Column(db.Date, info='War Mitarbeiter bis.')
running the code from the python console with
>>> from connect_test import TPerson
results in:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 't_person' and 't_staff'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.
I know, in this special case i dont really need the mod_users foreign key constraint - deleting it and doing mod_user = db.Column(db.Integer, nullable=False, index=True, server_default=db.FetchedValue())
works fine.
But i've hundrets of other constraints in the rest of the database that i can not delete - so i hoped solving this easy? case will help me to understand the rest...
greetings, c.
@above_c_level: Thanks a lot! - it works. Just had to change the conditions. Here's the complete code:
class TPerson(db.Model):
__tablename__ = 't_person'
idt_person = db.Column(db.Integer, primary_key=True, info='ID der Person')
salutation = db.Column(db.String(10), nullable=False, server_default=db.FetchedValue(),
info='Anrede - Geschlecht der Person ist daraus ersichtlich')
firstname = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Vorname')
lastname = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Nachname')
birthdate = db.Column(db.Date, info='Geburtsdatum - wird 1900-01-01 gesetzt wenn nicht bekannt')
birthplace = db.Column(db.String(45), info='Geburtsort')
cityzenship = db.Column(db.String(45), nullable=False, server_default=db.FetchedValue(), info='Staatsbürgerschaft')
familystatus = db.Column(db.String(30), nullable=False, server_default=db.FetchedValue())
dmtx_idt = db.Column(db.LargeBinary)
qr_idt = db.Column(db.LargeBinary)
mod_timestamp = db.Column(db.DateTime, nullable=False, index=True, server_default=db.FetchedValue(),
info='Timestamp of modification')
mod_user = db.Column(db.ForeignKey('t_staff.t_person_idt_person'), nullable=False, index=True)
t_staff = db.relationship('TStaff', primaryjoin='TPerson.mod_user == TStaff.t_person_idt_person',
backref='people')
class TStaff(TPerson):
__tablename__ = 't_staff'
t_person_idt_person = db.Column(db.ForeignKey('t_person.idt_person'), primary_key=True, info='Person id')
begin = db.Column(db.Date, nullable=False, info='Ist Mitarbeitr seit.')
end = db.Column(db.Date, info='War Mitarbeiter bis.')
# person = db.relationship('TPerson', primaryjoin='TPerson.idt_person == TStaff.t_person_idt_person',
# backref='staff')
__mapper_args__ = {'polymorphic_identity': 'staff',
'inherit_condition': TPerson.idt_person == t_person_idt_person}