Skip to main content

A SQL parser.

Project description

sqltree

sqltree is an experimental parser for SQL, providing a syntax tree for SQL queries. Possible use cases include:

  • Static analysis (for example, to validate column names)
  • Translating queries to another SQL dialect
  • Autoformatting

sqltree can parse queries:

$ python -m sqltree "SELECT * FROM x WHERE x = 3"
Select(select_exprs=[SelectExpr(expr=Star(), alias=None)], table=Identifier(text='x'), conditions=BinOp(left=Identifier(text='x'), op=Punctuation(text='='), right=IntegerLiteral(value=3)))

And format them:

$  python -m sqltree.formatter "SELECT * from x where x=3"
SELECT *
FROM x
WHERE x = 3

SQL is a big language with a complicated grammar that varies significantly between database vendors. sqltree is designed to be flexible enough to parse the full syntax supported by different databases, but I am prioritizing constructs used in my use cases for the parser. So far, that has meant a focus on parsing MySQL 8 queries. Further syntax will be added as I have time.

Features

Useful features of sqltree include:

Placeholder support

sqltree supports placeholders such as %s or ? in various positions in the query, so that queries using such placeholders can be formatted and analyzed.

$ python -m sqltree.formatter 'select * from x where y = 3 %(limit)s'
SELECT *
FROM x
WHERE y = 3
%(limit)s

Better error messages

sqltree's handwritten parser often produces better error messages than MySQL itself. For example:

$ mysql
mysql> show replicca status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'replicca status' at line 1
$ python -m sqltree 'show replicca status'
Unexpected 'replicca' (expected one of REPLICA, SLAVE, REPLICAS, TABLES, TABLE, TRIGGERS, VARIABLES, STATUS, COUNT, WARNINGS, ERRORS, COLUMNS, FIELDS, INDEX, INDEXES, KEYS)
0: show replicca status
        ^^^^^^^^

API

  • sqltree.sqltree: parse a SQL query and return the parse tree. See sqltree.parser for the possible parse nodes.
  • sqltree.formatter.format: reformat a SQL query.
  • sqltree.tools.get_tables: get the tables referenced in a SQL query.

More detailed documentation to follow.

Requirements

sqltree runs on Python 3.6 and up and it has no dependencies.

Using the fixit rule

sqltree embeds a fixit rule for formatting SQL. Here is how to use it:

  • Install fixit if you don't have it yet
    • pip install fixit
    • python -m fixit.cli.init_config
  • Run python -m fixit.cli.apply_fix --rules sqltree.fixit.SqlFormatRule path/to/your/code

Changelog

Version 0.3.0 (July 12, 2022)

  • Add ANSI SQL as a dialect
  • Support escaping quotes by doubling them in string literals
  • Support scientific notation with a negative exponent
  • Fix formatting for quoted identifiers that contain non-alphanumeric characters
  • Support the unary NOT operator
  • Fix formatting of LEFT JOIN and similar queries

Version 0.2.0 (June 24, 2022)

  • Support SELECT ... INTO syntax
  • Support SET TRANSACTION syntax
  • Support a MOD B and a DIV b syntax
  • Support GROUP_CONCAT() syntax

Version 0.1.0 (June 13, 2022)

  • Initial release

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqltree-0.3.0.tar.gz (36.8 kB view details)

Uploaded Source

Built Distribution

sqltree-0.3.0-py2.py3-none-any.whl (39.2 kB view details)

Uploaded Python 2 Python 3

File details

Details for the file sqltree-0.3.0.tar.gz.

File metadata

  • Download URL: sqltree-0.3.0.tar.gz
  • Upload date:
  • Size: 36.8 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.1 CPython/3.9.13

File hashes

Hashes for sqltree-0.3.0.tar.gz
Algorithm Hash digest
SHA256 49c8e5912789d06825921dcc3711710a6a0927bce4d3f1afb78587cf12edc950
MD5 c3597847863d88810e1c8e94d2d7b60f
BLAKE2b-256 fccbd554cc68e9902fbbb209c0c239dc8e1401f3b2f233fd5999690ed9498b05

See more details on using hashes here.

Provenance

File details

Details for the file sqltree-0.3.0-py2.py3-none-any.whl.

File metadata

  • Download URL: sqltree-0.3.0-py2.py3-none-any.whl
  • Upload date:
  • Size: 39.2 kB
  • Tags: Python 2, Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/4.0.1 CPython/3.9.13

File hashes

Hashes for sqltree-0.3.0-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 0ef6fdec6e6a1dbb7c5b2682752e954e886edacfc7e717f389e7fdd25f2e8acf
MD5 7536303d4efeb13baec9ea95c86773ce
BLAKE2b-256 31734ff58c610efbe00155bf7d5f7c739900b8f4147c15dacc07ba2f41b452b1

See more details on using hashes here.

Provenance

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page