Table of Contents

Install

pip3 install"sqlglot[rs]"# Without Rust tokenizer (slower):# pip3 install sqlglot

# Optionally prefix with UV=1 to use uv for the installationmake install

# Optionally prefix with UV=1 to use uv for the installationmake install-dev

Versioning

Get in Touch

FAQ

Examples

Formatting and Transpiling

importsqlglotsqlglot.transpile("SELECT EPOCH_MS(1618088028295)",read="duckdb",write="hive")[0]

'SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))'

importsqlglotsqlglot.transpile("SELECT STRFTIME(x, '%y-%-m-%S')",read="duckdb",write="hive")[0]

"SELECT DATE_FORMAT(x, 'yy-M-ss')"

importsqlglot# Spark SQL requires backticks (`) for delimited identifiers and uses `FLOAT` over `REAL`sql="""WITH baz AS (SELECT a, c FROM foo WHERE a = 1) SELECT f.a, b.b, baz.c, CAST("b"."a" AS REAL) d FROM foo f JOIN bar b ON f.a = b.a LEFT JOIN baz ON f.a = baz.a"""# Translates the query into Spark SQL, formats it, and delimits all of its identifiersprint(sqlglot.transpile(sql,write="spark",identify=True,pretty=True)[0])

WITH`baz`AS (SELECT`a`,`c`FROM`foo`WHERE`a`=1)SELECT`f`.`a`,`b`.`b`,`baz`.`c`,  CAST(`b`.`a`AS FLOAT)AS`d`FROM`foo`AS`f`JOIN`bar`AS`b`ON`f`.`a`=`b`.`a`LEFT JOIN`baz`ON`f`.`a`=`baz`.`a`

sql="""/* multi   line   comment*/SELECT  tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,  CAST(x AS SIGNED), # comment 3  y               -- comment 4FROM  bar /* comment 5 */,  tbl #          comment 6"""# Note: MySQL-specific comments (`#`) are converted into standard syntaxprint(sqlglot.transpile(sql,read='mysql',pretty=True)[0])

/* multi   line   comment*/SELECTtbl.cola/* comment 1*/+tbl.colb/* comment 2*/,  CAST(xASINT),/* comment 3*/  y/* comment 4*/FROM bar/* comment 5*/, tbl/*          comment 6*/

Metadata

fromsqlglotimportparse_one,exp# print all column references (a and b)forcolumninparse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Column):print(column.alias_or_name)# find all projections in select statements (a and c)forselectinparse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Select):forprojectioninselect.expressions:print(projection.alias_or_name)# find all tables (x, y, z)fortableinparse_one("SELECT * FROM x JOIN y JOIN z").find_all(exp.Table):print(table.name)

Parser Errors

importsqlglotsqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")

