Command-line Tools¶
sleek¶
Sleek is a CLI tool for formatting SQL. It helps you maintain a consistent style across your SQL code, enhancing readability and productivity.
sqlfluff¶
The command sqlfluff fix can be used to fix issues (including formatting issues) in SQL code.
It can be used as a tool to format SQL code.
However,
sqlfluff fix runs extremely slow.
Python Libraries¶
andialbrecht/sqlparse¶
You can also use the Python library andialbrecht/sqlparse to help you format SQL queries. It is actually the backend of SQLFormat.
!pip3 install sqlparse
import sqlparse as sp
sql = """
select c1, c2 /* this is a comment */
from t1 join t2 on t1.id = t2.id and t1.city = 'Shanghai' where c1 > 1
"""
sql_fmt = sp.format(
sql,
keyword_case="upper",
identifier_case="lower",
strip_comments=False,
reindent=True,
indent_width=2,
)
print(sql_fmt)
sql = """
create table db.table as
select c1, c2 /* this is a comment */
from t where c1 > 1
"""
sql_fmt = sp.format(
sql,
keyword_case="upper",
identifier_case="lower",
strip_comments=False,
reindent=True,
indent_width=2,
)
print(sql_fmt)
sql = """
select c1, c2 /* this is a comment */
from student S join (select c1, c2, c3, c4, case when c5 >0 then 1 else 0 end as c5 from class where id > 0) C on S.id = C.id
join table3 on s.id = table3.id where c1 > 1
"""
sql_fmt = sp.format(
sql,
keyword_case="upper",
identifier_case="lower",
strip_comments=False,
reindent=True,
indent_width=2,
)
print(sql_fmt)
JavaScipt/TypeScript Libraries¶
Java Libraries¶
vertical-blank/sql-formatter is Java port of the great JS-based SQL formatter zeroturnaround/sql-formatter (which is itself a JS port of the PHP-based SQL formatter.)
!wget https://repo1.maven.org/maven2/com/github/vertical-blank/sql-formatter/1.0.3/sql-formatter-1.0.3.jar
!pip3 install JPype1
from pathlib import Path
import jpype
import jpype.imports
jpype.addClassPath(Path("sql-formatter-1.0.3.jar").resolve())
jpype.startJVM()
print(jpype.java.lang.System.getProperty("java.class.path"))
import com.github.vertical_blank.sqlformatter.SqlFormatter as SqlFormatter
sql = """
select c1, c2 /* this is a comment */
from t where c1 > 1
"""
print(SqlFormatter.format(sql))
help(SqlFormatter.format)
sql = """
select c1, c2 /* this is a comment */
from student S join (select c1, c2, c3, c4, case when c5 >0 then 1 else 0 end as c5 from class where id > 0) C on S.id = C.id where c1 > 1
"""
print(SqlFormatter.format(sql, " "))
Online SQL Formatting Tools¶
You can use online SQL formatters to help you format SQL queries.
SQL Style and Formatting¶
||works differently on different date types. This is because different data types have different (default) padding/formatting styles. You can manually cast date types to produce the format you want.some people like to put
,before column names in select, I don't think this is a good practices, as what if we want to remove the first column? it similarly inconvenient to removing the last column when put comma after column names