sqlparse: A Powerful Non-Validating SQL Parser for Python

Introduction

sqlparse is a non-validating SQL parser for Python, offering powerful capabilities to process SQL text. It excels at splitting scripts into individual statements, formatting them according to various styles, and allowing users to traverse their intricate token trees.

This library tokenizes SQL text, organizing the recognized parts into a hierarchical structure of statements, clauses, identifiers, and expressions. A key advantage of sqlparse is its flexibility: it accepts any input without validation and makes no assumptions about specific SQL dialects. This makes it highly adaptable for handling vendor extensions and templated SQL, serving as an essential building block for formatters, linters, editors, and advanced query analysis tools.

Installation

Getting started with sqlparse is straightforward. You can install it using pip:

pip install sqlparse

Examples

sqlparse provides three primary module-level functions to cover most common needs: split(), format(), and parse().

Split a script into statements

Easily break down a SQL script containing multiple statements into a list of individual statements:

import sqlparse

sqlparse.split("select * from foo; select * from bar;")
# ['select * from foo;', 'select * from bar;']

Format a statement

Enhance the readability of your SQL queries with sqlparse's formatting capabilities. You can specify options like reindentation and keyword casing:

print(sqlparse.format("select id,name from users where active=1",
                      reindent=True, keyword_case="upper"))

This will produce:

SELECT id,
       name
FROM users
WHERE active=1

The format() function supports various options, including reindent, keyword_case, strip_comments, and indent_width, similar to its command-line counterpart.

Parse and inspect a statement

For deeper analysis, sqlparse allows you to parse a statement and inspect its token tree. This is invaluable for understanding the structure of complex queries:

statement = sqlparse.parse("select id, name from users where active = 1")[0]

print(statement.get_type())
for token in statement.tokens:
    if not token.is_whitespace:
        print(f"{token.ttype or type(token).__name__!s:20} {token!s}")

Output will resemble:

SELECT
Token.Keyword.DML    select
IdentifierList       id, name
Token.Keyword        from
Identifier           users
Where                where active = 1

Tokens with a ttype are leaf nodes, while others represent groups that can be further explored. This hierarchical representation is how nested constructs, such as subqueries and CASE expressions, are managed. For more details on traversing the API, refer to the Analyzing SQL statements documentation.

Why Use sqlparse?

sqlparse stands out as an indispensable tool for anyone working with SQL in Python due to several key features:

Flexibility: Its non-validating nature and dialect-agnostic approach mean it can parse virtually any SQL, including vendor-specific extensions and templated queries.
Foundation for Tools: It provides the core parsing logic needed to build sophisticated SQL formatters, linters, editors, and advanced query analysis applications.
Simple API: Common tasks like splitting, formatting, and parsing SQL are made easy with intuitive module-level functions.
Command-Line Utility: The sqlformat command-line tool offers quick formatting directly from your terminal, supporting in-place file modifications.
Pre-commit Integration: Seamlessly integrate sqlparse into your development workflow using pre-commit hooks to ensure consistent SQL formatting across your projects.