flask sqlalchemy.exc.NoForeignKeysError NoForeignKeysError: Could not determine join condition

Question

Here is the exception trace in the debugger

sqlalchemy.exc.NoForeignKeysError

NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Department.employees - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

This is my models.py, I'm using reflection with an existing schema.

    from sqlalchemy import Table, Column, Integer, ForeignKey, String, DateTime, Date, Time
    from sqlalchemy.orm import relationship, backref

    #make sure we get the right base
    from database import ProbeDB_Base, DB

    class Location(ProbeDB_Base):
        __tablename__ = u'Location'
        __table_args__ = {u'schema': u'probedb'}

        LocationID = Column(Integer, primary_key=True)
        LocationName = Column(String(100))
        controllers = relationship("Controller", backref="Location")

    class Controller(ProbeDB_Base):
        __tablename__ = u'Controller'
        __table_args__ = {u'schema': 'probedb'}

        ControllerID = Column(Integer, primary_key=True)
        ControllerName = Column(String(100))
        ControllerNo = Column(Integer)
        SlaveNo = Column(Integer)
        IPAddress = Column(String(20))
        Port = Column(Integer)
        CommunicationType = Column(Integer)
        Enable = Column(Integer)
        ResponseTime = Column(Integer)
        LocationID = Column(Integer, ForeignKey('Location.LocationID'), index=True)
        #LocationID = relationship(Location, primaryjoin='Controller.LocationID == DB.Location.LocationID')
        MaxChannels = Column(Integer)
        #Location_id = relationship(u'Location')
        transactions = relationship("Transaction", backref="Controller")

    class Department(ProbeDB_Base):
        __tablename__ = u'Department'
        __table_args__ = {u'schema': 'probedb'}

        DepartmentID = Column(Integer, primary_key=True)
        Name = Column(String(100))
        employees = relationship("Employee", backref="Department")

    class Designation(ProbeDB_Base):
        __tablename__ = u'Designation'
        __table_args__ = {u'schema': 'probedb'}

        DesignationID = Column(Integer, primary_key=True)
        Name = Column(String(100))
        employees = relationship("Employee", backref="Designation")

    class Employee(ProbeDB_Base):
        __tablename__ = u'Employee'
        __table_args__ = {u'schema': 'probedb'}

        EmployeeID = Column(Integer, primary_key=True)
        EmployeeCode = Column(String(15), index=True)
        LocationID = Column(Integer)
        Name = Column(String(50))
        CardNo = Column(Integer)
        DepartmentID = Column(Integer, ForeignKey('Department.DepartmentID'), index=True)
        #DepartmentID = relationship(Department, primaryjoin='Employee.DepartmentID == DB.Department.DepartmentID')
        DesignationID = Column(Integer, ForeignKey('Designation.DesignationID'), index=True)
        #DesignationID = relationship(Designation, primaryjoin='Employee.DesignationID == DB.Designation.DesignationID')
        EnrollDate = Column(DateTime)
        ExpDate = Column(DateTime)
        #Department_id = relationship(u'Department')
        #Designation_id = relationship(u'Designation')

    class Event(ProbeDB_Base):
        __tablename__ = u'Event'
        __table_args__ = {u'schema': 'probedb'}

        EventID = Column(Integer, primary_key=True)
        Name = Column(String(100))

        transactions = relationship("Transaction", backref="Event")

    class Transaction(ProbeDB_Base):
        __tablename__ = u'Transaction'
        __table_args__ = {u'schema': 'probedb'}

        TransactionID = Column(Integer, primary_key=True)
        DateTime = Column(DateTime)
        Date = Column(Date)
        Time = Column(Time)
        CardNo = Column(Integer)
        Channel = Column(Integer)
        EventID = Column(Integer, ForeignKey('Event.EventID'), index=True)
        #EventID = relationship(Event, primaryjoin='Transaction.EventID == DB.Event.EventID')
        AccessType = Column(String(4))
        ReadPtr = Column(Integer)
        ControllerID = Column(Integer, ForeignKey('Controller.ControllerID'), index=True)
        #ControllerID = relationship("Controller", primaryjoin='Transaction.ControllerID == Controller.ControllerID')
        IPAddress = Column(String(20))
        #Controller_id = relationship(u'Controller')
        #Event_id = relationship(u'Event')

    class User(ProbeDB_Base):
        __tablename__ = u'users'
        __table_args__ = {u'schema': 'probedb'}

        uid = Column(Integer, primary_key=True)
        firstname = Column(String(100), nullable=False)
        lastname = Column(String(100), nullable=False)
        email = Column(String(120), nullable=False, index=True)
        pwdhash = Column(String(100), nullable=False)

