Python regex to match SQL INSERT statements

Question

I am working on a django website, and I am trying to use the data dumped from a legacy database to create YAML fixtures for django.

I am writing a crude SQL parser using regex (I know, I know .. but I can't find anything that will help me do this quickly, so I have to "roll my own" - unless there are better suggestions).

Part of the "rolling my own" solution is to parse the SQL statements - these are autogenerated, so the format of the statements will not change.

Here are two sample INSERT statements:

INSERT INTO ref_geographic_region (continent_id,name) VALUES(8,'Europe (Western)');
INSERT INTO ref_currency_group (name) VALUES('Major');

I want to grok the SQL statements into the following pattern:

INSERT INTO <table_name> VALUES (one_or_more_alphanums_separated_by_comma);

I then need to match the following values:

• table_name
• one_or_more_alphanums_separated_by_comma

Here is my regex pattern. It is matching, but the grouping is not quite what I want.

pattern_string = r"INSERT INTO ([a-zA-Z\_]+)\s\(((([a-zA-Z\_]+)(\,)*)+)\)\s+VALUES\(([0-9]*)|([a-zA-Z\(\)']+)(\,)*\;"

How I can modify (and simplify) the pattern above, so it matches only the tokens I'm interested in?

Answer 1

Stop trying to parse SQL with regex. This is roughly as bad as parsing HTML with regex, since SQL is a context-free language that regexes are ill-equipped to handle. This can be accomplished far more easily with a proper parsing module like PyParsing

from pyparsing import Regex, QuotedString, delimitedList

# Object names and numbers match these regular expression
object_name = Regex('[a-zA-Z_]+')
number = Regex('-?[0-9]+')
# A string is just something with quotes around it - PyParsing has a built in
string = QuotedString("'") | QuotedString('"')

# A term is a number or a string
term = number | string

# The values we want to capture are either delimited lists of expressions we know about...
column_list = (delimitedList(object_name)).setResultsName('columns')
term_list = (delimitedList(term)).setResultsName('terms')

# Or just an expression we know about by itself
table_name = object_name.setResultsName('table')

# And an SQL statement is just all of these pieces joined together with some string between them
sql_stmt = "INSERT INTO " + table_name + "(" + column_list + ") VALUES(" + term_list + ");"


if __name__ == '__main__':
    res = sql_stmt.parseString("""INSERT INTO ref_geographic_region (continent_id,name) VALUES(8,'Europe (Western)');""")
    print res.table         # ref_geographic_region
    print list(res.columns) # ['continent_id', 'name']
    print list(res.terms)   # ['8', 'Europe (Western)']

This is a quick half-hour strawman - I'd recommend reading through its docs and getting a proper understanding of how it works. In particular, PyParsing has some strange behaviour with whitespace that it's worth understanding before you properly strike out.