View source for Python - printsql.py

= 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.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;''')
</pre>

= Output =

<pre>
$ ./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;
</pre>