sqlglot.errors.ParseError: Expecting ). Line 1, Col: 34.  SELECT foo FROM (SELECT baz FROM t                                   ~

importsqlglot.errorstry:sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")exceptsqlglot.errors.ParseErrorase:print(e.errors)

[{'description':'Expecting )','line':1,'col':34,'start_context':'SELECT foo FROM (SELECT baz FROM ','highlight':'t','end_context':'','into_expression':None}]

Unsupported Errors

importsqlglotsqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo",read="presto",write="hive")

APPROX_COUNT_DISTINCT does not support accuracy'SELECT APPROX_COUNT_DISTINCT(a) FROM foo'

importsqlglotsqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo",read="presto",write="hive",unsupported_level=sqlglot.ErrorLevel.RAISE)

sqlglot.errors.UnsupportedError: APPROX_COUNT_DISTINCT does not support accuracy

Build and Modify SQL

fromsqlglotimportselect,conditionwhere=condition("x=1").and_("y=1")select("*").from_("y").where(where).sql()

'SELECT * FROM y WHERE x = 1 AND y = 1'

fromsqlglotimportparse_oneparse_one("SELECT x FROM y").from_("z").sql()

'SELECT x FROM z'

fromsqlglotimportexp,parse_oneexpression_tree=parse_one("SELECT a FROM x")deftransformer(node):ifisinstance(node,exp.Column)andnode.name=="a":returnparse_one("FUN(a)")returnnodetransformed_tree=expression_tree.transform(transformer)transformed_tree.sql()

'SELECT FUN(a) FROM x'

SQL Optimizer

importsqlglotfromsqlglot.optimizerimportoptimizeprint(optimize(sqlglot.parse_one("""            SELECT A OR (B OR (C AND D))            FROM x            WHERE Z = date '2021-01-01' + INTERVAL '1' month OR 1 = 0        """),schema={"x": {"A":"INT","B":"INT","C":"INT","D":"INT","Z":"STRING"}}    ).sql(pretty=True))

SELECT  ("x"."a"<>0OR"x"."b"<>0OR"x"."c"<>0  )AND ("x"."a"<>0OR"x"."b"<>0OR"x"."d"<>0  )AS"_col_0"FROM"x"AS"x"WHERE  CAST("x"."z"ASDATE)= CAST('2021-02-01'ASDATE)

AST Introspection

fromsqlglotimportparse_oneprint(repr(parse_one("SELECT a + 1 AS z")))

Select(expressions=[Alias(this=Add(this=Column(this=Identifier(this=a,quoted=False)),expression=Literal(this=1,is_string=False)),alias=Identifier(this=z,quoted=False))])

AST Diff

fromsqlglotimportdiff,parse_onediff(parse_one("SELECT a + b, c, d"),parse_one("SELECT c, a - b, d"))

[Remove(expression=Add(this=Column(this=Identifier(this=a,quoted=False)),expression=Column(this=Identifier(this=b,quoted=False)))),Insert(expression=Sub(this=Column(this=Identifier(this=a,quoted=False)),expression=Column(this=Identifier(this=b,quoted=False)))),Keep(source=Column(this=Identifier(this=a,quoted=False)),target=Column(this=Identifier(this=a,quoted=False))),  ...]

Custom Dialects

fromsqlglotimportexpfromsqlglot.dialects.dialectimportDialectfromsqlglot.generatorimportGeneratorfromsqlglot.tokensimportTokenizer,TokenTypeclassCustom(Dialect):classTokenizer(Tokenizer):QUOTES= ["'",'"']IDENTIFIERS= ["`"]KEYWORDS= {**Tokenizer.KEYWORDS,"INT64":TokenType.BIGINT,"FLOAT64":TokenType.DOUBLE,        }classGenerator(Generator):TRANSFORMS= {exp.Array:lambdaself,e:f"[{self.expressions(e)}]"}TYPE_MAPPING= {exp.DataType.Type.TINYINT:"INT64",exp.DataType.Type.SMALLINT:"INT64",exp.DataType.Type.INT:"INT64",exp.DataType.Type.BIGINT:"INT64",exp.DataType.Type.DECIMAL:"NUMERIC",exp.DataType.Type.FLOAT:"FLOAT64",exp.DataType.Type.DOUBLE:"FLOAT64",exp.DataType.Type.BOOLEAN:"BOOL",exp.DataType.Type.TEXT:"STRING",        }print(Dialect["custom"])

<class '__main__.Custom'>

SQL Execution

fromsqlglot.executorimportexecutetables= {"sushi": [        {"id":1,"price":1.0},        {"id":2,"price":2.0},        {"id":3,"price":3.0},    ],"order_items": [        {"sushi_id":1,"order_id":1},        {"sushi_id":1,"order_id":1},        {"sushi_id":2,"order_id":1},        {"sushi_id":3,"order_id":2},    ],"orders": [        {"id":1,"user_id":1},        {"id":2,"user_id":2},    ],}execute("""    SELECT      o.user_id,      SUM(s.price) AS price    FROM orders o    JOIN order_items i      ON o.id = i.order_id    JOIN sushi s      ON i.sushi_id = s.id    GROUP BY o.user_id    """,tables=tables)

user_idprice14.023.0

Used By

Documentation

make docs-serve

Run Tests and Lint

make style  # Only linter checksmake unit   # Only unit tests (or unit-rs, to use the Rust tokenizer)make test   # Unit and integration tests (or test-rs, to use the Rust tokenizer)make check  # Full test suite & linter checks

Benchmarks

make bench            # Run parsing benchmarkmake bench-optimize   # Run optimization benchmark

Optional Dependencies

x+ interval'1' month

Supported Dialects