python中的 sql語句用法

標籤：mod   done   spec   pre   where   coalesce   state   origin   tools   

函數中應用sql語句
def _get_cust_number(self,cr,uid,ids,field_name,args,context=None):    res={}    for order in self.browse(cr, uid, ids, context=context):       #擷取單據資訊     
       aa=order.origin            if aa:                sql="select t1.cust_numr_no from stock_fah t0 left join sale_orde t1 on t0.origin=t1.name where t0.origin=‘%s‘"%(aa)                cr.execute(sql)                dict4=cr.dictfetchall()                if dict4:                    dd=dict4[0][‘cust_numr_no‘]                    res[order.id]=dd                else:                    res[order.id]=‘‘            else:                res[order.id]=‘‘    return res

或者著一種

函數中另外一種寫法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())

 或者建立views

初始化執行的函數：建立一個視圖，用sql語句    ，紫色為sql語句

def init(self,cr):        tools.sql.drop_view_if_exists(cr, ‘cust_cp_info_report‘)        cr.execute("""            create or replace view cust_product_info_report as (               select t0.id,t0.hpartner_id as hpartner_id, t0.khwl_code as khwl_code,t1.product_mp_id as productn,               t3.pname,t3.mmar,t3.spec,               t4.id as name_uom,  t5.price as custo_price,t0.product_meno as product_meno ,t0.meno as meno,t5.date_from,t5.date_to                from  product_custo_info t0                LEFT JOIN product_order t1 on t0.product_tmpl_id=t1.product_tmpl_id                LEFT JOIN product_model t3 on t3.id=t1.product_tmpl_id                LEFT JOIN ke_partner t2 on t2.id=t0.hpartner_id                LEFT JOIN product_dan t4 on  t3.uom_id=t4.id                left join partner_cp_price t5  on t5.ppp_line_id=t0.id                where  (now()>=t5.date_from  and now()<=t5.date_to) or  (t5.date_from is null and t5.date_to is null )  or                (t5.date_from is null and (now()<=t5.date_to))  or (t5.date_to is null and (now()>=t5.date_from))            )        """)

 

python中的 sql語句用法