使用sqlite3在Flask中查询多个表 [英] Querying multiple tables in Flask using sqlite3
问题描述
有人可以给我一个更简单的解决方案吗?
Can anyone please give me a simpler solution for this?
我正在尝试查询数据库中的四个不同的表,并在HTML中使用非常奇怪的FOR模式对其进行迭代.
I'm trying to query four different tables in my database and iterating them with a very bizarre FOR pattern, within HTML.
因为数据库很大,所以我总是遇到MemoryError.
All the time I get MemoryError because the database is huge.
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 %}
有什么方法可以使用函数并从HTML调用它,以便它返回python脚本,然后以HTML形式返回SumOfStoc的值?
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?
#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!
谢谢.
推荐答案
忘记添加一件事:
{%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 %}`
通过删除main for内部的for循环,可以迭代整个查询,但是如果我像下面的示例中那样保留代码,则仅查询1个项目.
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.
这篇关于使用sqlite3在Flask中查询多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!