Flask-SqlAlchemy relationship error

Question

Here is simple model I have

  class Record(db.Model):
      __tablename__ = 'reg_records'
      request_id = db.Column(db.Integer, primary_key = True)
      title = db.Column(NVARCHAR())

  class Field(db.Model):
      __tablename__ = 'reg_fields'
      request_id = db.Column(db.Integer, primary_key= True) 
      name = db.Column(NVARCHAR(), primary_key = True)

Now I want to map releated fields to record. I'm following flask-sqlalchemy specs and getting this

  class Field(db.Model):
      __tablename__ = 'reg_fields'
      request_id = db.Column(db.Integer, ForeignKey('record.request_id'), primary_key= True) 
      name = db.Column(NVARCHAR(), primary_key = True)

  class Record(db.Model):
      __tablename__ = 'reg_records'
      request_id = db.Column(db.Integer, primary_key = True)
      title = db.Column(NVARCHAR())
      fields = db.relationship(Field, backref = 'record')

However when I try to use this model I'm getting following error

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

Adding primaryjoin to the relationship declaration like below

    fields = db.relationship(Field, backref = 'record', primaryjoin='Record.request_id==Field.request_id')

Doesnt solve the problem, but instead gives new exception

    sqlalchemy.exc.NoReferencedTableError
    NoReferencedTableError: Foreign key associated with column 'reg_fields.request_id' could not find table 'record' with which to generate a foreign key to target column 'request_id'

If I replace class names with table names in primaryjoin value,

    fields = db.relationship(Field, backref = 'record', primaryjoin='reg_records.request_id==reg_fields.request_id')

I'm getting one another exception

   sqlalchemy.exc.InvalidRequestError
   InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers.  Original exception was: 'Table' object has no attribute 'request_id'

Overall, what is right way setting up one-to-many relationship in flask-sqlalchemy.

Iron Fist · Accepted Answer · 2017-01-14 11:32:02Z

4

It is not very clear what you are trying to do. I am assuming that you are trying to make a one-to-many relationship between Record and Field (one Record can have zero or many Fields). I am also assuming that you want to have only one Field named name per record. The following models set up the tables and relationships as I believe that you want them:

from sqlalchemy.schema import UniqueConstraint

class Record(db.Model):
    __tablename__ = 'reg_records'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(NVARCHAR())


class Field(db.Model):
    __tablename__ = 'reg_fields'
    __table_args__ = (
        UniqueConstraint('record_id', 'name', name='record_id_name_uix'),
    )

    id = db.Column(db.Integer, primary_key=True)
    record_id = db.Column(db.Integer, ForeignKey(Record.id))
    name = db.Column(NVARCHAR())

    record = db.relationship(Record, backref='fields')

You can now do things like creating new Records and Fields:

record = Record(title='a record')
record.fields.append(Field(name='field1'))
record.fields.append(Field(name='field2'))
db.session.add(record)
db.session.commit()

However, try to create two Fields with the same name for the same Record, you will get an IntegrityError when you try to commit them:

record = Record(title='another record')
record.fields.append(Field(name='same_name'))
record.fields.append(Field(name='same_name'))
db.session.add(record)
db.session.commit()

Lastly, please try to be more careful with how your Python code is formatted, it is very hard to read as it stands.

edited Jan 14, 2017 at 11:32

Iron Fist

11k2 gold badges21 silver badges36 bronze badges

answered Sep 7, 2013 at 20:38

clj

4812 silver badges8 bronze badges

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

2 Comments

vittore Over a year ago

You are right, I want to have one-to-many relationship between Record and Field, so Record has multiple Fields and name is unique within one record. I'm mapping it to view so there is no real primary key, that is why both reqest_id and name were set to primary_key.

vittore Over a year ago

Anyway, moving relatioship to the Field class did the trick , however all examples were showing it the way I did it initially.

Collectives™ on Stack Overflow

Flask-SqlAlchemy relationship error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related