Using libsyntaqlite from Python

The syntaqlite package is libsyntaqlite's Python API. In this tutorial you install it, format a query, check the query against a schema, and look at its syntax tree.

1. Install

pip install syntaqlite

It needs Python 3.10 or later. The wheel includes the native library, so there is nothing to compile, and it also installs the syntaqlite command.

2. Format a query

Create format.py:

import syntaqlite

with syntaqlite.Syntaqlite() as sq:
    sql = "select id,name,email from users where active=1 order by name"
    print(sq.format_sql(sql), end="")
python format.py
SELECT id, name, email FROM users WHERE active = 1 ORDER BY name;

Every call goes through a Syntaqlite instance, which holds the native library's state. The with block closes it when you are done. In a longer program, create one instance and reuse it.

3. Validate against a schema

Create analyze.py:

import syntaqlite
from syntaqlite import AnalysisOutput, RenderOptions, Schema

schema = Schema(ddl="CREATE TABLE users (id INTEGER, name TEXT, email TEXT, active INTEGER);")

with syntaqlite.Syntaqlite() as sq:
    report = sq.analyze(
        "SELECT id, nme FROM users WHERE active = 1",
        schema,
        output=AnalysisOutput.TEXT,
        render_options=RenderOptions(source_name="query.sql"),
        strict_schema=True,
    )
    print(report, end="")
python analyze.py
error: unknown column 'nme'
 --> query.sql:1:12
  |
1 | SELECT id, nme FROM users WHERE active = 1
  |            ^~~
  = help: did you mean 'name'?

strict_schema=True makes unknown tables and columns errors. Without it, they are reported as warnings. AnalysisOutput.TEXT returns the report as text; without it, analyze returns the diagnostics as objects, as the Python guide shows.

4. Parse and inspect the AST

Create parse.py:

import syntaqlite

with syntaqlite.Syntaqlite() as sq:
    stmt = sq.parse("SELECT id, name FROM users WHERE active = 1")[0]

    print(type(stmt).__name__)
    print(type(stmt.from_clause).__name__, stmt.from_clause.table_name)
    for col in stmt.columns:
        print(f"  {type(col.expr).__name__}: {col.expr.column}")
python parse.py
SelectStmt
TableRef users
  ColumnRef: id
  ColumnRef: name

parse returns one typed object per statement, with a class for each kind of node, so your editor can complete attribute names and you can use isinstance to check what a node is.

Next steps