Sqlglot Python

SQLGlot Python

Produce dialect-explicit SQLGlot code that treats SQL as a typed syntax tree, preserves caller ownership, separates syntactic rewrite from semantic proof, and fails visibly when translation is unsupported.

Boundary

Use this skill when the implementation imports SQLGlot or explicitly needs its parser/transpiler/AST. SQLGlot does not execute against the target database and is not an authorization sandbox, query optimizer for physical performance, or substitute for database parameters. Use the database's own tooling for runtime plans and execution semantics.

Know the objects before editing

parse_one returns one root expression; parse preserves a multi-statement sequence. .sql(dialect=...) generates text from an AST. transpile combines source parsing and target generation, but syntactic success is not proof of equivalent behavior in the target engine. Expression methods can mutate nodes or return changed trees; copy a caller-owned tree before modifying it unless in-place mutation is the declared API. Read the AST and dialect model.

Ordered workflow

1. Recover the contract: one or many statements, known source dialect, target dialect, allowed statement/node classes, required semantic preservation, available schema, output formatting, and error policy.
2. Pass read= whenever the source dialect is known. Do not let the SQLGlot superset dialect guess vendor syntax.
3. Parse once. Reject parse errors and disallowed roots before traversing or transforming.
4. Use Expression classes, find_all, scopes, builders, or transform; never use regex/string replacement for structural SQL edits.
5. Supply schema and qualify/type-annotate only when the transformation needs name or type resolution. Do not invent missing catalog facts.
6. Copy before changing a tree whose original must remain available.
7. Generate with an explicit target dialect and a strict unsupported-feature policy. Preserve statement count and ordering.
8. Reparse generated SQL in the target dialect, then run target-engine tests when semantic equivalence matters.

Choose by intent

Read the operation map before choosing parsing, building, scope analysis, transformation, optimization, or generation APIs.

Canonical strict transpilation

import sqlglot
from sqlglot import ErrorLevel


def transpile_script(sql: str, source: str, target: str) -> list[str]:
    if not source or not target:
        raise ValueError("source and target dialects are required")
    return sqlglot.transpile(
        sql,
        read=source,
        write=target,
        unsupported_level=ErrorLevel.RAISE,
        pretty=True,
    )

The list preserves the possibility of multiple statements and rejects known unsupported generation rather than warning and emitting a degraded query. Callers that accept only one statement must check len(result) == 1. Reparse each output with read=target; execute representative queries in the target engine before claiming semantic equivalence.

Canonical structural inspection

from sqlglot import exp, parse_one


def selected_columns(sql: str, dialect: str) -> tuple[str, ...]:
    tree = parse_one(sql, read=dialect)
    if not isinstance(tree, exp.Select):
        raise ValueError(f"expected SELECT, got {type(tree).__name__}")
    return tuple(node.sql(dialect=dialect) for node in tree.expressions)

This inspects projection expressions rather than regex-matching commas or column-like tokens. It does not claim to resolve *, aliases, or source columns; that requires scopes and often schema qualification.

High-risk rules

Dialects and semantics

• Always specify the source dialect when known and target dialect when generating. Identifier case, quoting, function names, date arithmetic, casts, arrays, JSON, and null behavior can differ.
• Treat unsupported warnings as failures for correctness-critical translation. A successful parse means the syntax was represented, not that every target construct has an equivalent.
• Type-sensitive transpilation and optimization need schema information. If the catalog is unavailable, preserve the construct or return an uncertainty; never fabricate column types.
• SQLGlot optimization is logical canonicalization, not target physical-plan tuning. Benchmark or inspect the actual engine for performance claims.

AST mutation and resolution

• Match nodes by class and semantic arguments, not str(node). Preserve comments and formatting only to the degree the selected generator supports; regenerated SQL is not a textual round trip.
• Copy before mutation when a function promises not to alter its input. Test nested subqueries, CTE shadowing, aliases, quoted identifiers, stars, and correlated references.
• find_all(exp.Column) finds syntax nodes but does not resolve which table supplies them. Use scope/qualification analysis for lineage or authorization.
• Construct identifiers with identifier helpers/nodes and values with literals or executor placeholders. Never turn untrusted text into an expression by parsing it merely to avoid quoting.

Security boundary

Parsing SQL does not make it safe to execute. If only read-only queries are allowed, inspect every parsed statement and all prohibited node classes, reject commands the parser represents generically, enforce database credentials and resource limits, and bind data values at execution. Treat AST allowlisting as defense in depth, not the sole authorization layer.

Run the installed-API inspector, read version and API grounding, then apply the AST verification matrix.

Completion gate

Do not declare completion until source and target dialects are explicit or a documented unknown branch exists; multi-statement input cannot bypass checks; allowed root/node classes are enforced; mutations honor ownership; identifiers and values remain distinct; schema-dependent transforms have real schema; unsupported generation is surfaced; target output reparses; nested/quoted/CTE fixtures pass; and semantic or performance claims are verified in the target database rather than inferred from SQLGlot alone.

References

• AST and dialect model
• Operation map
• Version and API grounding
• Testing SQLGlot transformations