Submitting a form with SQLAlchemy throws 'list' object has no attribute

Question

I am new both to Flask and SQLAlchemy and to coding, so have patience please.

What I am trying to do is to send data through a form to my database.
Worked fine until I wanted two more tables with relationship one to many, as in a plant can accumulate many elements and a plant can have many properties and flask throws error AttributeError: 'list' object has no attribute '_sa_instance_state'.
The form is :

from flask_wtf import FlaskForm
from wtforms.ext.sqlalchemy.fields import  QuerySelectMultipleField
from wtforms import StringField, PasswordField, SubmitField,BooleanField, TextAreaField

#Query for Dynamic Nutrient Accumulator Model
def enabled_dna():
    return DNA.query.all()
#Query for Nitrogen Fixers Nursing Model

def enabled_nfn():
    return NFN.query.all()

class NewPlantForm(FlaskForm):
    common_name = StringField('Common Name', render_kw={"placeholder": "Common name"},
                          validators=[DataRequired(), Length(min=2, max=40)])
    botanical_name = StringField('Botanical Name', render_kw={"placeholder": "Botanical name"},
                             validators=[DataRequired(), Length(min=2, max=80)])
    short_description = TextAreaField('Short Description', render_kw={"placeholder": "Please add a short description"},
                                  validators=[DataRequired()])
    medicinal = TextAreaField('Medicinal Use', render_kw={"placeholder": "Medicinal use"},
                        validators=[DataRequired()])
    dna = QuerySelectMultipleField('Select Element',query_factory=enabled_dna,allow_blank=True)
    nfn = QuerySelectMultipleField('Select Property',query_factory=enabled_nfn,allow_blank=True)
    submit = SubmitField('Add plant')

The models.py looks like this :