I did specify a foreign key for the transaction table and a backref in the controller table, but not able to figure out where I am going wrong

Here is the create.sql for the table

    /*
    ProbeDB 
    This is the setup SQL for probedb
    */

    CREATE TABLE IF NOT EXISTS Location(
    LocationID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    LocationName varchar(100)
    )
    ;
    CREATE TABLE IF NOT EXISTS Controller(
    ControllerID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ControllerName varchar(100),
    ControllerNo int,
    SlaveNo int,
    CONSTRAINT SlaveNo_Ck CHECK (SlaveNo BETWEEN 1 AND 128),
    IPAddress varchar(20),
    Port int,
    CommunicationType int,
    CONSTRAINT CommunicationType_Ck CHECK (CommunicationType BETWEEN 1 AND 2),
    /* 
    1 TCP
    2 UDP
    */
    Enable int, 
    CONSTRAINT Enable_Ck CHECK (Enable BETWEEN 0 AND 1),
    /* 
    0 Enable
    1 Disable
    */
    ResponseTime int,
    LocationID int,
    CONSTRAINT fk_LocationReference FOREIGN KEY (LocationID) REFERENCES Location(LocationID),
    MaxChannels int
    )
    ;
    CREATE TABLE IF NOT EXISTS Department(
    DepartmentID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100)
    )
    ;
    CREATE TABLE IF NOT EXISTS Designation(
    DesignationID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100)
    )
    ;
    CREATE TABLE IF NOT EXISTS Employee(
    EmployeeID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    EmployeeCode varchar(15) UNIQUE,
    LocationID int,
    Name varchar(50),
    CardNo int,
    DepartmentID int,
    CONSTRAINT fk_DepartmentReference FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID),
    DesignationID int,
    CONSTRAINT fk_DesignationReference FOREIGN KEY (DesignationID) REFERENCES Designation(DesignationID),
    EnrollDate datetime,
    ExpDate datetime
    )
    ;
    CREATE TABLE IF NOT EXISTS Event(
    EventID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100)
    )
    ;
    CREATE TABLE IF NOT EXISTS Transaction(
    TransactionID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    DateTime datetime,
    Date date,
    Time time,
    CardNo int,
    Channel int,
    EventID int,
    CONSTRAINT fk_EventReference FOREIGN KEY (EventID) REFERENCES Event(EventID),
    AccessType varchar(4),
    ReadPtr int,
    ControllerID int,
    CONSTRAINT fk_ControllerReference FOREIGN KEY (ControllerID) REFERENCES Controller(ControllerID),
    IPAddress varchar(20)
    )
    ;
    CREATE TABLE users (
    uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE,
    pwdhash VARCHAR(100) NOT NULL
    );
    /*
    END
    */

What am I doing wrong?

