Find And Count Instance Of Substring Within Multiple Strings Using Python

I have a sql query within python with multiple sub-queries. So the set up is multiple substrings within a larger string. I would like to check for the number of instances of a stri

Solution 1:

You can split the CTE queries, and then use re.findall on a truncated version of the subquery:

qry = ''' 
with 
qry_1 as ( 
  SELECT ID, 
      NAME
  FROM   ( ... other code...
),
qry_2 as ( 
  SELECT coalesce (table1.ID, table2.ID) as ID,
       NAME
FROM (...other code...
),
qry_3 as (
  SELECT WEATHER
FROM (...other code..
)
'''defget_cols(s):
   [cte_name] = re.findall('^\w+(?=\sas)|(?<=with\s)\w+(?=\sas)', s)
   cols = re.findall('(?<=as\s)[\w\.]+|(?<=SELECT\s)[\w\.]+|(?<=,\s)[\w\.]+', s)
   return [cte_name, cols]

#dictionary with the cte name as the key, and the columns as the values
v = dict(get_cols(re.sub('coalesce\s\(.+\)|[\s\n]+', ' ', i)) for i in re.split('(?<=\)),(?:\s+)*\n', qry))
#filter the dictionary above to only include desired column names
r = {a:k if (k:=[i for i in b if i in {'NAME', 'ID'}]) elseNonefor a, b in v.items()}

Output:

{'qry_1': ['ID', 'NAME'], 'qry_2': ['ID', 'NAME'], 'qry_3': None}