[FIXED] Flask how can I handle multiple sqlite3 queries in the same template?

Issue

I got a problem with my Flask application. I am trying to show multiple HTML tables on the same page that contain data from 3 different tables in the same database. At first I tried this following code

@bp.route('/')
@login_required
def summary():
 db = get_db()

 expense_entries = db.execute(
    'SELECT id, expense_date, expense_item, expense_value, expense_item_category, expense_source'
    ' FROM budget_expense'
    ' ORDER BY expense_date ASC'
 ).fetchall()

 revenue_entries = db.execute(
    'SELECT id, revenue_date, revenue_value, revenue_source'
    'FROM budget_revenue'
    'ORDER BY revenue_date ASC'
 ).fetchall()

 savings_entries = db.execute(
    'SELECT id, savings_date,savings_value, savings_source, savings_reason, savings_action'
    'FROM budget_savings'
    'ORDER BY savings_date ASC'
 ).fetchall()

 return render_template('budget/summary.html', expense_entries=expense_entries, revenue_entries=revenue_entries, savings_entries=savings_entries)

Then I have a template that loops over and sets the values in a HTML Table. Side note, it works just fine when I have just one db.execute().fetchall() added.

The error that I get is this sqlite3.OperationalError: near "BY": syntax error at the revenue_entries db.execute() --> This occurs every time, until I remove all other db.execute() commands until only one is left. I already tested the queries in DB Browser for SQLite and they execute just fine, no syntax error whatsoever.

After some research I found something similar in this question: flask multiple forms on the same page

And tried the same with a class object like the following:

@bp.route('/')
@login_required
def summary():
    class BudgetDbConnector:
        def __init__(self):
            self.db = get_db()

        @property
        def expense_entries(self):
            return self.db.execute(
                'SELECT id, expense_date, expense_item, expense_value, expense_item_category, expense_source'
                ' FROM budget_expense'
                ' ORDER BY expense_date ASC')

        @property
        def revenue_entries(self):
            return self.db.execute(
                'SELECT id, revenue_date, revenue_value, revenue_source'
                'FROM budget_revenue'
                'ORDER BY revenue_date ASC')

        @property
        def savings_entries(self):
            return self.db.execute(
                'SELECT id, savings_date,savings_value, savings_source, savings_reason, savings_action'
                'FROM budget_savings'
                'ORDER BY savings_date ASC')

    return render_template('budget/summary.html', _object=BudgetDbConnector())

with template loops exactly as in the question linked. If works just as fine when only one object is called simultaneously. If add in the template the other two queries I receive the exact error shown above.

This is the HTML structure for when I only execute 1 query from the class object. In the instance of not using the class object the for block would just be replaced with the parameters expense_entries, revenue_entries, savings_entries

{% for obj in _object.expense_entries %}
          <tbody class="post">
            <tr>
                <th class="small mb-1">{{ obj['id'] }}</th>
                <th class="small mb-1">{{ obj['expense_date'] }}</th>
                <th class="small mb-1">{{ obj['expense_item'] }}</th>
                <th class="small mb-1">{{ obj['expense_value'] }}</th>
                <th class="small mb-1">{{ obj['expense_item_category'] }}</th>
                <th class="small mb-1">{{ obj['expense_source'] }}</th>
            </tr>
          </tbody>
 {% endfor %}

This is the HTML structure for when I try to execute all 3 statements

{% for obj in _object.expense_entries %}
    <tbody class="post">
     <tr>
        <th class="small mb-1">{{ obj['id'] }}</th>
        <th class="small mb-1">{{ obj['expense_date'] }}</th>
        <th class="small mb-1">{{ obj['expense_item'] }}</th>
        <th class="small mb-1">{{ obj['expense_value'] }}</th>
        <th class="small mb-1">{{ obj['expense_item_category'] }}</th>
        <th class="small mb-1">{{ obj['expense_source'] }}</th>
    </tr>
   </tbody>
{% endfor %}
{% for obj in _object.revenue_entries %}
<tbody class="post">
  <tr>
      <th class="small mb-1">{{ obj['id'] }}</th>
      <th class="small mb-1">{{ obj['revenue_date'] }}</th>
      <th class="small mb-1">{{ obj['revenue_value'] }}</th>
      <th class="small mb-1">{{ obj['revenue_source'] }}</th>
  </tr>
</tbody>
{% endfor %}

{% for obj in _object.savings_entries %}
<tbody class="post">
  <tr>
      <th class="small mb-1">{{ obj['id'] }}</th>
      <th class="small mb-1">{{ obj['savings_date'] }}</th>
      <th class="small mb-1">{{ obj['savings_value'] }}</th>
      <th class="small mb-1">{{ obj['savings_source'] }}</th>
      <th class="small mb-1">{{ obj['savings_reasons'] }}</th>
      <th class="small mb-1">{{ obj['savings_action'] }}</th>
  </tr>
</tbody>
{% endfor %}

Is there something wrong in my code or is sqlite3 not fit for multiple concurrent queries?

Solution

You are blaming SQLite when what you actually have is a syntax error, as it says. There is a very important difference between these two queries:

   'SELECT id, expense_date, expense_item, expense_value, expense_item_category, expense_source'
    ' FROM budget_expense'
    ' ORDER BY expense_date ASC'

    'SELECT id, revenue_date, revenue_value, revenue_source'
    'FROM budget_revenue'
    'ORDER BY revenue_date ASC'

The difference is the space at the beginning the second lines. What you're passing is basically "..., revenue_sourceFROM budget_revenueORDER BY...", so the "BY" is a syntax error.

Answered By - Tim Roberts