<jupyter_start><jupyter_text>format_file> Functions to format a SQL file with multiple queries and SQL statements<jupyter_code>#hide
from nbdev.showdoc import *<jupyter_output><empty_output><jupyter_text>Use-CaseAssume you have a file called sql_file.sql containing SQL statements and queries.After reading it in python we could have something like this:<jupyter_code>sql_file = """
--- Views for some nice data mart ---
use database my_database;
use schema my_schema;

create or replace view first_view as -- my first view
select a.car_id,
       b.car_name, sum(a.price) over (partition by b.car_name order by a.car_id) as sum_price, a.price,
from sales as a left join (select car_id, car_name, from cars) as b 
on a.car_id = b.car_id
where car_id>1 and car_id<=100 order by b.car_name;

-- Table no. 1 --
create or replace table first_table as -- my first table
select car_id,
       avg(price) as avg_price,
from first_view
group by car_id order by car_id;

--- End of file ---
""".strip()<jupyter_output><empty_output><jupyter_text>Then we would like to format the SQL-queries in this file, while letting every other non-query-SQL statement untouched. For the example above we would like to have something like this:<jupyter_code>expected_sql_file = """
--- Views for some nice data mart ---
use database my_database;
use schema my_schema;


CREATE OR REPLACE VIEW first_view AS -- my first view
SELECT a.car_id,
       b.car_name,
       sum(a.price) OVER (PARTITION BY b.car_name
                          ORDER BY a.car_id) as sum_price,
       a.price
FROM   sales as a
    LEFT JOIN (SELECT car_id,
                      car_name
               FROM   cars) as b
        ON a.car_id = b.car_id
WHERE  car_id > 1
   and car_id <= 100
ORDER BY b.car_name;


-- Table no. 1 --
CREATE OR REPLACE TABLE first_table AS -- my first table
SELECT car_id,
       avg(price) as avg_price
FROM   first_view
GROUP BY car_id
ORDER BY car_id;

--- End of file ---
""".lstrip()<jupyter_output><empty_output><jupyter_text>Formatting philosophy of SQL files* Every SQL-query is separated from above by two new lines* Every SQL-query is formatted via `format_sql`  Main function to format the SQL commands in a fileThis function applies also basic validation and aborts formatting if the statements `CREATE .. TABLE / VIEW` appear at least twice in the same query after splitting by semicolon, warning the user that she / he may have forgotten a semicolon<jupyter_code>#export
def format_sql_commands(s, max_len=82):
    "Format SQL commands in `s`. If SELECT line is longer than `max_len` then reformat line"
    s = s.strip()  # strip file contents
    split_s = split_by_semicolon(s)  # split by query
    # validate semicolon
    validations_semicolon = [validate_semicolon(sp) for sp in split_s]
    val_summary_semicolon = sum([val["exit_code"] for val in validations_semicolon])
    # validate balanced parenthesis
    validations_balanced = [validate_balanced_parenthesis(sp) for sp in split_s]
    val_summary_balanced = sum([val["exit_code"] for val in validations_balanced])
    # validate balanced case when ... end
    val_case_end_balanced = [validate_case_when(sp) for sp in split_s if sp != ""]
    val_summary_case = sum([val["exit_code"] for val in val_case_end_balanced])
    if sum([val_summary_semicolon, val_summary_balanced, val_summary_case]) == 0:
        split_comment_after_semicolon = re.compile("((?:\n|create|select))")
        check_comment_after_semicolon = re.compile(r"[\r\t\f\v ]*(?:\/\*|--)")
        check_ending_semicolon = re.compile(r";\s*$")
        split_s_out = []  # initialize container
        last_i = len(split_s) - 1
        for i, sp in enumerate(split_s):  # split by semicolon
            # take care of comment after semicolon
            # split by first newline and format only the second item
            if check_comment_after_semicolon.match(sp):
                split_s2 = split_comment_after_semicolon.split(sp, maxsplit=1)
            else:
                split_s2 = [sp]
            formatted_split_s2 = [
                "\n\n\n" + format_sql(sp, semicolon=True, max_len=max_len).strip()
                if check_sql_query(sp) and not check_skip_marker(sp)
                else sp
                for sp in split_s2
            ]
            formatted_sp = "".join(formatted_split_s2)
            if i != last_i:
                split_c = split_comment(formatted_sp)
                s_code = "".join([d["string"] for d in split_c if not d["comment"]])
                formatted_sp = (
                    formatted_sp 
                    if check_ending_semicolon.search(s_code)
                    or formatted_sp == ""
                    else formatted_sp + ";"
                )
            split_s_out.append("".join(formatted_sp))
        # join by semicolon
        formatted_s = "".join(split_s_out)
        # remove starting and ending newlines
        formatted_s = formatted_s.strip()
        # remove more than 3 newlines
        formatted_s = re.sub(r"\n{4,}", "\n\n\n", formatted_s)
        # add newline at the end of file
        formatted_s = formatted_s + "\n"
        return formatted_s
    else:
        error_dict = {}
        if val_summary_semicolon > 0:
            file_lines = [
                tuple([line + sum([sd["total_lines"] for sd in validations_semicolon[0:i]]) for line in d["val_lines"]])
                for i, d in enumerate(validations_semicolon)
                if d["exit_code"] == 1
            ]
            error_dict["semicolon"] = {
                "error_code": 2,
                "lines": file_lines
            }
        if val_summary_balanced > 0:
            file_lines = [
                [line + sum([sd["total_lines"] for sd in validations_balanced[0:i]]) for line in d["val_lines"]]
                for i, d in enumerate(validations_balanced)
                if d["exit_code"] == 1
            ]            
            error_dict["unbalanced_parenthesis"] = {
                "error_code": 3,
                "lines": file_lines
            }
        if val_summary_case > 0:
            file_lines = [
                [line + sum([sd["total_lines"] for sd in val_case_end_balanced[0:i]]) for line in d["val_lines"]]
                for i, d in enumerate(val_case_end_balanced)
                if d["exit_code"] == 1
            ]            
            error_dict["unbalanced_case"] = {
                "error_code": 4,
                "lines": file_lines
            }            
        return error_dict<jupyter_output><empty_output><jupyter_text>Basic file formatting<jupyter_code>assert_and_print(
    format_sql_commands(sql_file),
    expected_sql_file
)<jupyter_output>82
--- Views for some nice data mart ---
use database my_database;
use schema my_schema;


