<jupyter_start><jupyter_text>![CI](https://github.com/PabloRMira/sql_formatter/workflows/CI/badge.svg) [![PyPI](https://img.shields.io/pypi/v/sql-formatter?color=yellow&label=pypi%20version)](https://pypi.org/project/sql-formatter/description)[![Anaconda-Server Badge](https://anaconda.org/pablormira/sql_formatter/badges/version.svg)](https://anaconda.org/pablormira/sql_formatter)  sql_formatter> A Python based SQL formatter  How to installVia pip`pip install sql-formatter`or via conda`conda install -c pablormira sql_formatter`  How to useFormat your SQL files via the command line`sql-formatter sql_file.sql sql_file2.sql`You can also format all your SQL-files via`sql-formatter *.sql`To format all your SQL files recursively use`sql-formatter -r "*.sql"`  Controlling maximum length line via truncationThe `sql_formatter` will try to truncate too long lines in the `SELECT` clause for either* Function with many arguments* `in` with many elementsThe default maximum line length is 82 after line stripping.You can control the maximum length line using e.g.`sql-formatter sql_file.sql --max-line-length=50`  Usage with `pre-commit` [pre-commit](https://pre-commit.com) is a nice development tool to automatize the binding of pre-commit hooks. After installation and configuration `pre-commit` will run your hooks before you commit any change. To add `sql-formatter` as a hook to your `pre-commit` configuration to format your SQL files before commit, just add the following lines to your `.pre-commit-config.yaml`:```yamlrepos:  - repo: https://github.com/PabloRMira/sql_formatter    rev: master    hooks:    - id: sql_formatter```If you want to install `sql-formatter` locally and use that instead of using `pre-commit`'s default environment, set `repo: local` in your `.pre-commit-config.yaml` file:```yamlrepos:  - repo: local    hooks:    - id: sql_formatter      name: SQL formatter      language: system      entry: sql-formatter      files: \.sql$```or```yamlrepos:  - repo: local    hooks:    - id: sql_formatter      name: SQL formatter      language: system      entry: sql-formatter --max-line-length=50      files: \.sql$```for a custom maximum line length truncation of e.g. 50  Usage in Python To exemplify the formatting let's say you have a SQL query like this<jupyter_code>example_sql = """
create or replace table mytable as -- mytable example
seLecT a.asdf, b.qwer, -- some comment here
c.asdf, -- some comment there
b.asdf2 frOm table1 as a leFt join 
table2 as b -- and here a comment
    on a.asdf = b.asdf  -- join this way
    inner join table3 as c
on a.asdf=c.asdf
whEre a.asdf= 1 -- comment this
anD b.qwer =2 and a.asdf<=1 --comment that
or b.qwer>=5
groUp by a.asdf
"""<jupyter_output><empty_output><jupyter_text>Then you can use this package to format it so that it is better readable<jupyter_code>from sql_formatter.core import format_sql
print(format_sql(example_sql))<jupyter_output>CREATE OR REPLACE TABLE mytable AS -- mytable example
SELECT a.asdf,
       b.qwer, -- some comment here
       c.asdf, -- some comment there
       b.asdf2
FROM   table1 as a
    LEFT JOIN table2 as b -- and here a comment
        ON a.asdf = b.asdf -- join this way
    INNER JOIN table3 as c
        ON a.asdf = c.asdf
WHERE  a.asdf = 1 -- comment this
   and b.qwer = 2
   and a.asdf <= 1 --comment that
    or b.qwer >= 5
GROUP BY a.asdf<jupyter_text>It can even deal with subqueries and it will correct my favourite simple careless mistake (comma at the end of SELECT statement before of FROM) for you on the flow :-)<jupyter_code>print(format_sql("""
select asdf, cast(qwer as numeric), -- some comment
qwer1
from 
(select asdf, qwer, from table1 where asdf = 1) as a
left 
join (select asdf, qwer2 from table2 where qwer2 = 1) as b
on a.asdf = b.asdf
where qwer1 >= 0
"""))<jupyter_output>SELECT asdf,
       cast(qwer as numeric), -- some comment
       qwer1
FROM   (SELECT asdf,
               qwer
        FROM   table1
        WHERE  asdf = 1) as a
    LEFT JOIN (SELECT asdf,
                      qwer2
               FROM   table2
               WHERE  qwer2 = 1) as b
        ON a.asdf = b.asdf
WHERE  qwer1 >= 0<jupyter_text>The formatter is also robust against nested subqueries<jupyter_code>print(format_sql("""
select field1, field2 from (select field1, 
field2 from (select field1, field2, 
field3 from table1 where a=1 and b>=100))
"""))<jupyter_output>SELECT field1,
       field2
FROM   (SELECT field1,
               field2
        FROM   (SELECT field1,
                       field2,
                       field3
                FROM   table1
                WHERE  a = 1
                   and b >= 100))<jupyter_text>If you do not want to get some query formatted in your SQL file then you can use the marker `/*skip-formatter*/` in your query to disable formatting for just the corresponding query<jupyter_code>from sql_formatter.format_file import format_sql_commands
print(format_sql_commands(
"""
use database my_database;

-- My first view --
create or repLace view my_view as
select asdf, qwer from table1
where asdf <= 10;


/*skip-formatter*/
create oR rePlace tabLe my_table as
select asdf
From my_view;
"""
))<jupyter_output>use database my_database;


-- My first view --
CREATE OR REPLACE VIEW my_view AS
SELECT asdf,
       qwer
FROM   table1
WHERE  asdf <= 10;


/*skip-formatter*/
create oR rePlace tabLe my_table as
select asdf
From my_view;
{'fraction_non_alphanumeric': 0.0907746348678129, 'fraction_numerical': 0.013865779256794232, 'mean_word_length': 3.2631993695823485, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 24, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '171851', 'license': 'Apache-2.0', 'n_tokens_mistral': 1784, 'n_tokens_neox': 1681, 'n_words': 626, 'path': 'nbs/index.ipynb', 'repo_name': 'yatsky/sql_formatter', 'size': '5409'}