Querying multiple tables in Flask using sqlite3

Question

Can anyone please give me a simpler solution for this?

I'm trying to query four different tables in my database and iterating them with a very bizarre FOR pattern, within HTML.

All the time I get MemoryError because the database is huge.

Python script:

import sqlite3
con=sqlite3.connect('/home/sergiuster/Downloads/python/exportSQL.db', check_same_thread=False)
con.row_factory = sqlite3.Row

#QUERY MATERIALECARACT
cur = con.cursor()
cur.execute("SELECT MaterialeCaracteristici.CodProdus, MaterialeCaracteristici.Rollout, MaterialeCaracteristici.CatSezon, MaterialeCaracteristici.CodEAN, MaterialeCaracteristici.Descriere,MaterialeCaracteristici.Descriere, MaterialeCaracteristici.PretVz FROM MaterialeCaracteristici WHERE MaterialeCaracteristici.CodProdus LIKE 'VGF%' GROUP BY MaterialeCaracteristici.CodProdus")
row = cur.fetchall()

#QUERY STOC
cur2=con.cursor()
cur2.execute("SELECT StocTotal.CodProdus, Sum(StocTotal.Stoc) AS SumOfStoc FROM StocTotal WHERE StocTotal.CodProdus LIKE 'VGF%' GROUP BY StocTotal.CodProdus")
row2 = cur2.fetchall()

#QUERY VANZARI
cur3=con.cursor()
cur3.execute("SELECT dbo_VanzariCumulat.CodProdus,Sum(dbo_VanzariCumulat.Cant) AS SumOfCant FROM dbo_VanzariCumulat WHERE dbo_VanzariCumulat.CodProdus LIKE 'VGF%' AND dbo_VanzariCumulat.UnLg NOT LIKE 'SH-D101' GROUP BY dbo_VanzariCumulat.CodProdus")
row3 =cur3.fetchall()

#QUERY PA
cur4=con.cursor()
cur4.execute("SELECT dbo_PA.MTRL, dbo_PA.CodProdus, dbo_PA.PA FROM dbo_PA GROUP BY dbo_PA.MTRL, dbo_PA.CodProdus, dbo_PA.PA")
row4 =cur4.fetchall()


from flask import Flask, render_template, request
app = Flask(__name__)
app.debug = True

@app.route("/index")
def index():

    return render_template('index.html', object2 = row2, object = row, object3 = row3,object4 = row4)

html:

{% for obj in object %}

            VZ:
            {% for obj3 in object3 %}
              {% if obj3['CodProdus'] == obj['CodProdus'] %}
                          {{ obj3['CodProdus'] }}//
                          {{ obj3['SumOfCant']  | int}}<br>
              {% endif %}
            {% endfor %}

            STOC:
            {% for obj2 in object2 %}
              {% if obj2['CodProdus'] == obj['CodProdus'] %}
                          {{ obj2['CodProdus'] }}//
                          {{ obj2['SumOfStoc']  | int}}<br>
              {% endif %}
            {% endfor %}

            PA:
            {% for obj4 in object4 %}
              {% if obj4['CodProdus'] == obj['CodProdus'] %}
                  {{ obj4['CodProdus'] }}//
                  {{ obj4['PA']|round(2)|float}}<br>
                  {{(((obj['PretVz']/1.19)-obj4['PA'])/obj4['PA']*100)|round(2)|float}}%
              {% endif %}
           {% endfor %}
 {% endfor %}

Is there any way that I can use a function and call it from HTML so that it will go back to the python script and then return the value for SumOfStoc back in HTML?

Example below:

#QUERY MATERIALECARACT
cur = con.cursor()
cur.execute("SELECT MaterialeCaracteristici.CodProdus, MaterialeCaracteristici.Rollout, MaterialeCaracteristici.CatSezon, MaterialeCaracteristici.CodEAN, MaterialeCaracteristici.Descriere,MaterialeCaracteristici.Descriere, MaterialeCaracteristici.PretVz FROM MaterialeCaracteristici WHERE MaterialeCaracteristici.CodProdus LIKE 'VGF%' GROUP BY MaterialeCaracteristici.CodProdus")
row = cur.fetchall()



def query_stoc(cod):  // I want to use MaterialeCaracteristici.CodProdus in html and pass it into this function, then return another value with the help of this function, in HTML;
    #QUERY STOC
    cur2=con.cursor()
    cur2.execute("SELECT StocTotal.CodProdus, Sum(StocTotal.Stoc) AS SumOfStoc FROM StocTotal WHERE StocTotal.CodProdus =? GROUP BY StocTotal.CodProdus", (cod))
    row2 = cur2.fetchall()
    return row2['SumOfStoc']

I hope i'm understandable and not making a fool of myself. Any help appreciated!

Thank you.

Ajax1234 · Answer 1 · 2018-12-04T15:27:35.850

There are several things you can do to 1) increase efficiency and 2) simplify your current code:

First, an option is to create a class to handle the different database connections. The class can have property attributes that can query from a corresponding table. Second, instead of cursor.fetchall, which loads the entire source into memory, simply return the cursor, as you only need to iterate over the source once, in the template itself. Lastly, the class instance can be passed to the template as a single parameter:

