repository·master·Indexed 26 days ago
https://github.com/andialbrecht/sqlparseA non-validating SQL parser for Python 3.10+ used to split scripts into statements, format SQL text, and walk token trees for analysis. It provides the sqlformat CLI tool and can be integrated as a pre-commit hook. Key functions include sqlparse.split(), sqlparse.format(), and sqlparse.parse(), with support for custom lexer configuration via the Lexer singleton to handle specific SQL dialects or custom keywords.
sqlparse online.sqlparse.parse(), the returned value is a tree-like representation of the analyzed SQL statements. You can traverse this tree to retrieve metadata and structural information about the SQL code.The sqlparse.lexer.Lexer is a singleton class responsible for breaking character streams into tokens using regular expressions and keyword dictionaries. To customize the parsing logic for specific SQL dialects or custom keywords, you must interact with the singleton instance.
To configure the lexer:
Lexer.get_default_instance()..clear() to remove all existing keyword dictionaries and regular expressions. Warning: .clear() removes all keyword dictionaries; you must re-add any dictionaries you wish to keep..set_SQL_REGEX(SQL_REGEX) to apply a new list of regular expressions..add_keywords(KEYWORDS) to add keyword dictionaries (e.g., keywords.KEYWORDS_COMMON, keywords.KEYWORDS_MYSQL).You can use sqlformat to automatically format SQL files during a git commit using pre-commit.
Important: When overriding args, you MUST include the --in-place flag, otherwise the hook will write to stdout and leave your files unchanged.
repos:
- repo: https://github.com/andialbrecht/sqlparse
rev: 0.5.5 # use the latest release
hooks:
- id: sqlformat
args: [--in-place, --reindent, --keywords, upper]You can integrate sqlparse with pre-commit to automatically format SQL files before they are committed to version control.
Add the following to your .pre-commit-config.yaml file:
repos:
- repo: https://github.com/andialbrecht/sqlparse
rev: 0.5.5 # Replace with the version you want to use
hooks:
- id: sqlformatAfter adding the configuration, install the hooks by running:
pre-commit installTo run the hook manually on all files, use:
pre-commit run sqlformat --all-filesrepos:
- repo: https://github.com/andialbrecht/sqlparse
rev: 0.5.5
hooks:
- id: sqlformatsqlparse, you must have Python 3.10+ installed.You can customize the formatting behavior of the sqlformat pre-commit hook by overriding the args parameter in your .pre-commit-config.yaml.
Important: When overriding args, you must include --in-place. If you omit this flag, sqlformat will write to stdout instead of modifying the file, and the pre-commit hook will leave your files unchanged.
Common arguments include:
--in-place (Required for pre-commit to modify files)--reindent or -r: Reindent statements--keywords <case> or -k <case>: Convert keywords to upper or lower--identifiers <case> or -i <case>: Convert identifiers to upper or lower--indent_width <int>: Set indentation width--strip-comments: Remove comments from SQLrepos:
- repo: https://github.com/andialbrecht/sqlparse
rev: 0.5.5
hooks:
- id: sqlformat
args: [--in-place, --reindent, --keywords, upper, --identifiers, lower]Install sqlparse from the Python Package Index (PyPI) using pip.
Requirements:
$ pip install sqlparseYou can extend sqlparse to support custom SQL syntax (like ZORDER BY) or specific keywords (like BAR) by modifying the Lexer's regex list and keyword dictionaries.
When using .clear(), you can reconstruct the regex list by slicing the existing sqlparse.keywords.SQL_REGEX and injecting your custom tuple, which should follow the format (regex_string, token_type). You can then re-populate the lexer with standard keyword sets from sqlparse.keywords and add your own custom dictionary.
import re
import sqlparse
from sqlparse import keywords
from sqlparse.lexer import Lexer
# get the lexer singleton object to configure it
lex = Lexer.get_default_instance()
# Clear the default configurations.
lex.clear()
# Define a custom regex for 'ZORDER BY'
my_regex = (r"ZORDER\s+BY\b", sqlparse.tokens.Keyword)
# slice the default SQL_REGEX to inject the custom object
lex.set_SQL_REGEX(
keywords.SQL_REGEX[:38]
+ [my_regex]
+ keywords.SQL_REGEX[38:]
)
# add the default keyword dictionaries
lex.add_keywords(keywords.KEYWORDS_COMMON)
lex.add_keywords(keywords.KEYWORDS_ORACLE)
lex.add_keywords(keywords.KEYWORDS_MYSQL)
lex.add_keywords(keywords.KEYWORDS_PLPGSQL)
lex.add_keywords(keywords.KEYWORDS_HQL)
lex.add_keywords(keywords.KEYWORDS_MSACCESS)
lex.add_keywords(keywords.KEYWORDS_SNOWFLAKE)
lex.add_keywords(keywords.KEYWORDS_BIGQUERY)
lex.add_keywords(keywords.KEYWORDS)
# add a custom keyword dictionary
lex.add_keywords({'BAR': sqlparse.tokens.Keyword})
# The lexer is now configured to handle the custom syntax
sqlparse.parse("select * from foo zorder by bar;")The sqlparse module provides three primary module-level functions for handling SQL:
sqlparse.split(): Splits a script into individual SQL statements.sqlparse.format(): Formats a SQL statement according to specific rules.sqlparse.parse(): Parses a SQL statement into a list of tokens.If the encoding parameter is not provided, sqlparse assumes the SQL statement is encoded in either utf-8 or latin-1.
sqlparse.format() to beautify SQL statements. You can pass keyword arguments to control formatting, such as reindent for indentation and keyword_case to change the casing of SQL keywords.