发布于2024-11-25 09:26 阅读(1025) 评论(0) 点赞(18) 收藏(1)
谁能给我一个更简单的解决方案?
我正在尝试查询数据库中的四个不同的表,并在 HTML 中使用非常奇怪的 FOR 模式对它们进行迭代。
由于数据库太大,我总是收到 MemoryError。
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)
{% 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 的值?
#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']
希望大家能理解我,不要让我出丑。任何帮助我的人都感激不尽!
谢谢。
您可以采取以下几种措施来 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")
然后,在您提供模板的路线中:
@app.route("/index")
def index():
return render_template('index.html', _object = db_Connector())
现在,在模板中,只需调用适当的属性:
{%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 %}
作者:黑洞官方问答小能手
链接:http://www.qianduanheidong.com/blog/article/535699/10091b43b958b0ca2ef9/
来源:前端黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 前端黑洞网 All Rights Reserved 版权所有,并保留所有权利。 京ICP备18063182号-3
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!