CREATE OR REPLACE VIEW first_view AS -- my first view
SELECT a.car_id,
       b.car_name,
       sum(a.price) OVER (PARTITION BY b.car_name
                          ORDER BY a.car_id) as sum_price,
       a.price
FROM   sales as a
    LEFT JOIN (SELECT car_id,
                      car_name
               FROM   cars) as b
        ON a.car_id = b.car_id
WHERE  car_id > 1
   and car_id <= 100
ORDER BY b.car_name;


-- Table no. 1 --
CREATE OR REPLACE TABLE first_table AS -- my first table
SELECT car_id,
       avg(price) as avg_price
FROM   first_view
GROUP BY car_id
ORDER BY car_id;

--- End of file ---<jupyter_text>Using the `/*skip-formatter*/` marker to not format some query<jupyter_code>assert_and_print(
    format_sql_commands("""
use database my_database;

/*skip-formatter*/
create Or replace View my_view aS
select asdf, qwer
from table1;

create or replace table my_table As
Select asdf, qwer
From table2
group by asdf;
"""),
    """use database my_database;

/*skip-formatter*/
create Or replace View my_view aS
select asdf, qwer
from table1;


CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table2
GROUP BY asdf;
""")
assert_and_print(
    format_sql_commands("""
create or replace table my_table As
Select asdf, qwer
From table2
group by asdf;
"""),
    """
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table2
GROUP BY asdf;
""".lstrip())
assert_and_print(
    format_sql_commands("""
create or replace table my_table As
Select asdf, qwer
From table2
group by asdf -- some comment
;
"""),
    """
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table2
GROUP BY asdf; -- some comment
""".lstrip())
assert_and_print(
    format_sql_commands(
"""
create table my_table As
select asdf, Qwer, /* ; */
qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1; /* Some comment */

create view my_view As
Select asdf
From my_table; /* Another comment */
"""
    ),
"""
CREATE TABLE my_table AS
SELECT asdf,
       qwer, /* ; */
       qwer2, -- ;
       replace(';', '', qwer3) as qwer4
FROM   table1; /* Some comment */


CREATE VIEW my_view AS
SELECT asdf
FROM   my_table; /* Another comment */
""".lstrip()
)
assert_and_print(
    format_sql_commands(
"""
create or replace transient table my_table As
select asdf, Qwer, /* ; */
qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1;

create view my_view As
Select asdf
From my_table;
""".lstrip()
    ),
"""
CREATE OR REPLACE TRANSIENT TABLE my_table AS
SELECT asdf,
       qwer, /* ; */
       qwer2, -- ;
       replace(';', '', qwer3) as qwer4
FROM   table1;


CREATE VIEW my_view AS
SELECT asdf
FROM   my_table;
""".lstrip()
)<jupyter_output>82
CREATE OR REPLACE TRANSIENT TABLE my_table AS
SELECT asdf,
       qwer, /* ; */
       qwer2, -- ;
       replace(';', '', qwer3) as qwer4
FROM   table1;


