使用sqlite3查询Flask中的多个表

Question

有谁能给我一个更简单的解决办法吗？在

我试图在我的数据库中查询四个不同的表，并在HTML中用一个非常奇怪的FOR模式迭代它们。在

因为数据库是巨大的。在

Python脚本：

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格式：

^{pr2}$

有没有什么方法可以让我使用一个函数并从HTML调用它，这样它就可以返回python脚本，然后用HTML返回sumofsoc的值？在

示例如下：

#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']

我希望我能理解，不要出丑。 感谢任何帮助！在

谢谢。在

Answer 1

忘了加一件事：

{%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 %}`  

通过删除mainfor中的for循环，它将遍历整个查询，但是如果我像下面的示例一样保留代码，它只查询1个项。

Answer 2

您可以做以下几件事：1）提高效率；2）简化当前代码：

首先，一个选项是创建一个类来处理不同的数据库连接。类可以有property属性，这些属性可以从相应的表中查询。第二，与cursor.fetchall不同，它将整个源代码加载到内存中，而只需返回光标，因为您只需要在模板本身中迭代源代码一次。最后，类实例可以作为单个参数传递给模板：

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")

然后，在为模板提供服务的路径中：

^{pr2}$

现在，在模板中，只需调用适当的属性：

{%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 %}