Skip to main content

Parsing utilities to validate and split SQL statements for CrateDB.

Project description

CrateDB SQL Parser for Python

License PyPI version PyPI downloads

Tests Test coverage Python versions

Status

This package provides utilities to validate and split SQL statements specifically designed for CrateDB.

It is built upon CrateDB's antlr4 grammar, ensuring accurate parsing tailored to CrateDB's SQL dialect.

It draws inspiration from sqlparse.

Installation.

pip install cratedb-sqlparse

Usage.

Simple example

from cratedb_sqlparse import sqlparse

query = """
    SELECT * FROM SYS.SHARDS;
    INSERT INTO doc.tbl VALUES (1);
"""
statements = sqlparse(query)

print(len(statements))
# 2

select_query = statements[0]

print(select_query.query)
# 'SELECT * FROM SYS.SHARDS'

print(select_query.type)
# 'SELECT'

print(select_query.tree)
# (statement (query (queryNoWith (queryTerm (querySpec SELECT (selectItem *) FROM (relation (aliasedRelation (relationPrimary (table (qname (ident (unquotedIdent SYS)) . (ident (unquotedIdent (nonReserved SHARDS)))))))))))))

Exceptions and errors.

By default exceptions are stored in statement.exception

from cratedb_sqlparse import sqlparse

query = """
SELECT COUNT(*) FROM doc.tbl f HERE f.id = 1;

INSERT INTO doc.tbl VALUES (1, 23, 4);
"""
statements = sqlparse(query)
stmt = statements[0]

if stmt.exception:
    print(stmt.exception.error_message)
    # InputMismatchException[line 2:31 mismatched input 'HERE' expecting {<EOF>, ';'}]

    print(stmt.exception.original_query_with_error_marked)
    # SELECT COUNT(*) FROM doc.tbl f HERE f.id = 1;
    #                                ^^^^
    # 
    # INSERT INTO doc.tbl VALUES (1, 23, 4);

    print(stmt.exception.offending_token.text)
    # HERE

In some situations, you might want sqlparse to raise an exception.

You can set raise_exception to True

from cratedb_sqlparse import sqlparse

sqlparse('SELECT COUNT(*) FROM doc.tbl f WHERE .id = 1;', raise_exception=True)

# cratedb_sqlparse.parser.ParsingException: NoViableAltException[line 1:37 no viable alternative at input 'SELECT COUNT(*) FROM doc.tbl f WHERE .']

Catch the exception:

from cratedb_sqlparse import sqlparse, ParsingException

try:
    t = sqlparse('SELECT COUNT(*) FROM doc.tbl f WHERE .id = 1;', raise_exception=True)[0]
except ParsingException:
    print('Catched!')

Note:

It will only raise the first exception it finds, even if you pass in several statements.

Query metadata.

Query metadata can be read with statement.metadata

from cratedb_sqlparse import sqlparse

stmt = sqlparse("SELECT A, B FROM doc.tbl12")

print(stmt.metadata)
# Metadata(tables=[Table(schema='doc', name='tbl12')], parameterized_properties={}, with_properties={})

Query properties.

Properties defined within a WITH statement, statement.metadata.with_properties.

from cratedb_sqlparse import sqlparse

stmt = sqlparse("""
    CREATE TABLE doc.tbl12 (A TEXT) WITH (
      "allocation.max_retries" = 5,
      "blocks.metadata" = false
    );
""")[0]

print(stmt.metadata)
# Metadata(tables=[Table(schema='doc', name='tbl12')], with_properties={'allocation.max_retries': '5', 'blocks.metadata': 'false'})

Table name

print(stmt.metadata.tables)
# [Table(schema='doc', name='tbl12')]

table = stmt.metadata.tables[0]
print(table.schema, table.name, table.fqn, sep='\n')
# doc
# tbl12
# '"doc"."tbl12"'

Parameterized properties.

Parameterized properties are properties without a real defined value, marked with a dollar string, metadata.parameterized_properties

from cratedb_sqlparse import sqlparse

stmt = sqlparse("""
    CREATE TABLE doc.tbl12 (A TEXT) WITH (
    "allocation.max_retries" = 5,
    "blocks.metadata" = $1
);
""")[0]

print(stmt.metadata)
# Metadata(tables=[Table(schema='doc', name='tbl12')], parameterized_properties={'blocks.metadata': '$1'}, with_properties={'allocation.max_retries': '5', 'blocks.metadata': '$1'})

In this case, blocks.metadata will be in with_properties and parameterized_properties as well.

For values to be picked up they need to start with a dollar '$' and be preceded by integers, e.g. '$1', '$123' - '$123abc' would not be valid.

Development

Set up environment

git clone https://github.com/crate/cratedb-sqlparse

cd cratedb-sqlparse/cratedb_sqlparse_py

python3 -m venv .venv

source .venv/bin/activate

pip install --editable='.[develop,generate,release,test]'

Everytime you open a shell again you would need to run source .venv/bin/activate to use poe commands.

Run lint and tests with coverage.

poe check

Run only tests

poe test

Run a specific test.

poe test -k test_sqlparse_collects_exceptions_2

Run linter

poe lint

Download files

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

Source Distribution

cratedb_sqlparse-0.0.10.tar.gz (141.0 kB view details)

Uploaded Source

Built Distribution

cratedb_sqlparse-0.0.10-py3-none-any.whl (142.5 kB view details)

Uploaded Python 3

File details

Details for the file cratedb_sqlparse-0.0.10.tar.gz.

File metadata

  • Download URL: cratedb_sqlparse-0.0.10.tar.gz
  • Upload date:
  • Size: 141.0 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/5.1.1 CPython/3.12.7

File hashes

Hashes for cratedb_sqlparse-0.0.10.tar.gz
Algorithm Hash digest
SHA256 417f7d17661bf6f4f2e20fcd9c87195c9bcdfa7650eae9cd69aa470bfd6fad82
MD5 8e0f1a9e2d6191cdc23206c338ee09c3
BLAKE2b-256 e3019f209fb868d67c2c15cde908e41f0399306a670e937ae656cbd357f6e7f9

See more details on using hashes here.

File details

Details for the file cratedb_sqlparse-0.0.10-py3-none-any.whl.

File metadata

File hashes

Hashes for cratedb_sqlparse-0.0.10-py3-none-any.whl
Algorithm Hash digest
SHA256 602b133a22ca3506e19b2487683069d4573d11ba656a6303b1d00da63f7b9947
MD5 62f1d25b8c297fca55eecd266caea8e6
BLAKE2b-256 d5d7cb8739c55c10bf44ad6cb430eb89863d668cb51b1a0c6db49cd2cc72fb71

See more details on using hashes here.

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