python中sql语句例题_python中的 sql语句用法

函数中另外一种写法cr.execute

cr.execute('''select ref_line_id,nolqty from( select ref_line_id,sum(COALESCE(case when t0.location_id=get_warehouse_id('成仓') then product_qty when t0.location_id=get_warehouse_id('原仓') then product_qty else -1*product_qty end,0) ) dlvqty from

stock_ynum t0 left join stock_fah t1 on t0.picking_id=t1.id

where t0.state='done' and ((t0.location_id=get_warehouse_id('成仓') and t0.location_dest_id=get_warehouse_id('Customers')) or (t0.location_id=get_warehouse_id('Customers')

and t0.location_dest_id=get_warehouse_id('成仓')))

or ((t0.location_id=get_warehouse_id('原仓')

and t0.location_dest_id=get_warehouse_id('Customers')) or (t0.location_id=get_warehouse_id('Customers') and t0.location_dest_id=get_warehouse_id('原仓')))

group by t0.product_id,ref_num,ref_line_id)t where ref_line_id in %s''',(tuple(ids),))

jhqty= dict(cr.fetchall())