A Complete Many-to-One Example Using Flask, WTForm, SQLAlchemy, and Jinja2

Question

Here is my HTML dropdown menu. The value is the primary key of the child table.

<select id="category" name="category">
   <option selected value="__None"></option>
   <option value="1">Category Number One</option>
   <option value="2">Category Number Two</option>
</select>

I need to update Post.category_id with the value integer 1 instead of "Category Number One". Here is my code.

# create new post
@app.route('/admin/post', methods=['GET', 'POST'])
@login_required # Required for Flask-Security
def create_post():
    form = PostForm()
    if form.validate_on_submit():
        post = Post(title=form.title.data,
                    body=form.body.data,
                    pub_date=form.pub_date.data,
                    cateogry_id=form.category.data)
        db.session.add(post)
        db.session.commit()
        flash('Your post has been published.')
        return redirect(url_for('admin'))
    posts = Post.query.all()
    return render_template('create_post.html', form=form, posts=posts)

I've tried making...

cateogry_id=form.category.data
cateogry_id=form.category.value

Now wouldn't that be nice!

Answer 1

I figured it out! Here is my solution. Hopefully this writeup will help out the next guy.

The solution is to let extension do the work for you! Here is a working example the WT Forms sqlalchemy extension using Flask, WTForm, SQLAlchemy, and Jinja2. In short, you don't need to worry about the child id because the extension takes care of it automagically. that means when your dealing with SQLAlchemy Parent and Child models in a one to many relationship you ONLY have to deal with the PARENT.

MODEL

First, make sure your model and relationships are correct. Notice in my example how the relationships are defined and that the model's init only has CATEGORY... NOT CATEGORY_ID. My mistake was thinking I would have the populate my model's CATEGORY_ID field. Nope. The extension does it for you. In fact, if you try to do it manually like I did it won't work at all....

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    url = db.Column(db.String(120))
    body = db.Column(db.Text)
    create_date = db.Column(db.DateTime)
    pub_date = db.Column(db.DateTime)
    pub_status = db.Column(db.Text(80))
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    author = db.relationship('User',
                             backref=db.backref('posts', lazy='dynamic'))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
                               backref=db.backref('posts', lazy='dynamic'))

    def __init__(self, title, body, category, pub_date=None):
        self.title = title
        self.body = body
        if pub_date is None:
            pub_date = datetime.utcnow()
        self.category = category
        self.pub_date = pub_date

    def __repr__(self):
        return '<Post %r>' % self.title

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    description = db.Column(db.String(250))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return self.name

Second, check out the form.... NOTICE I'm using the wtfrom sqlalchmey QuerySelectedField and the Category_ID field is absent...

FORM

from sprucepress.models import Tag, Category
from flask_wtf import Form
from wtforms.fields import StringField, DateTimeField
from wtforms.widgets import TextArea
from wtforms.validators import DataRequired
from wtforms.ext.sqlalchemy.orm import model_form
from wtforms.ext.sqlalchemy.fields import QuerySelectField


def enabled_categories():
    return Category.query.all()


class PostForm(Form):
    title = StringField(u'title', validators=[DataRequired()])
    body = StringField(u'Text', widget=TextArea())
    pub_date = DateTimeField(u'date create')
    category = QuerySelectField(query_factory=enabled_categories,
                                allow_blank=True)

Now the FLASK routing and view logic... Notice in post NO category_id again! Only Category only!!!

ROUTING/VIEW

# create new post
@app.route('/admin/post', methods=['GET', 'POST'])
@login_required  # required for Flask-Security
def create_post():
    form = PostForm()
    if form.validate_on_submit():
        post = Post(title=form.title.data, pub_date=form.pub_date.data,
                    body=form.body.data, category=form.category.data)
        db.session.add(post)
        db.session.commit()
        flash('Your post has been published.')
        return redirect(url_for('admin'))
    posts = Post.query.all()
    return render_template('create_post.html', form=form, posts=posts)

Finally, the template. Guess what, we only generate the form.category field!!!

TEMPLATE

  <form action="" method=post>
  {{form.hidden_tag()}}
    <dl>
      <dt>Title:
      <dd>{{ form.title }}
      <dt>Post:
      <dd>{{ form.body(cols="35", rows="20") }}
      <dt>Category:
      <dd>{{ form.category }}

    </dl>
    <p>
      <input type=submit value="Publish">
  </form>

The result... this solution correctly uses the Category model as a lookup table and associates the Post rows correctly by writing the Category PK integers to the Posts Category_Id field. Yeeeehaww!