Flask-Alchemy relationship error

I am new in flask and alchemy and decided to learn by working on a large project using flask. When I started creating relationships between classes. Each of the classes is in separate file and folder. the Logic I want to implement is each controlaccount can have only one project assigned to it while a project can be assigned to multiple controlaccounts i.e. one-to-mant relationship.

I keep getting the following error message sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'project_id' in 'field list'") [SQL: 'INSERT INTO controlaccounts (code, name, budget, PMB_start, PMB_finish, PMU_start, PMU_finish, parent_id, project_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('Root', 'Root', 1.0, datetime.date(2017, 12, 1), datetime.date(2017, 12, 31), datetime.date(2018, 12, 1), datetime.date(2019, 2, 27), 1, 1)]

My code in controlaccounts/models.py

class ControlAccount(db.Model):
    __tablename__='controlaccounts'
    id = db.Column(db.Integer, primary_key =True)
    code = db.Column(db.String(80))
    name = db.Column(db.String(80))
    budget = db.Column(db.Float)
    PMB_start = db.Column(db.Date)
    PMB_finish = db.Column(db.Date)
    PMU_start = db.Column(db.Date)
    PMU_finish = db.Column(db.Date)
    parent_id = db.Column(db.Integer)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))


    def __init__(self, code, name, budget, PMB_start, PMB_finish, PMU_start, PMU_finish, parent_id, project):
        self.code = code
        self.name = name
        self.parent_id = parent_id
        #self.accounttype_id = accounttype.id
        self.budget = budget
        self.PMB_start = PMB_start
        self.PMB_finish = PMB_finish
        self.PMU_start = PMU_start
        self.PMU_finish = PMU_finish
        self.project_id = project.id


    def __repr__(self):
        return self.name

the project/models file code is

class Project(db.Model):


__tablename__ ="projects"
    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(80))
    name = db.Column(db.String(80))
    owner = db.Column(db.Integer, db.ForeignKey('users.id'))
    description = db.Column(db.Text)
    start = db.Column(db.DateTime)
    finish = db.Column(db.DateTime)
    status = db.Column(db.Boolean)
    project = db.relationship('ControlAccount', backref='projects', lazy='dynamic',primaryjoin="Project.id == ControlAccount.project_id")

    def __init__(self, code, name, description, owner, start, finish, status):
        self.code = code
        self.name = name
        self.owner = owner
        self.description = description
        self.start = start
        self.finish = finish
        self.status = status

The form for controlaccount code is

class ControlAccountForm(Form):

    def get_projects():
        return Project.query.all()

    code = StringField('Code', [validators.Required()])
    name = StringField('Type Name', [validators.Required()])
    #accounttype = QuerySelectField('Account Type', query_factory= get_account_types)
    #curve_id = QuerySelectField('Spread Profile', query_factory= get_spread_profile)
    budget = FloatField('Budget', [validators.Required()])
    PMB_start = DateField('Planned Start', [validators.Required()])
    PMB_finish = DateField('Planned Finish', [validators.Required()])
    PMU_start = DateField('Anticipated Start')
    PMU_finish = DateField('Anticipated Finish')
    #parent_id = QuerySelectField('Parent Accounts', query_factory= get_control_accounts)
    parent_id = IntegerField('Parent ID')
    project_id = QuerySelectField('Project', query_factory= get_projects)

and last is the views code

@app.route('/newcontrolaccount', methods=['POST', 'GET'])
def newcontrolaccount():
    form = ControlAccountForm()
    if request.method == "POST" and form.validate():
        #accounttype = form.accounttype.data
        controlaccount = ControlAccount (code= form.code.data, 
                            name= form.name.data,
                            #accounttype = form.accounttype.data,
                            budget = form.budget.data,
                            PMB_start = form.PMB_start.data,
                            PMB_finish = form.PMB_finish.data,
                            PMU_start = form.PMU_start.data,
                            PMU_finish = form.PMU_finish.data,
                            parent_id = form.parent_id.data,
                            #curve_id = form.curve_id.data,
                            project = form.project_id.data
                            )
        db.session.add(controlaccount)
        db.session.commit()
        return redirect(url_for('ca_added'))
    return render_template('controlaccounts/newaccount.html', form=form, action='new')

Solution

As the error says the project_id field is missing in your database. Have a look with a database viewer if it has been created. Otherwise delete the database and recreate it with db.create_all().

Also the project field in Project class should be removed. This doesn't make sense. If you want to get a backref to the related control accounts just add a backref in your ControlAccount class. See http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-one for details.

class ControlAccount(db.Model):
    __tablename__='controlaccounts'
    # ...
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))
    project = relationship("Project", backref="control_account")