Flask-Alchemy relationship error

Question

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')

This is because of an indentation error you have made defining you `project_id` property in `ControlAccount` — MrLeeh, Feb 21 '17 at 06:09
The indentation is correct in my code; it is just a formatting thing in stakeoverflow. I have corrected it in the code enclosed. — Hassan Emam, Feb 21 '17 at 06:54
This was actually related to the issue. If the indent error is not only in the post but also in your project file it would result in exactly the error you wrote about. This is because then you define `project_id` outside of the class `ControlAccount` as indentation is crucial for code structuring in Python. — MrLeeh, Feb 21 '17 at 06:54
I agree with that. however, it is not the case in my situation. It will be great if you can help me with finding the root-cause. Thanks for your support — Hassan Emam, Feb 21 '17 at 06:56

score 0 · Accepted Answer · answered Feb 21 '17 at 07:32

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")

I had to drop and re-create the tables for it to work. Thanks — Hassan Emam, Feb 21 '17 at 15:12

Flask-Alchemy relationship error

1 Answers1