PyPIDataupdated 06 Aug 2026

sqlglot

Item: sqlglot
Rating: 4
Author: Keyoor

SQLGlot turns SQL text into a Python expression tree, lets you inspect or rewrite that tree, and prints it back out in any of more than 30 dialects. That single trick covers a surprising range of jobs: transpile a Spark query to DuckDB, pretty-print and normalize SQL in a linter, pull the table and column references out of a warehouse query for lineage, rewrite table names when promoting a query from staging to production, or canonicalize two queries to check whether they mean the same thing. It has no required runtime dependencies, is written in pure Python with an optional mypyc-compiled build, and ships a toy in-memory execution engine that runs SQL over lists of dicts. It is the SQL layer under Ibis, Apache Superset, Dagster, dlt, and SQLMesh.

sqlglot · v30.15.0 · 19.2M/wk · ★ 9,501 · pushed 06 Aug 2026 · tobymao/sqlglot · docs · reviewed by Keyoor

Verdict

The default answer for anything that needs to read, rewrite, or translate SQL in Python, with dialect coverage nothing else in the language matches. Treat the version number as load-bearing: pin it exactly, because the project's own scheme puts breaking changes in minor releases.

API stability2/5The README states MINOR is incremented for backwards-incompatible changes and MAJOR for significant ones; at version 30 with releases landing weekly, expression class names, dialect output, and optimizer behavior all shift under you unless you pin exactly.

Docs4/5The README is one of the better ones in Python data tooling (FAQ that admits the parser is lenient, per-dialect support tiers, real benchmarks) and the repo carries written primers on the AST and semantic diffing, but sqlglot.com itself is pdoc-generated API reference with little narrative for the optimizer and lineage modules.

Maintenance5/5Pushed the same day this was written, with only 1 open issue (5 counting PRs) against 9.5k stars, backed by Tobiko Data who depend on it for SQLMesh, and a test suite that gates every dialect change.

Ecosystem5/5Roughly 19.2 million weekly downloads, and it is the SQL layer inside Ibis, Apache Superset, Dagster, dlt, SQLFrame, and SQLMesh; third-party dialects can now register through entry points as plugins.

Use it if

You need to move SQL between engines: reading Snowflake and writing DuckDB, or migrating a pile of Hive queries to Trino, where date functions and identifier quoting differ per dialect
You are building tooling over SQL: a lineage graph, a query cost guard, an access-control rewriter that injects a WHERE clause, or a linter that reformats queries in CI
You want the tables and columns a query touches without running it, including through CTEs and subqueries, which parse_one().find_all(exp.Table) gives you in three lines
You need to normalize SQL for comparison or caching: the optimizer canonicalizes aliases, quoting, and boolean logic so two spellings of the same query hash identically
You want zero install friction inside a library you ship: no compiled extension required, no dependencies, so it does not fight your users' environment

Skip it if

You expect a validator: the parser is deliberately lenient and the FAQ says so outright, so invalid SQL frequently parses without complaint and only blows up when a real engine sees it; use sqlfluff if you want linting with opinions
You cannot absorb breaking changes: the project's stated versioning makes MINOR the backwards-incompatible bump, and it moves fast enough to be on version 30, so pin an exact version and read the release notes before every upgrade
You just want to pretty-print or split a SQL file: sqlparse is smaller and does not build a full typed AST, and SQLGlot will silently rewrite your casing, quoting, and comment placement because it regenerates from the tree rather than editing text
Your workload is millions of parses per second: pure Python is slower than the Rust-backed sqloxide, and the sqlglot[c] extra that closes most of that gap disables runtime subclassing of the compiled classes, so you cannot have both speed and a custom dialect
Your dialect is only community-supported or a plugin: the README grades dialects Official, Community, and Plugin, and anything outside the Official list gets lower priority on bug fixes or is maintained by a third party entirely
You want an actual query engine: sqlglot.executor exists but the README says plainly it is not supposed to be fast, and it is meant for unit tests, not production

Setup reality