CREATE VIEW my_view AS
SELECT asdf
FROM   my_table;<jupyter_text>If the validation fails, then the function returns a dictionary instead of the formatted queries with information about the error Semicolon validation error<jupyter_code>assert_and_print(
    format_sql_commands(
"""
create or replace transient table my_table As
select asdf, Qwer, /* ; */
qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1

create view my_view As
Select asdf
From my_table;
""".lstrip()
    ), 
    {"semicolon": {"error_code": 2, "lines": [(1, 7)]}}
)<jupyter_output>82
{'semicolon': {'error_code': 2, 'lines': [(1, 7)]}}<jupyter_text>Unbalanced parenthesis error<jupyter_code>assert_and_print(
    format_sql_commands(
"""
create or replace transient table my_table As
select asdf, Qwer, /* ; */
(qwer2, -- ; ()
( /* ) */
replace(';', '', qwer3) as Qwer4
from table1;

create view my_view As
(Select asdf
From my_table;
""".lstrip()
    ), 
    {"unbalanced_parenthesis": {"error_code": 3, "lines": [[3, 4], [9]]}}
)<jupyter_output>82
{'unbalanced_parenthesis': {'error_code': 3, 'lines': [[3, 4], [9]]}}<jupyter_text>Unbalanced parenthesis + semicolon error<jupyter_code>assert_and_print(
    format_sql_commands(
"""
create or replace transient table my_table As
select asdf, Qwer, /* ; */
(qwer2, -- ; ()
( /* ) */
replace(';', '', qwer3) as Qwer4
from table1

create view my_view As
(Select asdf
From my_table;
""".lstrip()
    ), 
    {
        "semicolon": {"error_code": 2, "lines": [(1, 8)]},        
        "unbalanced_parenthesis": {"error_code": 3, "lines": [[3, 4, 9]]},
    }
)<jupyter_output>82
{'semicolon': {'error_code': 2, 'lines': [(1, 8)]}, 'unbalanced_parenthesis': {'error_code': 3, 'lines': [[3, 4, 9]]}}<jupyter_text>Unbalanced case when ... end<jupyter_code>assert_and_print(
    format_sql_commands(
"""
create or replace transient table my_table As
select asdf, Qwer, /* ; */
case when asdf = 1 then 1 as qwer,
replace(';', '', qwer3) as Qwer4
from table1;

create view my_view As
""".lstrip()
    ), 
    {"unbalanced_case": {"error_code": 4, "lines": [[3]]}}
)<jupyter_output>82
{'unbalanced_case': {'error_code': 4, 'lines': [[3]]}}<jupyter_text>Function to format 1 SQL file<jupyter_code>#export
def format_sql_file(f, max_len=82):
    """Format file `f` with SQL commands and overwrite the file.
    If SELECT line is longer than 82 characters then reformat line
    
    Return exit_code:
    * 0 = Everything already formatted
    * 1 = Formatting applied
    * 2 = Problem detected, formatting aborted
    """
    # open the file
    with open(f, "r") as file:
        sql_commands = file.read()
    # format SQL statements
    formatted_file = format_sql_commands(sql_commands, max_len=max_len)
    if isinstance(formatted_file, dict):
        print(f"Something went wrong in file: {f}")
        if "semicolon" in formatted_file.keys():
            print(
                (
                "[WARNING] Identified CREATE keyword more than twice within the same query " +
                f"at lines {formatted_file['semicolon']['lines']}\n"
                "You may have forgotten a semicolon (;) to delimit the queries"
                )
            )
        if "unbalanced_parenthesis" in formatted_file.keys():
            print(
                (
                "[WARNING] Identified unbalanced parenthesis " +
                f"at lines {formatted_file['unbalanced_parenthesis']['lines']}\n"
                "You should check your parenthesis"
                )
            )
        if "unbalanced_case" in formatted_file.keys():
            print(
                (
                "[WARNING] Identified unbalanced case when ... end " +
                f"at lines {formatted_file['unbalanced_case']['lines']}\n"
                "You should check for missing case or end keywords"
                )
            )            
        print(f"Aborting formatting for file {f}")
        exit_code = 2

        print(f"Aborting formatting for file {f}")
        exit_code = 2

    else:
        exit_code = 0 if sql_commands == formatted_file else 1
        # overwrite file
        with open(f, "w") as f:
            f.write(formatted_file)
    return exit_code
with tempfile.NamedTemporaryFile(mode="r+") as file:
    file.write(sql_file)
    file.seek(0)
    format_sql_file(file.name)
    formatted_file = file.read()
assert_and_print(
    formatted_file,
    expected_sql_file
)
sql_forgotten_semicolon = """
create or replace transient table my_table As
select asdf, Qwer, /* ; */
qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1

create view my_view As
Select asdf
From my_table;
""".lstrip()
with tempfile.NamedTemporaryFile(mode="r+") as file:
    file.write(sql_forgotten_semicolon)
    file.seek(0)
    exit_code = format_sql_file(file.name)
    formatted_file = file.read()
