How to pretty format the printing of SQL Queries in SQLAlchemy?

Question

I'm using SQLAlchemy to generate PL/SQL on the fly using compile and setting the dialect and compile_kwargs arguments (e.g., using str(ins.compile(dialect=oracle.dialect(), compile_kwargs={'literal_binds': True}))) This works fine, except the output is not formatted in the most pretty SQL statement ever.

For example, one of my outputs looks like this:

INSERT INTO my_table (a, b, c) SELECT my_table2.d, bar.e, bar.f 
FROM my_table2 JOIN (SELECT my_table3.e AS e, max(my_table3.f) AS f, count(my_table3.g) AS g 
FROM my_table3 
WHERE my_table3.h = 'foo' GROUP BY my_table3.e 
HAVING count(my_table3.g) = 1) bar ON my_table2.g = bar.g

Instead, I would want this to print out like the following:

INSERT INTO my _table (a, b c)
SELECT my_table2.d, bar.e, bar.f
FROM my_table2 JOIN (
    SELECT my_table3.e, max(my_table3.f), count(my_table3.g)
    FROM my_table3
    WHERE my_table3.h = 'foo'
    GROUP BY my_table3.e
    HAVING count(my_table3.g) = 1
) bar ON my_table2.g = bar.g

How can I get SQLAlchemy to pretty print the SQL statements?

---

To replicate:

from sqlalchemy import table, column, String, Numeric, func, select
from sqlalchemy.dialects import oracle
my_table = table('my_table', column('a', String), column('b', String), column('c', String))
my_table2 = table('my_table2', column('d', String), column('g', String))
my_table3 = table('my_table3', column('d', String), column('e', String), column('f', Numeric), column('g', String), column('h', String))

inner_sel = select([my_table3.c.e, func.max(my_table3.c.f).label('f'), func.count(my_table3.c.g).label('g')]).where(my_table3.c.h=='foo').group_by(my_table3.c.e).having(func.count(my_table3.c.g)==1).alias('bar')


outer_sel = select([my_table2.c.d, inner_sel.c.e, inner_sel.c.f]).select_from(my_table2.join(inner_sel, my_table2.c.g==inner_sel.c.g))

ins = my_table.insert().from_select([my_table.c.a, my_table.c.b, my_table.c.c], outer_sel)

print ins.compile(dialect=oracle.dialect(), compile_kwargs={'literal_binds': True})

Answer 1

You can use sqlparse package and sqlparse.format(sql, reindent=True, keyword_case='upper') should do what you want?

Answer 2

The project sqlparse is mature (10+ years) and still very active. sqlparse aims parsing, splitting and formatting SQL statements.

The following example uses sqlparse to pretty formats SQL files:

import argparse
import sqlparse

# Parse command line arguments
parser = argparse.ArgumentParser(prog="pretty_print_sql")
parser.add_argument("file", type=argparse.FileType("r"), nargs="+")
args = parser.parse_args()

# Pretty print input files
for file in args.file:
    print(sqlparse.format(file.read(), reindent=True, keyword_case='upper'))

To install sqlparse using pip for personal usage:

python3 -m pip install sqlparse --user --upgrade

To install sqlparse using pipenv (within a project):

python3 -m pipenv install sqlparse

Answer 3

There's a couple of options to try:

• Pygments
• sqlparse
• format-sql