pip install sqlglot is about as painless as Python gets: pure Python, no required dependencies, Python 3.9 or newer. Two optional pieces matter. pip install "sqlglot[c]" pulls the mypyc-compiled build that the project benchmarks at roughly 3-5x faster, but it falls back to compiling from source when no wheel matches your platform, and it disables runtime subclassing of the compiled classes, so a codebase with a custom Dialect subclass has to stay on pure Python. python-dateutil is optional and only affects whether the optimizer can simplify literal interval arithmetic. The real setup cost is conceptual: almost every call needs an explicit dialect. Without read=, parse_one assumes the SQLGlot superset dialect and will happily accept or mangle engine-specific syntax; without write=, you get SQLGlot's own output, not your target engine's. Type-sensitive transpilation additionally needs a schema passed through qualify or annotate_types, which is not on by default.

Patterns

Translate a query from one engine to anothertranspile-between-dialects

import sqlglot

sqlglot.transpile("SELECT EPOCH_MS(1618088028295)", read="duckdb", write="hive")[0]
# 'SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))'

sqlglot.parse_one("SELECT DATEADD(day, 1, x) FROM t", dialect="tsql").sql(dialect="postgres")
# "SELECT x + INTERVAL '1 DAY' FROM t"

transpile returns a list because the input can hold several statements; the [0] is not optional. Always pass both read and write. Leaving read off means the parser assumes SQLGlot's own superset dialect, which is the single most common cause of wrong output.

Find the tables and columns a query touchesextract-tables-and-columns

from sqlglot import exp, parse_one

ast = parse_one("SELECT a, b + 1 AS c FROM db.tbl JOIN other o ON 1 = 1")

[t.sql() for t in ast.find_all(exp.Table)]     # ['db.tbl', 'other AS o']
[c.alias_or_name for c in ast.find_all(exp.Column)]  # ['a', 'b']
ast.find(exp.Table).name                        # 'tbl'

find_all walks the whole tree including CTEs and subqueries, so it reports CTE names as tables too. .name gives the bare identifier, .sql() gives the qualified form with alias; pick deliberately or your lineage output will mix the two.

Pretty-print and force identifier quotingformat-and-quote

import sqlglot

print(sqlglot.transpile("select a from foo", write="spark",
                        identify=True, pretty=True)[0])
# SELECT
#   `a`
# FROM `foo`

SQLGlot regenerates SQL from the tree, so your original casing, indentation, and comment positions are not preserved; comments survive on a best-effort basis only. If byte-for-byte fidelity matters, use a formatter that edits text instead.

Compose a query with the builder APIbuild-sql-programmatically

from sqlglot import condition, parse_one, select

where = condition("x = 1").and_("y = 2")
select("a", "b").from_("t").where(where).sql()
# 'SELECT a, b FROM t WHERE x = 1 AND y = 2'

# builders also work on an already-parsed query
parse_one("SELECT x FROM y").from_("z").sql()
# 'SELECT x FROM z'

Builder methods mutate a copy by default and return the expression, so chain them. from_() replaces the existing FROM rather than adding to it, which is how you retarget a query; use .join() to add tables.

Rewrite every matching node in the treerewrite-ast

from sqlglot import exp, parse_one

def mask_pii(node):
    if isinstance(node, exp.Column) and node.name == "ssn":
        return parse_one("SHA256(ssn)")
    return node

parse_one("SELECT ssn, name FROM users").transform(mask_pii).sql()
# 'SELECT SHA256(ssn), name FROM users'

transform visits every node bottom-up and replaces whatever the function returns, so returning node unchanged is mandatory for the default branch. Returning a newly parsed expression drops any alias that was on the original node; re-attach it with exp.alias_ if you need one.

Repoint a query at different tablesreplace-table-names

from sqlglot import exp, parse_one

ast = parse_one("SELECT * FROM a JOIN b ON a.id = b.id")
exp.replace_tables(ast, {"a": "prod.a", "b": "prod.b"}).sql()
# 'SELECT * FROM prod.a /* a */ JOIN prod.b /* b */ ON a.id = b.id'

The original name is left behind as a comment, which surprises people diffing output; pass copy=False to mutate in place, and strip comments with .sql(comments=False) if the trailing /* a */ is noise. Mapping keys are matched dialect-aware, so pass dialect= for case-insensitive engines like Snowflake.

Canonicalize a query against a schemaqualify-and-optimize

from sqlglot import parse_one
from sqlglot.optimizer import optimize
from sqlglot.optimizer.qualify import qualify

