Python regex to match "on" join clause in SQL

Question

I'm trying to analyze some SQL using python. I've got SQL that might contain multiple "on" clauses specifying table joins.

e.g

"on tableA.user_id = tableB.id"

I'd like to parse out all of the join relationships.

I tried the following but am having difficulty detecting multiple ones. It just finds the first one and stops there. Can someone help?

pattern = re.compile(r'on\s([^"\s]*)\s*=\s*([^"\s]*)')
results = re.search(pattern, sql)
for result in results.groups():
     print result

https://github.com/andialbrecht/sqlparse/ might be useful for you — ThiefMaster, Jan 16 '15 at 22:34
Okay, figured it out. I need to use findall: http://stackoverflow.com/questions/7724993/python-using-regex-to-find-multiple-matches-and-print-them-out — user491880, Jan 16 '15 at 22:36

score 0 · Answer 1 · answered Jan 16 '15 at 22:40

If you want to stick to python, look into the (very good) python regex docs here. It specifically says that re.search only matched one occurrance.

You probably want to use re.match, which will return none if it did not match, and a match object containing all occurrances otherwise.

score 0 · Answer 2 · answered Jan 16 '15 at 22:58

I would use the re.findall() function.

Return all non-overlapping matches of pattern in string, as a list of strings. The string is scanned left-to-right, and matches are returned in the order found. If one or more groups are present in the pattern, return a list of groups; this will be a list of tuples if the pattern has more than one group. Empty matches are included in the result unless they touch the beginning of another match.

Python regex to match "on" join clause in SQL

2 Answers2