EDITED After modifying per Miguel

    from sqlalchemy import Table, Column, Integer, ForeignKey, String, DateTime, Date, Time
    from sqlalchemy.orm import relationship, backref

    #make sure we get the right base
    from database import ProbeDB_Base, DB

    class Location(ProbeDB_Base):
        __tablename__ = u'Location'
        __table_args__ = {u'schema': u'probedb'}

        LocationID = Column(Integer, primary_key=True)
        LocationName = Column(String(100))
        controllers = relationship("Controller", backref="Location")

    class Controller(ProbeDB_Base):
        __tablename__ = u'Controller'
        __table_args__ = {u'schema': 'probedb'}

        ControllerID = Column(Integer, primary_key=True)
        ControllerName = Column(String(100))
        ControllerNo = Column(Integer)
        SlaveNo = Column(Integer)
        IPAddress = Column(String(20))
        Port = Column(Integer)
        CommunicationType = Column(Integer)
        Enable = Column(Integer)
        ResponseTime = Column(Integer)
        LocationID = Column(Integer, ForeignKey('Location'), index=True)
        #LocationID = relationship(Location, primaryjoin='Controller.LocationID == DB.Location.LocationID')
        MaxChannels = Column(Integer)
        #Location_id = relationship(u'Location')
        transactions = relationship("Transaction", backref="Controller")

    class Department(ProbeDB_Base):
        __tablename__ = u'Department'
        __table_args__ = {u'schema': 'probedb'}

        DepartmentID = Column(Integer, primary_key=True)
        Name = Column(String(100))
        employees = relationship("Employee", backref="Department")

    class Designation(ProbeDB_Base):
        __tablename__ = u'Designation'
        __table_args__ = {u'schema': 'probedb'}

        DesignationID = Column(Integer, primary_key=True)
        Name = Column(String(100))
        employees = relationship("Employee", backref="Designation")

    class Employee(ProbeDB_Base):
        __tablename__ = u'Employee'
        __table_args__ = {u'schema': 'probedb'}

        EmployeeID = Column(Integer, primary_key=True)
        EmployeeCode = Column(String(15), index=True)
        LocationID = Column(Integer)
        Name = Column(String(50))
        CardNo = Column(Integer)
        DepartmentID = Column(Integer, ForeignKey('Department'), index=True)
        #DepartmentID = relationship(Department, primaryjoin='Employee.DepartmentID == DB.Department.DepartmentID')
        DesignationID = Column(Integer, ForeignKey('Designation'), index=True)
        #DesignationID = relationship(Designation, primaryjoin='Employee.DesignationID == DB.Designation.DesignationID')
        EnrollDate = Column(DateTime)
        ExpDate = Column(DateTime)
        #Department_id = relationship(u'Department')
        #Designation_id = relationship(u'Designation')

    class Event(ProbeDB_Base):
        __tablename__ = u'Event'
        __table_args__ = {u'schema': 'probedb'}

        EventID = Column(Integer, primary_key=True)
        Name = Column(String(100))

        transactions = relationship("Transaction", backref="Event")

    class Transaction(ProbeDB_Base):
        __tablename__ = u'Transaction'
        __table_args__ = {u'schema': 'probedb'}

        TransactionID = Column(Integer, primary_key=True)
        DateTime = Column(DateTime)
        Date = Column(Date)
        Time = Column(Time)
        CardNo = Column(Integer)
        Channel = Column(Integer)
        EventID = Column(Integer, ForeignKey('Event'), index=True)
        #EventID = relationship(Event, primaryjoin='Transaction.EventID == DB.Event.EventID')
        AccessType = Column(String(4))
        ReadPtr = Column(Integer)
        ControllerID = Column(Integer, ForeignKey('Controller'), index=True)
        #ControllerID = relationship("Controller", primaryjoin='Transaction.ControllerID == Controller.ControllerID')
        IPAddress = Column(String(20))
        #Controller_id = relationship(u'Controller')
        #Event_id = relationship(u'Event')

    class User(ProbeDB_Base):
        __tablename__ = u'users'
        __table_args__ = {u'schema': 'probedb'}

        uid = Column(Integer, primary_key=True)
        firstname = Column(String(100), nullable=False)
        lastname = Column(String(100), nullable=False)
        email = Column(String(120), nullable=False, index=True)
        pwdhash = Column(String(100), nullable=False)

I get this exception

sqlalchemy.exc.NoForeignKeysError

NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Designation.employees - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Answer 1

This should teach me to RTFM properly flask enforces CamelCase in database tablenames