schema = {"x": {"a": "INT", "b": "INT"}}

qualify(parse_one("SELECT a FROM x"), schema=schema).sql()
# 'SELECT "x"."a" AS "a" FROM "x" AS "x"'

optimize(parse_one("SELECT a FROM x WHERE 1 = 1 AND a = 1"), schema=schema).sql()
# 'SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."a" = 1'

qualify expands stars, adds table prefixes, and names every projection; optimize runs it plus simplification and predicate pushdown. Neither runs by default because both cost time and both need a schema to be correct. Optimized SQL is for machines to compare, not for humans to read.

Trace a column back to its sourcecolumn-lineage

from sqlglot.lineage import lineage

node = lineage("c", "SELECT c FROM (SELECT b AS c FROM t) s")
[n.name for n in node.walk()]     # ['c', 's.c', 't.b']
[d.name for d in node.downstream] # ['s.c']

Pass schema= when the query has stars or ambiguous columns, or lineage cannot resolve where the column came from. walk() flattens the whole chain; downstream gives you one hop, which is what you want when building a graph node by node.

Catch syntax errors with position informationhandle-parse-errors

import sqlglot
from sqlglot.errors import ParseError

try:
    sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")
except ParseError as e:
    print(e.errors[0])
    # {'description': 'Expecting )', 'line': 1, 'col': 34,
    #  'start_context': 'SELECT foo FROM (SELECT baz FROM ', 'highlight': 't', ...}

e.errors is structured, so you can map it onto editor squiggles. Remember the parser is lenient by design: plenty of SQL that a real engine rejects will parse here with no error at all, so a clean parse is not validation.

Fail loudly when a translation is lossyunsupported-error-level

import sqlglot

# default: warns and does a best-effort translation
sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive")
# ['SELECT APPROX_COUNT_DISTINCT(a) FROM foo']  (accuracy argument silently dropped)

sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive",
                  unsupported_level=sqlglot.ErrorLevel.RAISE)
# sqlglot.errors.UnsupportedError: Argument 'accuracy' is not supported ...

The default silently changes query semantics and only logs a warning. If you are transpiling production queries in a batch job, set unsupported_level to RAISE or IMMEDIATE so lossy translations become failures you review instead of wrong numbers you ship.

Subclass a dialect to add engine-specific syntaxcustom-dialect

from sqlglot import exp, transpile
from sqlglot.dialects.dialect import Dialect
from sqlglot.generator import Generator
from sqlglot.tokens import Tokenizer, TokenType

class Custom(Dialect):
    class Tokenizer(Tokenizer):
        KEYWORDS = {**Tokenizer.KEYWORDS, "INT64": TokenType.BIGINT}

    class Generator(Generator):
        TYPE_MAPPING = {exp.DataType.Type.BIGINT: "INT64"}

transpile("SELECT CAST(x AS BIGINT)", write="custom")[0]
# 'SELECT CAST(x AS INT64)'

The dialect registers itself by lowercased class name the moment it is defined, so importing the module is enough. This does not work when sqlglot[c] is installed, because runtime subclassing of the mypyc-compiled classes is disabled; custom dialects force you onto the pure Python build or an entry-point plugin package.

Diff two queries by meaning, not textsemantic-diff

from sqlglot import diff, parse_one

changes = diff(parse_one("SELECT a + b, c, d"), parse_one("SELECT c, a - b, d"))
[type(c).__name__ for c in changes]
# ['Remove', 'Insert', 'Move', 'Keep', ...]

Reordering projections shows up as Move and Keep rather than a rewrite, which is the point: it ignores formatting and ordering noise that a text diff flags. The order of Keep and Move entries is not stable between runs, so sort before asserting on it in tests.

Alternatives

Package	Registry	Pick it when
sqlparse	PyPI	You only need to split statements, reformat, or strip comments and do not want a full AST or dialect model.
sqlfluff	PyPI	You want an opinionated SQL linter and auto-fixer for CI with configurable rules, not a transpiler library.
sqloxide	PyPI	Parsing speed dominates and Rust bindings are acceptable; you get an AST as nested dicts but no transpilation between dialects.
ibis-framework	PyPI	You want to write queries in Python dataframe style and have them compiled to many backends; it uses SQLGlot underneath for generation.