Python - printsql.py
Revision as of 14:31, 9 November 2007 by PeterHarding (talk | contribs) (New page: = Script = Here is an interesting little script which parses SQL and then reformats it to stdout: <pre> #!/usr/bin/env python # # Pretty print SQL statements # by Peter Bengtsson, www.pe...)
Script
Here is an interesting little script which parses SQL and then reformats it to stdout:
#!/usr/bin/env python # # Pretty print SQL statements # by Peter Bengtsson, www.peterbe.com # August, 2004 import re __version__='0.1' def printsql(sql): """ simple version of pprint for SQL strings """ keywords = ('select','from','where','order by','desc','asc', 'limit','offset','update','delete','insert','set', 'having','group by','count','table','create', 'drop','and','or','in','ilike','like', ) aloner = lambda x: '(\s%s\s|^%s\s|\s%s$)'%(x,x,x) re_flags = re.I|re.MULTILINE regex = '|'.join(map(aloner, keywords)) anykeyword = re.compile(regex, re_flags) for each in anykeyword.findall(sql): each = list(each) while '' in each:each.remove('') for subeach in each: sql = sql.replace(subeach, subeach.upper()) if len(sql) > 10: spad = ' ' regex = re.compile('(SELECT\s(.*?))(\sFROM\s(.*?)\s)', re_flags) found = regex.findall(sql) if found: sql = sql.replace(found[0][0], 'SELECT\n%s%s\n'%(spad, found[0][1])) sql = sql.replace(found[0][2], 'FROM\n %s\n'%found[0][3]) regex = re.compile('(ORDER BY\s(.*?))(\sLIMIT\s(.*?)\s)', re_flags) found = regex.findall(sql) if found: sql = sql.replace(found[0][0], '\nORDER BY %s\n'%found[0][1]) sql = sql.replace(found[0][2], 'LIMIT %s\n'%found[0][3]) regex = re.compile('(\s+WHERE\s(.*?)\s(GROUP BY|ORDER BY|LIMIT))', re_flags) found = regex.findall(sql) if found: sql = sql.replace(found[0][0], '\nWHERE\n%s%s\n%s'%(spad, found[0][1],found[0][2])) return sql def test(s): print printsql(s) print if __name__=='__main__': print "TESTING SOME SQL" print test('select * from foo order by bar;') test('''select id, bull, time from foobartable where time=1 and stuff='yes' order by bar limit 30 offset 10;''')
Output
$ ./printsql.py TESTING SOME SQL SELECT * FROM foo ORDER BY bar; SELECT id, bull, time FROM foobartable WHERE time=1 AND stuff='yes' ORDER BY bar LIMIT 30 OFFSET 10;