How to insert into association table via HTML form?

Question

Trying to reproduce (in the simplest way possible) Maciej Cegłowski's http://pinboard.in; instead of Links and Tags, I have Books and Tags. Every Book can be tagged with any number of Tag's, and a Tag is associated with many Book's.

class Book(db.Model):
    __tablename__ = 'books'
    book_id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), unique=True)
    auth = db.Column(db.String(120), unique=True)
    comment = db.Column(db.String(120), unique=True)
    date_read = db.Column(db.DateTime)
    era = db.Column(db.String(36))
    url = db.Column(db.String(120))
    notable = db.Column(db.String(1))

    tagged = db.relationship('Tag', secondary=assoc, backref=db.backref('thebooks',lazy='dynamic'))

class Tag(db.Model):
    __tablename__ = 'tags'
    tag_id = db.Column(db.Integer, primary_key=True)
    tag_name = db.Column(db.String(120))

def construct_dict(query):
    books_dict = {}
    for each in query: # query is {<Book object>, <Tag object>} in the style of assoc table - therefore, must make a dictionary bc of the multiple tags per Book object
        book_data = books_dict.setdefault(each[0].book_id, {'bookkey':each[0], 'tagkey':[]}) # query is a list like this {index-book_id, {<Book object>}, {<Tag object #1>, <Tag object #2>, ... }}
        book_data['tagkey'].append(each[1])
    return books_dict

@app.route('/query')
def query():
    query = db.session.query(Book, Tag).outerjoin('tagged') # query to get all books and their tags
    books_dict = construct_dict(query)

    return render_template("query.html", query=query, books_dict=books_dict)

This is where I start to get a bit lost; that is, in constructing the proper logic to handle what I'm trying to do, which is described in detail below.

{% for i in books_dict %}  
  <a href="{{books_dict[i].bookkey.url}}" target="_blank">{{books_dict[i].bookkey.title}}</a>
  {% for i in books_dict[i].tagkey %} # tagkey is a list of Tag objects; for each Book's tagkey, print each Object's tag_name
      <a href="/tag/{{i.tag_name}}" class="tag-link">{{i.tag_name}}</a>
  {% endfor %}
  <a href="" class="edit">edit</a> # eventually, edit link will display the form
  <form method="add_tag_to_book">
      {% for j in books_dict[i].tagkey %}
          <input type="text" name="tag" value="{{j.tag_name}}" />
      {% endfor %}
      <input type="submit" value="save">
   </form>
{% endfor %}

For any book, a user (just me for now) should be able to:

• UPDATE the assoc table such that a new association between a Book instance and Tag instance is created;
• or, if the Tag does not exist, create a new Tag instance (and, of course, UPDATE the assoc table so that the Book instance is properly associated with the new Tag)

I think this task is complicated for me because I'm still struggling with scope in Jinja loops. I realize, however, I need to do something like this:

1. grab input from user; check to see if tag_name already exists in __tablename__ = "tags"

2. if tag_name already exists, then grab its tag_id as well as the book_id for the Book instance AND add row to assoc table (i.e., book_id|tag_id)

3. if tag_name does not exist, create new Tag() instance and then perform STEP 2

Answer 1

First, construction of the book_dict dictionary:

def construct_dict(query):
    books_dict = {}
    for each in query: # query is {<Book object>, <Tag object>} in the style of assoc table - therefore, must make a dictionary bc of the multiple tags per Book object
        book_data = books_dict.setdefault(each[0].book_id, {'bookkey':each[0], 'tagkey':[]}) # query is a list like this {index-book_id, {<Book object>}, {<Tag object #1>, <Tag object #2>, ... }}
        book_data['tagkey'].append(each[1])
    return books_dict

@app.route('/query')
def query():
    query = db.session.query(Book, Tag).outerjoin('tagged') # query to get all books and their tags
    books_dict = construct_dict(query)

    return render_template("query.html", query=query, books_dict=books_dict)

Then, in addition to printing each Book instance in book_dict (and listing the book's associated Tag objects), we create a form for each Book instance that will allow user to associate new Tag's for a Book:

{% for i in books_dict %}
    <a href="{{books_dict[i].bookkey.url}}">{{books_dict[i].bookkey.title}}</a>,
    {{books_dict[i].bookkey.auth}}

    {% for i in books_dict[i].tagkey %}
        <a href="/tag/{{i.tag_name}}" class="tag-link">{{i.tag_name}}</a>
    {% endfor %}

    <form action="{{ url_for('add_tag_to_book') }}" method=post class="edit-form">
        <input type="hidden" name="book_id" value={{books_dict[i].bookkey.book_id}} />
        <input type="text" name="tag_name" value="" />
        <input type="submit" value="save">
    </form>
{% endfor %}

... the visible <input> will take the value entered by the user, which has name="tag_name"; when the form is submitted, the route /add_tag_to_book route is called. From the form, we grab the book_id (which was printed in the form but is not visible, i.e., <input type="hidden" name="book_id" value={{books_dict[i].bookkey.book_id}} />); we also grab the value of <input> element with name="tag_name"):

@app.route('/add_tag_to_book', methods=['POST'])
def add_tag_to_book():
    b = request.form['book_id']
    t = request.form['tag_name']

Next, we should check if tag_name submitted by the user is already a Tag(); Python returns None if the tag_name is not found in the Tag table; otherwise, it will return the Tag object that has tag_name=t (i.e., tag_name submitted by the user); if tag_object == None, we need to create a new instance of Tag() using the tag_name provided by user:

    tag_object = Tag.query.filter_by(tag_name=t).first()

    if tag_object == None:
        new_tag = Tag(tag_name=t)
        db.session.add(new_tag)
        db.session.commit()
        tag_object = Tag.query.filter_by(tag_name=t).first()

    tag_object_id = tag_object.tag_id

At this point, we will have a tag_object (either newly created or previously in our Tag table) whose tag_id we can grab and insert into our association table, along with the book_id for the Book object. Next, we create a database connection, insert book_id & tag_id, commit to database, and then return the user to the query page:

    conn = db.session.connection()
    ins = assoc.insert().values(book_id=b,tag_id=tag_object_id)
    result = conn.execute(ins)
    db.session.commit()

    return redirect(url_for('query'))

Putting it all together, the full @app.route('/add_tag_to_book') looks like this:

@app.route('/add_tag_to_book', methods=['POST'])
def add_tag_to_book():
    b = request.form['book_id']
    t = request.form['tag_name']
    tag_object = Tag.query.filter_by(tag_name=t).first()

    if tag_object == None:
        new_tag = Tag(tag_name=t)
        db.session.add(new_tag)
        db.session.commit()
        tag_object = Tag.query.filter_by(tag_name=t).first()

    tag_object_id = tag_object.tag_id

    conn = db.session.connection()
    ins = assoc.insert().values(book_id=b,tag_id=tag_object_id)
    result = conn.execute(ins)
    db.session.commit()

    return redirect(url_for('query'))