求一段Flask(SQLAlchemy)的代码优化,关于在一个循环里重复select的 .
#计算7天内销量,并把每件商品的不同尺码库存分别显示 day7=today-datetime.timedelta(days=7) logs7=db.session.execute("SELECT product_name,goods_id,sum(number) as sellsum FROM sdb_sell_logs where createtime>:createtime group by goods_id order by sum(number) desc limit 0,20",{"createtime":datetime_timestamp(day7.strftime("%Y-%m-%d 00:00:00"))}).fetchall() goods_store7={} for item in logs7: store=db.session.execute("SELECT pdt_desc,store from sdb_products where goods_id=:goods_id",{"goods_id":item.goods_id}).fetchall() item_store=[] for store_item in store: item_store.append([store_item.pdt_desc,store_item.store]) goods_store7[item.goods_id]=item_store
这段代码要怎么优化请问?
感觉这个for item in logs7:的循环里要处理N次的select效率好低下。
会飞的绵羊
11 years, 3 months ago
Answers
你可以这样处理,做成一个查询(空间换取时间)
product_name, goods_idA, sum(number), pdt_descA, storeA product_name, goods_idA, sum(number), pdt_descB, storeB product_name, goods_idA, sum(number), pdt_descC, storeC product_name, goods_idB, sum(number), pdt_descA, storeA product_name, goods_idB, sum(number), pdt_descB, storeB product_name, goods_idB, sum(number), pdt_descC, storeC
两个SQL做个连接,剩下的就是输出时去控制成你想要的结构就好了。
wc520
answered 11 years, 3 months ago