SQLite Execute query from joined Tables

Question

I am struggling with my final project. I need to join 2 Tables "book" and "idlist" and execute the values in index.html. Here is python and html code (also idlist Table book Table). If someone knows where is a mistake, I will be grateful!

@app.route("/", methods=["GET", "POST"])
@login_required
def index():
    """Show reading list"""

    if request.method == "GET":
        # Execute list joining "book" and "idlist" tables
        list = db.execute("SELECT Title1, Status, LastUpdate, Author, Year, Country, Language FROM idlist INNER JOIN book on idlist.Title1=book.Title WHERE id=:id",
                          id=session["user_id"])
        # If the user has no list yet
        if not list:
            el = {'Title1': "No", 'Author': "No", 'Year': "No", 'Country': "No", 'Language': "No", 'Status': "No", 'LastUpdate': "No"}
            return render_template("index.html")
        else:
            return render_template("index.html")
    return render_template("index.html")

html should execute the values from the joined tables

{% extends "layout.html" %}

{% block title %}
    Index
{% endblock %}
{% block main %}
        <table style="width:100%">
            <tr>
                <th>Title</th>
                <th>Author</th>
                <th>Year</th>
                <th>Country</th>
                <th>Language</th>
                <th>Status</th>
                <th>Last update</th>
            </tr>
                {% for el in list %}
                    <tr>
                        <td>
                            {{ el.Title1 }}
                        </td>
                        <td>
                            {{ el.Author }}
                        </td>
                        <td>
                            {{ el.Year }}
                        </td>
                        <td>
                            {{ el.Country }}
                        </td>
                        <td>
                            {{ el.Language }}
                        </td>
                        <td>
                            {{ el.Status }}
                        </td>
                        <td>
                            {{ el.LastUpdate }}
                        </td>
                    </tr>
                {% endfor %}
        </table>
{% endblock %}

Here is the error while I login with user id 16: RuntimeError: near "update": syntax error [SQL: 'SELECT Title, Status, update, Author, Year, Country, Language FROM idlist INNER JOIN book on idlis t.Title=book.Title WHERE id=16']

[Hint, hint](https://stackoverflow.com/help/how-to-ask). ;) Besides: the links do not work. — Markus W Mahlberg, Dec 09 '18 at 09:25
The `render_template()` function in Flask needs some data, you are not passing any data. See http://flask.pocoo.org/docs/1.0/quickstart/#rendering-templates — Tomalak, Dec 09 '18 at 10:05
By the way, you can determine which HTTP method a function is bound to directly in the `@route` decorator (http://flask.pocoo.org/docs/0.12/quickstart/#http-methods). Writing several dedicated functions is better than writing one catch-all function and using `if request.method == "GET":` etc. inside of it. — Tomalak, Dec 09 '18 at 10:16
Thanks a lot for the links @Tomalak. How I can say "go to "/" and get all the values from the joined tables with?" return redirect("/")? — Nelly Uzhakova, Dec 09 '18 at 10:21
@Tomalak I think that the problem is with query - because the error message is the same: RuntimeError: near "update": syntax error [SQL: 'SELECT Title, Status, update, Author, Year, Country, Language FROM idlist INNER JOIN book on idlis t.Title=book.Title WHERE id=16'] — Nelly Uzhakova, Dec 09 '18 at 10:27
Oh, yes. `UPDATE` is a reserved word in SQL (just like `SELECT`). Avoid calling your table columns like reserved words. It's best to rename the column. `LastUpdate` would be a fine name. — Tomalak, Dec 09 '18 at 10:29
@Tomalak Thanks I have changed to LastUpdate. Good point! The program returns no errors but unfortunately it exists with zeros for each column. Either it could not generate “list” or some problems in html. — Nelly Uzhakova, Dec 09 '18 at 17:36
@Tomalak do you mean using return render_template() is totally wrong or that I need to pass “list” into it? — Nelly Uzhakova, Dec 09 '18 at 18:01
You need to pass `list` into it. Inside `render_template()`, only variables are known that you have explicitly passed. It can't see any of the variables you're using in your other code. — Tomalak, Dec 09 '18 at 18:03
Nice! And you've figured it out on your own! :) If you post an answer containing your modified code along with a paragraph about what was the issue, I'll upvote it. — Tomalak, Dec 09 '18 at 19:42

score 1 · Answer 1 · edited Dec 22 '18 at 10:41

My program should join 2 Tables "book" and "idlist" and execute the values in index.html. The main issue was in the wrong use of the "render_template()" more presisely that I haven't passed any data into it. As I needed to express my "list" in html form, the right use would be "return render_template("index.html", list=list)"

Below is the solution:

@app.route("/", methods=["GET", "POST"])
@login_required
def index():
    """Show reading list"""

    if request.method == "GET":
        quote1 = db.execute("SELECT quote FROM quotes ORDER BY random() LIMIT 1")
        # Execute list joining "book" and "idlist" tables
        list = db.execute("SELECT Title1, Status, LastUpdate, Author, Year, Country, Language FROM idlist INNER JOIN book on idlist.Title1=book.Title WHERE id=:id",
                          id=session["user_id"])
        # If the user has no list yet
        if not list:
            el = {'Title1': "No", 'Author': "No", 'Year': "No", 'Country': "No", 'Language': "No", 'Status': "No", 'LastUpdate': "No"}
        return render_template("index.html", yourquote=quote1[0]["quote"])

    return render_template("index.html")

Here is html form:

{% extends "layout.html" %}

{% block title %}
    Index
{% endblock %}
{% block main %}
        <table style="width:100%">
            <tr>
                <th>Title</th>
                <th>Author</th>
                <th>Year</th>
                <th>Country</th>
                <th>Language</th>
                <th>Status</th>
                <th>Last update</th>
            </tr>
                {% for el in list %}
                    <tr>
                        <td>
                            {{ el.Title1 }}
                        </td>
                        <td>
                            {{ el.Author }}
                        </td>
                        <td>
                            {{ el.Year }}
                        </td>
                        <td>
                            {{ el.Country }}
                        </td>
                        <td>
                            {{ el.Language }}
                        </td>
                        <td>
                            {{ el.Status }}
                        </td>
                        <td>
                            {{ el.LastUpdate }}
                        </td>
                    </tr>
                {% endfor %}
        </table>
        <tr>
            <p> </p>

        </tr>
        <a class="card-header" href="/"><span class="blue">Inspire yourself</span></a>
         <tr>
            <p> </p>

        </tr>
        <a class="card-title"><span class="grey"><p>{{ yourquote }}</p></span></a>

{% endblock %}

You can do `if if not list: el = { ... }` without `else`, since you use `return render_template(...)` in both cases. This line can therefore be moved outside the `if`. — Tomalak, Dec 17 '18 at 10:28
I can see that you pass in `yourquote`, but you don't seem to pass in `list`. — Tomalak, Dec 22 '18 at 10:47

SQLite Execute query from joined Tables

1 Answers1