assert_and_print( # no formatting
    formatted_file,
    sql_forgotten_semicolon
)
assert exit_code == 2
sql_forgotten_semicolon = """
create or replace transient table my_table As
select asdf, Qwer, /* ; */
(qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1;

create view my_view As
(Select asdf
From my_table;
""".lstrip()
with tempfile.NamedTemporaryFile(mode="r+") as file:
    file.write(sql_forgotten_semicolon)
    file.seek(0)
    exit_code = format_sql_file(file.name)
    formatted_file = file.read()
assert_and_print( # no formatting
    formatted_file,
    sql_forgotten_semicolon
)
assert exit_code == 2
sql_forgotten_semicolon = """
create or replace transient table my_table As
select asdf, Qwer, /* ; */
(qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1

create view my_view As
(Select asdf
From my_table;
""".lstrip()
with tempfile.NamedTemporaryFile(mode="r+") as file:
    file.write(sql_forgotten_semicolon)
    file.seek(0)
    exit_code = format_sql_file(file.name)
    formatted_file = file.read()
assert_and_print( # no formatting
    formatted_file,
    sql_forgotten_semicolon
)
assert exit_code == 2<jupyter_output>82
Something went wrong in file: /tmp/tmpzf3rofts
[WARNING] Identified CREATE keyword more than twice within the same query at lines [(1, 7)]
You may have forgotten a semicolon (;) to delimit the queries
[WARNING] Identified unbalanced parenthesis at lines [[3, 8]]
You should check your parenthesis
Aborting formatting for file /tmp/tmpzf3rofts
Aborting formatting for file /tmp/tmpzf3rofts
create or replace transient table my_table As
select asdf, Qwer, /* ; */
(qwer2, -- ;
replace(';', '', qwer3) as Qwer4
from table1

create view my_view As
(Select asdf
From my_table;<jupyter_text>Function to format many SQL files<jupyter_code>#export
def format_sql_files(files, recursive=False, max_len=82):
    "Format SQL `files`"
    exit_codes = []
    # if wildcard "*" is input then use it
    if len(files) == 1 and re.search("\*", files[0]):
        if recursive:  # if recursive search
            files = glob(os.path.join("**", files[0]), recursive=True)
        else:
            files = glob(files[0])
    for file in files:
        exit_codes.append(format_sql_file(file, max_len=max_len))
    if sum(exit_codes) == 0:
        print("Nothing to format, everything is fine!")
    else:
        print("All specified files were formatted!")
with tempfile.TemporaryDirectory() as tmp_dir:
    with open(os.path.join(tmp_dir, "tmp"), "w") as f:
        f.write(sql_file)
    with open(os.path.join(tmp_dir, "tmp2"), "w") as f:
        f.write(sql_file)
    format_sql_files([os.path.join(tmp_dir, "tmp"), os.path.join(tmp_dir, "tmp2")])
    with open(os.path.join(tmp_dir, "tmp"), "r") as f:
        formatted_file = f.read()
    assert_and_print(
        formatted_file,
        expected_sql_file
    )
    with open(os.path.join(tmp_dir, "tmp2"), "r") as f:
        formatted_file = f.read()
    assert_and_print(
        formatted_file,
        expected_sql_file
    )
#export
def format_sql_files_cli():
    "Format SQL files"
    parser = argparse.ArgumentParser(description="Format SQL files")
    parser.add_argument(
        "files",
        help='Path to SQL files. You can also use wildcard using ".*sql"', 
        type=str,
        nargs="+"
    )
    parser.add_argument(
        "-r",
        "--recursive",
        help="Should files also be searched in subfolders?",
        action="store_true"
    )
    parser.add_argument(
        "-m",
        "--max-line-length",
        help="Maximum line length for trunction of SELECT fields",
        type=int,
        default=82
    )
    parser.add_argument(
        "-v",
        "--version",
        action="version",
        version=f"sql_formatter version {sql_formatter.__version__}"
    )
    args = parser.parse_args()
    format_sql_files(files=args.files, recursive=args.recursive, max_len=args.max_line_length)
#hide
from nbdev.export import notebook2script
notebook2script()<jupyter_output>Converted 00_core.ipynb.
Converted 01_format_file.ipynb.
Converted 02_utils.ipynb.
Converted 03_validation.ipynb.
Converted index.ipynb.
{'fraction_non_alphanumeric': 0.110597592433362, 'fraction_numerical': 0.010425623387790198, 'mean_word_length': 2.836116264687693, 'pattern_counts': {'":': 21, '<': 44, '<?xml version=': 0, '>': 48, 'https://': 0, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '53556', 'license': 'Apache-2.0', 'n_tokens_mistral': 6273, 'n_tokens_neox': 6002, 'n_words': 1737, 'path': 'nbs/01_format_file.ipynb', 'repo_name': 'yatsky/sql_formatter', 'size': '18608'}