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
| Object |
Meaning |
Use it for |
| Dialect |
Parser and generator rules for a SQL family. |
Interpreting source tokens and emitting target syntax. |
Expression |
One AST node with named child arguments and parent/tree relationships. |
Semantic inspection, construction, and transformation. |
| Statement expression |
Root node such as Select, Insert, Create, or Command. |
Classifying allowed jobs and preserving statement boundaries. |
Identifier |
A name whose quoting and normalization depend on dialect. |
Tables, columns, aliases, and other identifiers—not data values. |
Literal / placeholder node |
A typed SQL value or bind marker in the AST. |
Values; keep runtime user data parameterized by the executor. |
| Scope |
A query-resolution context for sources, projections, subqueries, and CTEs. |
Analysis that cannot be correct from local node search alone. |
| Schema mapping |
Table/column type information supplied by the caller. |
Qualification, star expansion, type annotation, and type-sensitive rewrites. |
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
- 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.
- Pass
read= whenever the source dialect is known. Do not let the SQLGlot
superset dialect guess vendor syntax.
- Parse once. Reject parse errors and disallowed roots before traversing or
transforming.
- Use
Expression classes, find_all, scopes, builders, or transform; never
use regex/string replacement for structural SQL edits.
- Supply schema and qualify/type-annotate only when the transformation needs
name or type resolution. Do not invent missing catalog facts.
- Copy before changing a tree whose original must remain available.
- Generate with an explicit target dialect and a strict unsupported-feature
policy. Preserve statement count and ordering.
- Reparse generated SQL in the target dialect, then run target-engine tests
when semantic equivalence matters.
Choose by intent
| Need |
Use |
Required guard |
| Parse exactly one known statement |
parse_one(sql, read=...) |
Establish that a one-statement boundary is intended. |
| Preserve a script's statements |
parse(sql, read=...) |
Validate every root; do not inspect only the first. |
| Find local syntax nodes |
find / find_all with exp classes |
Local traversal is not column resolution. |
| Resolve sources/columns across CTEs/subqueries |
Scope/optimizer qualification utilities |
Supply schema/catalog facts where required. |
| Rewrite nodes recursively |
Expression.transform or explicit AST replacement |
Preserve parentage and copy policy; test nested cases. |
| Construct SQL |
sqlglot.exp nodes/builders |
Keep identifiers distinct from literals and bind parameters. |
| Translate dialects |
transpile(read=..., write=...) or parse then .sql(dialect=...) |
Raise/report unsupported constructs; validate target semantics. |
| Canonical logical AST |
supported optimizer entry point with schema |
Not physical query-performance tuning. |
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
1---2name: sqlglot-python3description: Use for writing, reviewing, debugging, or testing Python code that parses, inspects, transforms, builds, qualifies, optimizes, formats, or transpiles SQL with SQLGlot. Trigger on parse_one/parse, sqlglot expressions and scopes, dialect conversion, AST traversal, lineage, qualification, schema-aware rewrites, and unsupported translation. Do not use for executing SQL, database query tuning, regex-only text edits, ORM query construction, SQLFluff lint configuration, or generic SQL unrelated to SQLGlot.4argument-hint: "[SQLGlot parse, AST, transform, dialect, or transpilation task]"5metadata:6 source: https://skillmd.com/skills/schattenspiegel/sqlglot-python7---89# SQLGlot Python1011Produce dialect-explicit SQLGlot code that treats SQL as a typed syntax tree,12preserves caller ownership, separates syntactic rewrite from semantic proof, and13fails visibly when translation is unsupported.1415## Boundary1617Use this skill when the implementation imports SQLGlot or explicitly needs its18parser/transpiler/AST. SQLGlot does not execute against the target database and19is not an authorization sandbox, query optimizer for physical performance, or20substitute for database parameters. Use the database's own tooling for runtime21plans and execution semantics.2223## Know the objects before editing2425| Object | Meaning | Use it for |26|---|---|---|27| Dialect | Parser and generator rules for a SQL family. | Interpreting source tokens and emitting target syntax. |28| `Expression` | One AST node with named child arguments and parent/tree relationships. | Semantic inspection, construction, and transformation. |29| Statement expression | Root node such as `Select`, `Insert`, `Create`, or `Command`. | Classifying allowed jobs and preserving statement boundaries. |30| `Identifier` | A name whose quoting and normalization depend on dialect. | Tables, columns, aliases, and other identifiers—not data values. |31| `Literal` / placeholder node | A typed SQL value or bind marker in the AST. | Values; keep runtime user data parameterized by the executor. |32| Scope | A query-resolution context for sources, projections, subqueries, and CTEs. | Analysis that cannot be correct from local node search alone. |33| Schema mapping | Table/column type information supplied by the caller. | Qualification, star expansion, type annotation, and type-sensitive rewrites. |3435`parse_one` returns one root expression; `parse` preserves a multi-statement36sequence. `.sql(dialect=...)` generates text from an AST. `transpile` combines37source parsing and target generation, but syntactic success is not proof of38equivalent behavior in the target engine. Expression methods can mutate nodes39or return changed trees; copy a caller-owned tree before modifying it unless40in-place mutation is the declared API. Read [the AST and dialect41model](references/object-model.md).4243## Ordered workflow44451. Recover the contract: one or many statements, known source dialect, target46 dialect, allowed statement/node classes, required semantic preservation,47 available schema, output formatting, and error policy.482. Pass `read=` whenever the source dialect is known. Do not let the SQLGlot49 superset dialect guess vendor syntax.503. Parse once. Reject parse errors and disallowed roots before traversing or51 transforming.524. Use `Expression` classes, `find_all`, scopes, builders, or `transform`; never53 use regex/string replacement for structural SQL edits.545. Supply schema and qualify/type-annotate only when the transformation needs55 name or type resolution. Do not invent missing catalog facts.566. Copy before changing a tree whose original must remain available.577. Generate with an explicit target dialect and a strict unsupported-feature58 policy. Preserve statement count and ordering.598. Reparse generated SQL in the target dialect, then run target-engine tests60 when semantic equivalence matters.6162## Choose by intent6364| Need | Use | Required guard |65|---|---|---|66| Parse exactly one known statement | `parse_one(sql, read=...)` | Establish that a one-statement boundary is intended. |67| Preserve a script's statements | `parse(sql, read=...)` | Validate every root; do not inspect only the first. |68| Find local syntax nodes | `find` / `find_all` with `exp` classes | Local traversal is not column resolution. |69| Resolve sources/columns across CTEs/subqueries | Scope/optimizer qualification utilities | Supply schema/catalog facts where required. |70| Rewrite nodes recursively | `Expression.transform` or explicit AST replacement | Preserve parentage and copy policy; test nested cases. |71| Construct SQL | `sqlglot.exp` nodes/builders | Keep identifiers distinct from literals and bind parameters. |72| Translate dialects | `transpile(read=..., write=...)` or parse then `.sql(dialect=...)` | Raise/report unsupported constructs; validate target semantics. |73| Canonical logical AST | supported optimizer entry point with schema | Not physical query-performance tuning. |7475Read [the operation map](references/operations.md) before choosing parsing,76building, scope analysis, transformation, optimization, or generation APIs.7778## Canonical strict transpilation7980```python81import sqlglot82from sqlglot import ErrorLevel838485def transpile_script(sql: str, source: str, target: str) -> list[str]:86 if not source or not target:87 raise ValueError("source and target dialects are required")88 return sqlglot.transpile(89 sql,90 read=source,91 write=target,92 unsupported_level=ErrorLevel.RAISE,93 pretty=True,94 )95```9697The list preserves the possibility of multiple statements and rejects known98unsupported generation rather than warning and emitting a degraded query.99Callers that accept only one statement must check `len(result) == 1`. Reparse100each output with `read=target`; execute representative queries in the target101engine before claiming semantic equivalence.102103## Canonical structural inspection104105```python106from sqlglot import exp, parse_one107108109def selected_columns(sql: str, dialect: str) -> tuple[str, ...]:110 tree = parse_one(sql, read=dialect)111 if not isinstance(tree, exp.Select):112 raise ValueError(f"expected SELECT, got {type(tree).__name__}")113 return tuple(node.sql(dialect=dialect) for node in tree.expressions)114```115116This inspects projection expressions rather than regex-matching commas or117column-like tokens. It does not claim to resolve `*`, aliases, or source columns;118that requires scopes and often schema qualification.119120## High-risk rules121122### Dialects and semantics123124- Always specify the source dialect when known and target dialect when125 generating. Identifier case, quoting, function names, date arithmetic, casts,126 arrays, JSON, and null behavior can differ.127- Treat unsupported warnings as failures for correctness-critical translation.128 A successful parse means the syntax was represented, not that every target129 construct has an equivalent.130- Type-sensitive transpilation and optimization need schema information. If the131 catalog is unavailable, preserve the construct or return an uncertainty;132 never fabricate column types.133- SQLGlot optimization is logical canonicalization, not target physical-plan134 tuning. Benchmark or inspect the actual engine for performance claims.135136### AST mutation and resolution137138- Match nodes by class and semantic arguments, not `str(node)`. Preserve139 comments and formatting only to the degree the selected generator supports;140 regenerated SQL is not a textual round trip.141- Copy before mutation when a function promises not to alter its input. Test142 nested subqueries, CTE shadowing, aliases, quoted identifiers, stars, and143 correlated references.144- `find_all(exp.Column)` finds syntax nodes but does not resolve which table145 supplies them. Use scope/qualification analysis for lineage or authorization.146- Construct identifiers with identifier helpers/nodes and values with literals147 or executor placeholders. Never turn untrusted text into an expression by148 parsing it merely to avoid quoting.149150### Security boundary151152Parsing SQL does not make it safe to execute. If only read-only queries are153allowed, inspect every parsed statement and all prohibited node classes, reject154commands the parser represents generically, enforce database credentials and155resource limits, and bind data values at execution. Treat AST allowlisting as156defense in depth, not the sole authorization layer.157158Run [the installed-API inspector](scripts/inspect_sqlglot.py), read [version and API159grounding](references/version-grounding.md), then apply [the AST verification160matrix](references/testing.md).161162## Completion gate163164Do not declare completion until source and target dialects are explicit or a165documented unknown branch exists; multi-statement input cannot bypass checks;166allowed root/node classes are enforced; mutations honor ownership; identifiers167and values remain distinct; schema-dependent transforms have real schema;168unsupported generation is surfaced; target output reparses; nested/quoted/CTE169fixtures pass; and semantic or performance claims are verified in the target170database rather than inferred from SQLGlot alone.171172## References173174- [AST and dialect model](references/object-model.md)175- [Operation map](references/operations.md)176- [Version and API grounding](references/version-grounding.md)177- [Testing SQLGlot transformations](references/testing.md)