class db_Connector:
   def __init__(self, _file = '/home/sergiuster/Downloads/python/exportSQL.db'):
      self.filename = '/home/sergiuster/Downloads/python/exportSQL.db'
      self.conn = sqlite3.connect(self.filename, check_same_thread=False).cursor()
   @property
   def materialecaract(self):
     return self.conn.execute("SELECT MaterialeCaracteristici.CodProdus, MaterialeCaracteristici.Rollout, MaterialeCaracteristici.CatSezon, MaterialeCaracteristici.CodEAN, MaterialeCaracteristici.Descriere,MaterialeCaracteristici.Descriere, MaterialeCaracteristici.PretVz FROM MaterialeCaracteristici WHERE MaterialeCaracteristici.CodProdus LIKE 'VGF%' GROUP BY MaterialeCaracteristici.CodProdus")
   @property
   def stoc(self):
     return self.conn.execute("SELECT StocTotal.CodProdus, Sum(StocTotal.Stoc) AS SumOfStoc FROM StocTotal WHERE StocTotal.CodProdus LIKE 'VGF%' GROUP BY StocTotal.CodProdus")
   @property
   def vanzari(self):
     return self.conn.execute("SELECT dbo_VanzariCumulat.CodProdus,Sum(dbo_VanzariCumulat.Cant) AS SumOfCant FROM dbo_VanzariCumulat WHERE dbo_VanzariCumulat.CodProdus LIKE 'VGF%' AND dbo_VanzariCumulat.UnLg NOT LIKE 'SH-D101' GROUP BY dbo_VanzariCumulat.CodProdus")
   @property
   def pa(self):
      return self.conn.execute("SELECT dbo_PA.MTRL, dbo_PA.CodProdus, dbo_PA.PA FROM dbo_PA GROUP BY dbo_PA.MTRL, dbo_PA.CodProdus, dbo_PA.PA")

Then, in the route from which you are serving your template:

@app.route("/index")
def index():
  return render_template('index.html', _object = db_Connector())

Now, in the template, simply call the proper attributes:

{%for obj in _object.materialecaract%}

        VZ:
      {% for obj3 in _object.vanzari%}
         {% if obj3['CodProdus'] == obj['CodProdus'] %}
                      {{ obj3['CodProdus'] }}//
                      {{ obj3['SumOfCant']  | int}}<br>
          {% endif %}
        {% endfor %}

        STOC:
        {% for obj2 in _object.stoc %}
          {% if obj2['CodProdus'] == obj['CodProdus'] %}
                      {{ obj2['CodProdus'] }}//
                      {{ obj2['SumOfStoc']  | int}}<br>
          {% endif %}
        {% endfor %}

        PA:
        {% for obj4 in _object.pa %}
          {% if obj4['CodProdus'] == obj['CodProdus'] %}
              {{ obj4['CodProdus'] }}//
              {{ obj4['PA']|round(2)|float}}<br>
              {{(((obj['PretVz']/1.19)-obj4['PA'])/obj4['PA']*100)|round(2)|float}}%
          {% endif %}
       {% endfor %}
{% endfor %}

now this gives me `sqlite3.ProgrammingError: Cannot operate on a closed cursor.` — Sergiu Ster, Dec 04 '18 at 07:34
now it gives me `AttributeError: __enter__` when it tries to call `with db_Connector() as _db_obj:` — Sergiu Ster, Dec 04 '18 at 14:44
@SergiuSter Oh right, the context manager has been removed. Please see my recent edit — Ajax1234, Dec 04 '18 at 15:27
modified it and ran. it gave me `jinja2.exceptions.UndefinedError: 'tuple object' has no attribute 'SumOfCant'`erros for objects that had been formated in my html ( | int , | float) and I had to remove them, but either way, after removing all those, it still brings me an empty html page with no values from my database. https://imgur.com/aJotwxe — Sergiu Ster, Dec 04 '18 at 15:57
you saved my life. I managed to get it working by calling the sqlite data `obj[1]` instead of `obj['CodProdus']`, and so on. THANK YOU! — Sergiu Ster, Dec 05 '18 at 11:46
It works ok if I remove the VZ, STOC and PA for loops. If I leave those for loops inside the materialecaract for loop It only displays 1 item in HTML. — Sergiu Ster, Dec 05 '18 at 12:22

score 0 · Answer 2 · answered Dec 05 '18 at 13:40

Forgot to add one thing:

{%for obj in _object.materialecaract%}

    VZ:
  {% for obj3 in _object.vanzari%}
     {% if obj3['CodProdus'] == obj['CodProdus'] %}
                  {{ obj3['CodProdus'] }}//
                  {{ obj3['SumOfCant']  | int}}<br>
      {% endif %}
    {% endfor %}

    STOC:
    {% for obj2 in _object.stoc %}
      {% if obj2['CodProdus'] == obj['CodProdus'] %}
                  {{ obj2['CodProdus'] }}//
                  {{ obj2['SumOfStoc']  | int}}<br>
      {% endif %}
    {% endfor %}

    PA:
    {% for obj4 in _object.pa %}
      {% if obj4['CodProdus'] == obj['CodProdus'] %}
          {{ obj4['CodProdus'] }}//
          {{ obj4['PA']|round(2)|float}}<br>
          {{(((obj['PretVz']/1.19)-obj4['PA'])/obj4['PA']*100)|round(2)|float}}%
      {% endif %}
   {% endfor %}
{% endfor %}`

By removing the for loops inside of the main for, it iterates through the entire query, but if i leave the code like in the example below, it only queries 1 item.

Querying multiple tables in Flask using sqlite3

Python script:

html:

Example below:

2 Answers2

Linked