#Plants Table
class Plants(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    common_name = db.Column(db.String(40), nullable=False)
    botanical_name = db.Column(db.String(80), unique=True, nullable=False)
    short_description = db.Column(db.Text, nullable=False)
    medicinal = db.Column(db.Text, nullable=False)
    image_file = db.Column(db.String(20), default='default_plant_pic.jpg')
    date_added = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    dna_id = db.Column(db.Integer, db.ForeignKey('DNA.id'))
    dna = db.relationship('DNA', backref=db.backref('plant_dna', lazy='dynamic'))  # Dynamic_Nutrient_Accumulated
    nfn_id = db.Column(db.Integer, db.ForeignKey('NFN.id'))
    nfn = db.relationship('NFN', backref=db.backref('plant_nfn', lazy='dynamic'))  # Nitrogen_Fixers_Nursing

    def __repr__(self):
        return f"Plants('{self.common_name}', '{self.botanical_name}', '{self.short_description}'," \
        f" '{self.medicinal}', '{self.dna}', '{self.nfn}' )"

#Dynamic_Nutrient_Accumulated
class DNA(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    element = db.Column(db.String(15))

    def __repr__(self):
        return '[ {}]'.format(self.element)
#Nitrogen_Fixers_Nursing
class NFN(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    plant_extra = db.Column(db.String(40))

    def __repr__(self):
        return '[ {}]'.format(self.plant_extra)

The route and form worked fine with the form containing fields only for one table. However, it doesn't work now while I added second and third fields containing data from other tables(form.dna.data and form.nfn.data).

My route for New Plant is :

@app.route("/plants/new/", methods=['GET', 'POST'])
@login_required# User must be logged in to create a new plant
def new_plant():
    form = NewPlantForm()

    if form.validate_on_submit():
        new_plant = Plants(common_name=form.common_name.data,
                           botanical_name=form.botanical_name.data,
                           short_description=form.short_description.data,
                           medicinal=form.medicinal.data,
                           dna=form.dna.data,
                           nfn=form.nfn.data,
                           author=current_user)
        db.session.add(new_plant)
        db.session.commit()
        flash('Thank you ! You have successfully added a plant '
              'to the database!', 'success')

        return redirect(url_for('plants'))
    image_file = url_for('static', filename='img/plants/default_plant_pic.jpg')

    return render_template('new_plant.html', title='Add new plant',
                       image_file=image_file, form=form)

And route where to render the information of plants is :

@app.route("/plants")
def plants():
    plants = Plants.query.all()
    return render_template('plants.html', title= 'Plants Database', plants=plants)

I have tried working with this locally from terminal and it works, but I don't know what I'm missing or if the relationship model is wrong to get it working from the flask app.

Thanks in advance for patience and help.

Update

After trial and error, it now seems to work all of it (add a plant with the selected fields to the DB, render the plant data correctly into the template, the plant is added correctly to the DB-viewing with DB Browser for SQLite) after I've changed QuerySelectMultipleField to QuerySelectField. However, my point was to be able to select and render multiple choices.

Another thing that I have noticed is that when using QuerySelectField, the template renders correctly a dropdown, but when trying to use QuerySelectMultipleField, it renders just a list with elements, no dropdown.

Here is the small part of the template with the select field form.dna and form.nfn:

<div class="form-group">
  {{ form.dna(class="form-control form-control-sm") }}
</div>
<div class="form-group">
      {{ form.nfn(class="form-control form-control-sm")}}
</div>

I am using Bootstrap. Could this thing be related to the template formatting not correctly wrote for the multiple select? Thanks.

Update2

I managed to get QuerySelectMultipleField to work by looping through the form data like this :

 @app.route("/plants/new/", methods=['GET', 'POST'])
 @login_required# User must be logged in to create a new plant
 def new_plant():
     form = NewPlantForm()

    if form.validate_on_submit():
       new_plant = Plants(common_name = form.common_name.data, botanical_name = form.botanical_name.data,
                       short_description = form.short_description.data, medicinal=form.medicinal.data,
                       author=current_user)
      **for dna_element in form.dna.data:
          new_plant.dna = dna_element
      for nfn_element in form.nfn.data:
          new_plant.nfn = nfn_element**

    db.session.add(new_plant)
    db.session.commit()
    flash(f'Thank you ! You have successfully added a plant to the database!', 'success')
    return redirect(url_for('plants'))
image_file = url_for('static', filename='img/plants/default_plant_pic.jpg')
return render_template('new_plant.html', title='Add new plant',
                       image_file=image_file, form=form)

I don't get anymore the error AttributeError: 'list' object has no attribute '_sa_instance_state' and the plant is successful added to the database, however when I'm looking in the database, I can see that only one option was selected, not multiple choices. From what I've read here : Flask App Using WTForms with SelectMultipleField , I was supposed to use form.something.data to get a list of items which I did, but still doesn't work and I only get one item. Please help. Thanks !

Update 3 and solving the problem

After implementing sleblanc's response, I now have the following code that works with the form and displays correctly: **models.py : **

plants_dna_table = db.Table(
'plants_dna',
db.Column('plants_id', db.Integer, db.ForeignKey('plants.id'), nullable=False),
db.Column('dna_id', db.Integer, db.ForeignKey('DNA.id'), nullable=False),
db.UniqueConstraint('plants_id', 'dna_id'))

plants_nfn_table = db.Table(
'plants_nfn',
db.Column('plants_id', db.Integer, db.ForeignKey('plants.id'), nullable=False),
db.Column('nfn_id', db.Integer, db.ForeignKey('NFN.id'), nullable=False),
db.UniqueConstraint('plants_id', 'nfn_id'))

#Plants Table
class Plants(db.Model):
id = db.Column(db.Integer, primary_key=True)
common_name = db.Column(db.String(40), nullable=False)
botanical_name = db.Column(db.String(80), unique=True, nullable=False)
short_description = db.Column(db.Text, nullable=False)
medicinal = db.Column(db.Text, nullable=False)
image_file = db.Column(db.String(20), default='default_plant_pic.jpg')
date_added = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
dna = db.relationship('DNA', secondary = plants_dna_table)  # Dynamic_Nutrient_Accumulated
nfn = db.relationship('NFN', secondary = plants_nfn_table)  # Nitrogen_Fixers_Nursing

def __repr__(self):
    return f"Plants('{self.common_name}', '{self.botanical_name}', '{self.short_description}'," \
        f" '{self.medicinal}', '{self.dna}', '{self.nfn}' )"

#Dynamic_Nutrient_Accumulated
class DNA(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   element = db.Column(db.String(15))

   def __repr__(self):
       return '{}'.format(self.element)
#Nitrogen_Fixers_Nursing
class NFN(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   plant_extra = db.Column(db.String(40))

   def __repr__(self):
   return '{}'.format(self.plant_extra)

The db.ForeignKey('DNA.id') displayed with capital letters does the job and will not get the error for not finding the table DNA.

**routes.py : **

#Route for users to add a plant to the database
@app.route("/plants/new/", methods=['GET', 'POST'])
@login_required# User must be logged in to create a new plant
def new_plant():
form = NewPlantForm()

if form.validate_on_submit():
    new_plant = Plants(common_name = form.common_name.data, botanical_name = form.botanical_name.data,
                       short_description = form.short_description.data, medicinal=form.medicinal.data,
                       author=current_user)
    for dna_element in form.dna.data:
        new_plant.dna.append(dna_element)

    for nfn_element in form.nfn.data:
        new_plant.nfn.append(nfn_element)

    print(new_plant)
    db.session.add(new_plant)
    db.session.commit()
    flash(f'Thank you ! You have successfully added a plant to the database!', 'success')
    return redirect(url_for('plants'))
image_file = url_for('static', filename='img/plants/default_plant_pic.jpg')
return render_template('new_plant.html', title='Add new plant',
                       image_file=image_file, form=form)

Thanks @sleblanc!

Answer 1

You have modeled your relationships in reverse.

You have a One-To-Many relationship from your Plants to your DNA and NFN if I am not mistaken, meaning that one 'Plants' object will have several DNA, and several NFN. As defined, your 'Plants' model only has a single field for DNA and a single field for NFN, meaning that there is no way to store the association in the database.

You will need to modify your Plants model to represent the relationship. You must first determine if a 'DNA' or a 'NFN' can be shared by multiple 'Plants', or if they are unique for each 'Plants' instance, as it involves a different schema.

class Plants(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...

# second option, exclusive relationship
class DNA(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...
    plants_id = db.Column(db.ForeignKey(Plants.id), nullable=False)
    ...

class NFN(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...
    plants_id = db.Column(db.ForeignKey(Plants.id), nullable=False)
    ...


# first option, non-exclusive relationship
class Plants(db.Model):
    ...
    dna = relationship("DNA", secondary=plants_dna_table)
    nfn = relationship("NFN", secondary=plants_nfn_table) 


plants_dna_table = db.Table(
    'plants_dna',
    db.Column('plants_id', db.ForeignKey('plants.id'), nullable=False),
    db.Column('dna_id', db.ForeignKey('dna.id'), nullable=False),
    db.UniqueConstraint('plants_id', 'dna_id'),
    )

plants_nfn_table = db.Table(
    'plants_nfn',
    db.Column('plants_id', db.ForeignKey('plants.id'), nullable=False),
    db.Column('nfn_id', db.ForeignKey('nfn.id'), nullable=False),
    db.UniqueConstraint('plants_id', 'nfn_id'),
    )

After having modified the Plants model and setup a relationship between the two, keep in mind that the .dna and .nfn attributes will have a list type. You can add a relationship to the instances by appending to the list.

I highly recommend you use FlaskForm.populate_obj:

@app.route("/plants/new/", methods=['GET', 'POST'])
@login_required# User must be logged in to create a new plant
def new_plant():
obj = Plants()
form = NewPlantForm()

if form.validate_on_submit():
    form.populate_obj(obj)

    print(obj) # debugging
    db.session.add(obj)
    db.session.commit()
    flash('Thank you ! You have successfully added '
          'a plant to the database!', 'success')
    return redirect(url_for('plants'))

image_file = url_for('static', filename='img/plants/default_plant_pic.jpg')
return render_template('new_plant.html', title='Add new plant',
                   image_file=image_file, form=form)

Also, it lets you combine a future Plant update view by adding just a few more lines:

@app.route('/plant/<id>/edit', methods=('GET', 'POST',))
@app.route('/plant/new', methods=('GET', 'POST'))
def create_plant(id=None):
    if id is not None:
        obj = Plant.query.get(id)
    else:
        obj = Plant()

    form = PlantForm(request.form, obj=obj)

    if form.validate_on_submit():
        form.populate_obj(obj)

        db.session.add(obj)
        db.session.commit()
        flash('Thank you ! You have successfully added '
              'a plant to the database!', 'success')
        return redirect(url_for('get_plant', id=obj.id))

    else:
        image_file = url_for(
            'static', 
            filename='img/plants/default_plant_pic.jpg')
        return render_template('plant.html', title='Add new plant',
                               image_file=image_file,
                               form=form, obj=obj)