使用sqlglot时获取WHERE子句中使用的所有列时，无效的表达式 /意外令牌

Question

sql_query = "select T.lk1,T1.lk1 as dc_lk1, T.BB_NOW_name,T.City,T.DC_name,T.SKU_code,T.SKU_description,T.Brand,T.Manufacturer,T.From_Date,T.To_date, T1.DC_CMBQ,T1.DC_AMBQ,T1.DC_ICM,T.BB_NOW_CMBQ,T.BB_NOW_AMBQ,T.BB_NOW_ICM,T.TLC,T.MLC,T.LLC,T.Supplier_name, T.SP,T.CP,T.MRP,T1.DC_SOH,T.BB_NOW_SOH,T1.Availability_Status_in_DC,T.Availability_Status_in_bbn,T1.Vendor_Fill,T1.DSD from (select CONCAT(dc.id, '_', rip.product_description_id) as lk1, CONCAT(dc.parent_dc_id , '_', rip.product_description_id) as lk2, pdc.name as DC_name, Trim(dc.name) as BB_NOW_name, rip.product_description_id as SKU_code, CONCAT(pb.name, ' ', pd.name, ' ', pd.weight) as SKU_description, pb.name as Brand, psup.name as Supplier_name, IF(sks.is_deleted = FALSE, 1, 0) AS bbd_inclusion, pt.pick_sub_type, mbq.mbq as BB_NOW_AMBQ, mbq.computed_mbq as BB_NOW_CMBQ, mbqo.from_date as From_Date, mbqo.to_date as To_date, icmd.name as BB_NOW_ICM, p.supplier_price as CP, p.sale_price as SP, p.mrp as MRP, ws.stock as BB_NOW_SOH, tpc.name as TLC, mpc.name as MLC, lpc.name as LLC, pd.Manufacturer, bc.name as City, case when rip.availability = 1 then 'Available' when rip.availability = 2 then 'OOS' else 'Not Available' end as Availability_Status_in_bbn from warehouse_dc dc left join bb_city bc on dc.city_id = bc.id inner join product_reservationinfoproducts rip on rip.reservation_info_id = dc.reservation_info_id left outer join product_product p on p.id = rip.product_id left outer join product_supplier psup on psup.id = p.supplier_id left outer join wh_producticm icm on icm.product_id = rip.product_id and icm.dc_id = dc.id left outer join wh_icmdefinition icmd on icmd.id = icm.icm_id left outer join product_productdescription pd on pd.id = rip.product_description_id left outer join product_category tpc on tpc.id = pd.top_level_category_id left outer join product_category lpc on lpc.id = pd.category_id left outer join product_category mpc on mpc.id = lpc.parent_id left outer join product_productbrand pb on pb.id = pd.brand_id left outer join warehouse_stockreservation ws on ws.ri_product_id = rip.id left outer join warehouse_dc pdc on pdc.id = dc.parent_dc_id left outer join warehouse_stockreservation pws on pws.product_description_id = rip.product_description_id and pws.reservation_info_id = pdc.reservation_info_id left outer join product_reservationinfoproducts prip on prip.id = pws.ri_product_id left outer join ( select mbq_temp.* FROM ( select wp.*, wa.name as mbq_algorithm from wh_productmbq wp left outer join product_product p on p.id = wp.product_id left outer join wh_algorithmtype wa on wa.id = wp.algorithm_type_id where wp.line_of_business_id = 1 and wp.dc_id in ({{location}}) $$and p.product_description_id in ({{sku_id}})$$ order by wp.created_on desc) mbq_temp group by dc_id, product_id) mbq on mbq.dc_id = dc.id and mbq.product_id = rip.product_id left outer join ( select mbqo_temp.* FROM ( select wp.*, usr.username from wh_productmbqoverride wp left outer join product_product p on p.id = wp.product_id left outer join auth_user usr on usr.id = wp.created_by_id where wp.line_of_business_id = 1 and wp.dc_id in ({{location}}) and wp.from_date <= CURDATE() and wp.to_date >= CURDATE() $$and p.product_description_id in ({{sku_id}})$$ order by wp.created_on desc) mbqo_temp group by dc_id, product_id) mbqo on mbqo.dc_id = dc.id and mbqo.product_id = rip.product_id left outer join warehouse_stockreservation wss on wss.ri_product_id = rip.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN product_supplier ps ON ps.id = p.supplier_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_producticm icmm ON icmm.product_id = p.id AND icmm.dc_id = dc.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_icmdefinition wi ON wi.id = icm.icm_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN source_skusourceassociation sks ON sks.sku_id = p.product_description_id AND sks.dc_ri = dc.reservation_info_id AND sks.source_id = 4 AND sks.is_deleted = FALSE AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN warehouse_product_collection_warehouseproductcollection wpcw ON wpcw.product_description_id = p.product_description_id AND wpcw.dc_id = dc.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wapi_picksubtype pt ON pt.id = wpcw.pick_sub_type_id LEFT OUTER JOIN product_supplier pss ON pss.id = p.supplier_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_productvendorfillrate fr ON fr.product_id = p.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 where dc.id in ({{location}}) and pd.is_combo = 0 and (ws.wms_qoh > 0 or rip.status = 1 or icm.id is not null) and pd.id not between 1200000 and 1300000 $$and pd.id in ({{sku_id}})$$ ) as T left join (select CONCAT(dc.id, '_', rip.product_description_id) as lk1, mbqq.mbq AS DC_AMBQ, mbqq.computed_mbq AS DC_CMBQ, wss.stock as DC_SOH, icmd.name AS DC_ICM, fr.vendor_fill_rate AS Vendor_Fill, IF(pss.supplier_type = 12, 'Y', 'N') AS DSD, case when rip.availability = 1 then 'Available' when rip.availability = 2 then 'OOS' else 'Not Available' end as Availability_Status_in_DC from warehouse_dc dc left join bb_city bc on dc.city_id = bc.id inner join product_reservationinfoproducts rip on rip.reservation_info_id = dc.reservation_info_id left outer join product_product p on p.id = rip.product_id left outer join product_supplier psup on psup.id = p.supplier_id left outer join wh_producticm icm on icm.product_id = rip.product_id and icm.dc_id = dc.id left outer join wh_icmdefinition icmd on icmd.id = icm.icm_id left outer join product_productdescription pd on pd.id = rip.product_description_id left outer join product_category tpc on tpc.id = pd.top_level_category_id left outer join product_category lpc on lpc.id = pd.category_id left outer join product_category mpc on mpc.id = lpc.parent_id left outer join product_productbrand pb on pb.id = pd.brand_id left outer join warehouse_stockreservation ws on ws.ri_product_id = rip.id left outer join warehouse_dc pdc on pdc.id = dc.parent_dc_id left outer join warehouse_stockreservation pws on pws.product_description_id = rip.product_description_id and pws.reservation_info_id = pdc.reservation_info_id left outer join warehouse_stockreservation wss on wss.ri_product_id = rip.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN ( SELECT mbq_tempp.* FROM ( SELECT wp.dc_id, wp.product_id, wp.mbq, wp.computed_mbq, wp.average, wp.sigma FROM wh_productmbq wp INNER JOIN product_product p ON p.id = wp.product_id WHERE wp.line_of_business_id = 1 AND wp.dc_id IN (select distinct parent_dc_id from warehouse_dc where id in ({{location}})) AND wp.algorithm_type_id = 1 $$AND p.product_description_id IN ({{sku_id}})$$ ORDER BY wp.created_on DESC)  GROUP BY dc_id, product_id) mbqq ON mbqq.dc_id = dc.id AND mbqq.product_id = p.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN product_supplier ps ON ps.id = p.supplier_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_producticm icmm ON icmm.product_id = p.id AND icmm.dc_id = dc.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_icmdefinition wi ON wi.id = icm.icm_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN source_skusourceassociation sks ON sks.sku_id = p.product_description_id AND sks.dc_ri = dc.reservation_info_id AND sks.source_id = 4 AND sks.is_deleted = FALSE AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN warehouse_product_collection_warehouseproductcollection wpcw ON wpcw.product_description_id = p.product_description_id AND wpcw.dc_id = dc.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wapi_picksubtype pt ON pt.id = wpcw.pick_sub_type_id LEFT OUTER JOIN product_supplier pss ON pss.id = p.supplier_id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 LEFT OUTER JOIN wh_productvendorfillrate fr ON fr.product_id = p.id AND rip.availability IN (1, 2, 3) AND pd.id <> 100 where dc.id in (select distinct parent_dc_id from warehouse_dc where id in ({{location}})) and pd.is_combo = 0 and (ws.wms_qoh > 0 or rip.status = 1 or icm.id is not null) and pd.id not between 1200000 and 1300000 $$and pd.id in ({{sku_id}})$$ ) as T1 on T.lk2 = T1.lk1"


import sqlglot
import sqlglot.expressions as exp
import sqlparse

this is the sql query 
required = sqlparse.format(sql_query, reindent=True, keyword_case='upper')
ans = set()
    # this piece of code i found on the stack overflow itself
for column in sqlglot.parse_one(required).find(exp.Where).find_all(exp.Column):
   ans.add(column.text("this"))

print("ans", ans)

任何人都知道如何提取子句中使用的所有列名，也应该在嵌套的选择语句上使用。 假设查询是 “从table_name中选择column_3，column_4其中column_1 = 10 and column_2 =“ test”。 它应该获取Column_1和column_2

请原谅我，我确实是Stackoverflow和编程的新手。 谢谢。

Answer 1

您可以执行

sqlglot.parse_one（sql）.find（exp.where）.find_all（exp.column），

但我的猜测是，我的sql是无效的，因为您有{{}}，请确保SQL在尝试用SQLGLOT解析之前是有效的