sqlglot: Parse and Transpile SQL in Python (2026)
Moving analytics SQL between MySQL, Postgres, DuckDB and Spark usually means rewriting date functions, null-coalescing helpers and string concatenation by hand. sqlglot is a pure-Python SQL parser and transpiler that turns a query into an AST, pretty-prints it, and rewrites dialect-specific functions for you. This tutorial was tested with sqlglot 30.23.0 on Python 3.13.5 and shows real parse, transpile and table-qualify output from the demos below.
Why sqlglot in 2026?
- One library covers dozens of dialects (MySQL, Postgres, DuckDB, Spark, BigQuery, Snowflake and more).
- Parse once into an AST, then pretty-print, lint, optimize or rewrite without string surgery.
- Transpile dialect helpers such as
DATE_FORMAT,IFNULLandCONCATinto the target engine's idioms. - Walk the AST to extract tables, columns and joins for lineage or migration tooling.
- Pairs cleanly with local analytics stacks such as DuckDB vs Polars benchmarks and dataframe adapters like Narwhals for Pandas and Polars.
Install
pip install sqlglotCheck your versions:
import sys, sqlglot
print("Python", sys.version.split()[0], "| sqlglot", sqlglot.__version__)
# Python 3.13.5 | sqlglot 30.23.0Step 1: Parse SQL into an AST and pretty-print
Pass a SQL string to sqlglot.parse_one() with a read dialect. The return value is an expression tree. Call .sql(pretty=True) to format it, and use find_all(exp.Table) to list every table the query touches.
import sys
import sqlglot
from sqlglot import exp
print("Python", sys.version.split()[0], "| sqlglot", sqlglot.__version__)
sql = """
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10
"""
ast = sqlglot.parse_one(sql, read="postgres")
print("--- AST root type ---")
print(type(ast).__name__)
print("--- Pretty SQL (Postgres) ---")
print(ast.sql(dialect="postgres", pretty=True))
print("--- Tables referenced ---")
tables = [t.name for t in ast.find_all(exp.Table)]
print(tables)
print("--- Columns in SELECT ---")
cols = [c.alias_or_name for c in ast.expressions]
print(cols)
Real output (parse)
Running python parse_pretty.py printed:
Python 3.13.5 | sqlglot 30.23.0
--- AST root type ---
Select
--- Pretty SQL (Postgres) ---
SELECT
u.id,
u.name,
COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o
ON o.user_id = u.id
WHERE
u.created_at >= '2026-01-01'
GROUP BY
u.id,
u.name
HAVING
COUNT(o.id) > 5
ORDER BY
order_count DESC
LIMIT 10
--- Tables referenced ---
['users', 'orders']
--- Columns in SELECT ---
['id', 'name', 'order_count']
The root node is a Select. Pretty-printing keeps aliases, joins and the HAVING clause intact, and the table walk returns users and orders without regex hacks.
Step 2: Transpile across dialects
MySQL helpers such as DATE_FORMAT, IFNULL, CONCAT and CURDATE() do not exist (or differ) in Postgres and DuckDB. sqlglot.transpile() rewrites them in one call. The same script also qualifies bare table names with a catalog and database, and extracts tables from a multi-statement script.
import sqlglot
from sqlglot import exp
from sqlglot.optimizer.qualify_tables import qualify_tables
mysql_sql = """
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
IFNULL(amount, 0) AS amount,
CONCAT(first_name, ' ', last_name) AS full_name
FROM sales
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
LIMIT 100
"""
print("=== Source (MySQL) ===")
print(mysql_sql.strip())
print("\n=== Transpile MySQL -> Postgres ===")
print(sqlglot.transpile(mysql_sql, read="mysql", write="postgres", pretty=True)[0])
print("\n=== Transpile MySQL -> DuckDB ===")
print(sqlglot.transpile(mysql_sql, read="mysql", write="duckdb", pretty=True)[0])
print("\n=== Transpile MySQL -> Spark ===")
print(sqlglot.transpile(mysql_sql, read="mysql", write="spark", pretty=True)[0])
# Qualify tables: inject catalog/db prefixes
raw = "SELECT id, name FROM users JOIN orders ON users.id = orders.user_id WHERE status = 'paid'"
ast = sqlglot.parse_one(raw, read="postgres")
qualified = qualify_tables(ast, db="analytics", catalog="warehouse", dialect="postgres")
print("\n=== Qualify tables (add catalog/db prefixes) ===")
print("Before:", raw)
print("After:", qualified.sql(dialect="postgres", pretty=True))
# Extract all table names from a multi-statement script
script = """
SELECT * FROM raw.events;
INSERT INTO mart.daily_users SELECT user_id FROM raw.events;
"""
print("\n=== Tables in a multi-statement script ===")
for stmt in sqlglot.parse(script, read="postgres"):
if stmt is None:
continue
names = [t.sql(dialect="postgres") for t in stmt.find_all(exp.Table)]
print(type(stmt).__name__, "->", names)
Real output (transpile)
=== Source (MySQL) ===
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
IFNULL(amount, 0) AS amount,
CONCAT(first_name, ' ', last_name) AS full_name
FROM sales
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
LIMIT 100
=== Transpile MySQL -> Postgres ===
SELECT
TO_CHAR(CAST(created_at AS TIMESTAMP), 'YYYY-MM') AS month,
COALESCE(amount, 0) AS amount,
first_name || ' ' || last_name AS full_name
FROM sales
WHERE
created_at >= CURRENT_DATE - INTERVAL '30 DAY'
LIMIT 100
=== Transpile MySQL -> DuckDB ===
SELECT
STRFTIME(CAST(created_at AS TIMESTAMP), '%Y-%m') AS month,
COALESCE(amount, 0) AS amount,
first_name || ' ' || last_name AS full_name
FROM sales
WHERE
created_at >= CURRENT_DATE - INTERVAL '30' DAY
LIMIT 100
=== Transpile MySQL -> Spark ===
SELECT
DATE_FORMAT(CAST(created_at AS TIMESTAMP), 'yyyy-MM') AS month,
COALESCE(amount, 0) AS amount,
CONCAT(first_name, ' ', last_name) AS full_name
FROM sales
WHERE
created_at >= CURRENT_DATE - INTERVAL '30' DAY
LIMIT 100
=== Qualify tables (add catalog/db prefixes) ===
Before: SELECT id, name FROM users JOIN orders ON users.id = orders.user_id WHERE status = 'paid'
After: SELECT
id,
name
FROM warehouse.analytics.users AS users
JOIN warehouse.analytics.orders AS orders
ON users.id = orders.user_id
WHERE
status = 'paid'
=== Tables in a multi-statement script ===
Select -> ['raw.events']
Insert -> ['mart.daily_users', 'raw.events']
Notice how MySQL DATE_FORMAT(..., '%Y-%m') becomes Postgres TO_CHAR, DuckDB STRFTIME, and Spark keeps DATE_FORMAT with a Spark-style pattern. IFNULL becomes COALESCE, and CONCAT becomes the || operator on Postgres and DuckDB. qualify_tables injects warehouse.analytics prefixes without changing column logic.
How the AST helps production code
- Parse with the source dialect, then
transpile(..., write="target")when migrating warehouses or writing dual-engine tests. - Walk
exp.Table,exp.Columnandexp.Joinnodes to build lineage graphs or validate that a query only touches approved schemas. - Use
pretty=Truein CI diffs so reviewers see readable SQL instead of one long line. - Keep dialect names explicit (
read="mysql",write="duckdb") so accidental defaults never silently change semantics. - When you also serialize query plans or metrics as JSON, a fast encoder such as orjson for Python keeps the pipeline snappy.
Production tips
- Always set both
readandwritedialects; silent defaults can change date math. - Round-trip a few golden queries in tests: parse, transpile, and assert the expected target SQL.
- Prefer AST transforms (
qualify_tables, custom visitors) over string replace for rewrites. - Catch
sqlglot.errors.ParseErrorandOptimizeErrorand surface the original SQL in logs. - Pin
sqlglotin requirements; dialect rewrites evolve between major versions.
Conclusion
sqlglot turns dialect pain into a short Python pipeline: parse to an AST, pretty-print for humans, transpile for the target engine, and walk tables for tooling. With sqlglot 30.23.0 on Python 3.13 you can move MySQL analytics SQL to Postgres, DuckDB or Spark without hand-editing every date function.