fork download
copy 
  1. import re
  2.  
  3. regex = r"^(.+?) as \((.*(?:\n(?!.* as \().*)*)\n\)"
  4. dict_result = {}
  5. s = ("with \n"
  6.             "qry_1 as ( -- some text\n"
  7.             "   SELECT ID, \n"
  8.             "          NAME\n"
  9.             "   FROM   ( ... other code...\n"
  10.             "),\n"
  11.             "qry_2 as ( \n"
  12.             "    SELECT coalesce (table1.ID, table2.ID) as ID,\n"
  13.             "           NAME\n"
  14.             "   FROM (...other code...\n"
  15.             "),\n"
  16.             "qry_3 as (\n"
  17.             "-- some text\n"
  18.             "     SELECT id.WEATHER AS WEATHER_MORN,\n"
  19.             "            ROW_NUMBER() OVER(PARTITION BY id.SUN\n"
  20.             "                ORDER BY id.TIME) AS SUN_TIME,\n"
  21.             "            id.RAIN,\n"
  22.             "            id.MIST\n"
  23.             "   FROM (...other code..\n"
  24.             "-- some other text\n"
  25.             ")")
  26.  
  27. for tup in re.findall(regex, s, re.MULTILINE):
  28.     matches = re.findall(r"-- (.*)", tup[1])
  29.     dict_result[tup[0]] = matches if len(matches) > 0 else None
  30.  
  31. print(dict_result)
Success #stdin #stdout 0.03s 9396KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
{'qry_1': ['some text'], 'qry_2': None, 'qry_3': ['some text', 'some other text']}
https://ideone.com/BwQN9i
language:
Python 3 (python  3.12)
created:
4 years ago
visibility:
secret

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!