How to query a table in flask?

0

Issue

A user have logged in and submitted 2 projects. I need to have a list of submitted projects by this user.
In my models.py I have a Project table:

class Project(db.Model):
    __tablename__ = 'project'

    users = db.relationship(User)

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) # means users.id is a ForeignKey for user_id

    date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) 
    title = db.Column(db.String(140), nullable=False)  # Project TITLE
    text = db.Column(db.Text, nullable=False) # JUST A TEXT OF THE PROJECT

    def __init__ (self,title,text,user_id):
        self.title = title
        self.text = text
        self.user_id = user_id

    def __repr__(self):
        return f"Project ID: {self.id} --Date: {self.date} -- {self.title}"

This is the route that I use to submit a project:

@app.route('/create_project', methods = ['GET','POST'])
@login_required
def create_project():

    form = ProjectForm()

    if form.validate_on_submit():

        print(current_user)
        project = Project(title = form.title.data,
                    text = form.text.data,
                    user_id = current_user.id
                    )

        db.session.add(project)

        db.session.commit
        flash('Project created')

        return redirect(url_for('welcome_user'))

    return render_template('create_project.html', form=form)

When I submit a project, the code works well and I have a flash message that project is submitted. This means the data enters into a database.

However when I want to see the projects created by the same user:

# User's list of PROJECTS:
@app.route("/<first_name>")
@login_required    # to make sure that user must be logged in to see that view
def user_posts(first_name):

    #requesting a page
    page = request.args.get('page',1,type=int)
    user = User.query.filter_by(first_name=first_name).first_or_404()

    # filtering the blog posts by username
    projects = Project.query.filter_by(author=user).order_by(Project.date.desc()).paginate(page=page, per_page=5) # comes from backref='author' on models.py

    return render_template('user_projects.html', projects=projects, user = user)

in user_projects.html I have the following:

{% extends "base.html" %}


{% block content %}

<div class="container">
  <div class="jumbotron">
    <div align="center">
      <h1>Welcome to the page for {{user.first_name}}</h1>

    </div>

  </div>
USER PROJECTS <br>
project ID
<hr>
{{projects}}
<hr>
USER
<hr>
{{user}}
<hr>
  {% for post in projects.items %}
    <h2> <a href="{{url_for('project', project_id=post.id)}}">{{post.title}}</a> </h2>

    <p class="text-muted">Published On: {{post.date.strftime("%Y-%m-%d")}}</p>

    <br>
    <p>{{post.text}}</p>
    <br>

  {% endfor %}

</div>

{% endblock %}

While I do have a user information inside this template, I can access name, surname etc., I have nothing inside a projects object, however I have <flask_sqlalchemy.Pagination object at 0x0000024F5E334CD0> on a screen – meaning that at least the query worked well and there is something in it.

I wonder how can I verify the content of the given table – when I create a test route:

@app.route('/test')
def test():
    test = Project.query.all()

    return render_template('test.html', test=test)

I see nothing inside a test object. Why?

Solution

I think you forgot the brackets behind the commit command within your route to save. The function is therefore not called and the new project is not stored in the database. The table is empty.

@app.route('/create_project', methods = ['GET','POST'])
@login_required
def create_project():
    form = ProjectForm()
    if form.validate_on_submit():
        project = Project(
            title = form.title.data,
            text = form.text.data,
            user_id = current_user.id
        )

        db.session.add(project)
        db.session.commit() # <-- The brackets were missing here!
        
        flash('Project created')
        return redirect(url_for('welcome_user'))

    return render_template('create_project.html', form=form)

Your queries appear correct.

Answered By – Detlef

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More