Parsing utilities to validate and split SQL statements for CrateDB.
Project description
CrateDB SQL Parser for Python
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
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
Built Distribution
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
Algorithm | Hash digest | |
---|---|---|
SHA256 | 417f7d17661bf6f4f2e20fcd9c87195c9bcdfa7650eae9cd69aa470bfd6fad82 |
|
MD5 | 8e0f1a9e2d6191cdc23206c338ee09c3 |
|
BLAKE2b-256 | e3019f209fb868d67c2c15cde908e41f0399306a670e937ae656cbd357f6e7f9 |
File details
Details for the file cratedb_sqlparse-0.0.10-py3-none-any.whl
.
File metadata
- Download URL: cratedb_sqlparse-0.0.10-py3-none-any.whl
- Upload date:
- Size: 142.5 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/5.1.1 CPython/3.12.7
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 602b133a22ca3506e19b2487683069d4573d11ba656a6303b1d00da63f7b9947 |
|
MD5 | 62f1d25b8c297fca55eecd266caea8e6 |
|
BLAKE2b-256 | d5d7cb8739c55c10bf44ad6cb430eb89863d668cb51b1a0c6db49cd2cc72fb71 |