Scratched my head for two days before I figured it out from the manual. Someone should make it bold or make it stand out or something on that page.

    from sqlalchemy import Table, Column, Integer, ForeignKey, String, DateTime, Date, Time
    from sqlalchemy.orm import relationship, backref
    from database import ProbeDB_Base, DB

    #make sure your models are setup right for reflection of an existing schema
    #convert all CamelCase table names - if you SQL table name is MyName, then change it to my_name
    #make sure you add backrefs and primaryjoin relationships for each ForeignKey constraint
    #make sure you escape all ' like this - \' in your relationship statements

    class Location(ProbeDB_Base):
        __tablename__ = u'location'
        __table_args__ = {u'schema': u'probedb'}

        location_id = Column(Integer, primary_key=True)
        Locationname = Column(String(100))
        controllers = relationship("u'controller'", backref="u'location'")

    class Controller(ProbeDB_Base):
        __tablename__ = u'lontroller'
        __table_args__ = {u'schema': 'probedb'}

        controller_id = Column(Integer, primary_key=True)
        controller_name = Column(String(100))
        controller_no = Column(Integer)
        slave_no = Column(Integer)
        ip_address = Column(String(20))
        port = Column(Integer)
        communicationType = Column(Integer)
        enable = Column(Integer)
        responseTime = Column(Integer)
        #location_id = Column(Integer, ForeignKey('location.location_id'), index=True)
        location_id = relationship(Location, primaryjoin='u\'controller\'.location_id == u\'location\'.location_id')
        max_channels = Column(Integer)
        #Location_id = relationship(u'Location')
        transactions = relationship("u'transaction'", backref="u'controller'")


    class Department(ProbeDB_Base):
        __tablename__ = u'department'
        __table_args__ = {u'schema': 'probedb'}

        department_id = Column(Integer, primary_key=True)
        name = Column(String(100))
        employees = relationship("u'employee'", backref="u'department'")

    class Designation(ProbeDB_Base):
        __tablename__ = u'designation'
        __table_args__ = {u'schema': 'probedb'}

        designation_id = Column(Integer, primary_key=True)
        name = Column(String(100))
        employees = relationship("u'employee'", backref="u'designation'")

    class Employee(ProbeDB_Base):
        __tablename__ = u'employee'
        __table_args__ = {u'schema': 'probedb'}

        employee_id = Column(Integer, primary_key=True)
        employee_code = Column(String(15), index=True)
        location_id = Column(Integer)
        name = Column(String(50))
        CardNo = Column(Integer)
        #department_id = Column(Integer, ForeignKey('Department.department_id'), index=True)
        department_id = relationship(u'department', primaryjoin='u\'employee\'.department_id == u\'department\'.department_id')
        #designation_id = Column(Integer, ForeignKey('Designation.designation_id'), index=True)
        designation_id = relationship(u'designation', primaryjoin='u\'employee\'.designation_id == u\'designation\'.designation_id')
        enroll_date = Column(DateTime)
        exp_date = Column(DateTime)
        #department_id = relationship(u'department')
        #designation_id = relationship(u'designation')

    class Event(ProbeDB_Base):
        __tablename__ = u'event'
        __table_args__ = {u'schema': 'probedb'}

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

        transactions = relationship("u'transaction'", backref="u'event'")

    class Transaction(ProbeDB_Base):
        __tablename__ = u'transaction'
        __table_args__ = {u'schema': 'probedb'}

        transaction_id = Column(Integer, primary_key=True)
        date_time = Column(DateTime)
        date = Column(Date)
        time = Column(Time)
        card_no = Column(Integer)
        channel = Column(Integer)
        #event_id = Column(Integer, ForeignKey('event.event_id'), index=True)
        event_id = relationship(u'event', primaryjoin='u\'transaction\'.event_id == u\'event\'.event_id')
        accessType = Column(String(4))
        read_ptr = Column(Integer)
        #controller_id = Column(Integer, ForeignKey(controller.controller_id'), index=True)
        controller_id = relationship(u'controller', primaryjoin='u\'transaction\'.controller_id == u\'controller\'.controller_id')
        ip_address = Column(String(20))
        #Controller_id = relationship(u'Controller')
        #Event_id = relationship(u'Event')

    class User(ProbeDB_Base):
        __tablename__ = u'users'
        __table_args__ = {u'schema': 'probedb'}

        uid = Column(Integer, primary_key=True)
        firstname = Column(String(100), nullable=False)
        lastname = Column(String(100), nullable=False)
        email = Column(String(120), nullable=False, index=True)
        pwdhash = Column(String(100), nullable=False)

This is the code that works, and if you used sqlacodegen to make your model.py initially like me, don't forget to escape ' to '\ if you want to use tablename