cardinal_pythonlib.sql.sql_grammar

---

Original code copyright (C) 2009-2022 Rudolf Cardinal (rudolf@pobox.com).

This file is part of cardinal_pythonlib.

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

---

SQL grammar parser: keywords, base class, and test functions.

Two approaches:

• sqlparse (https://sqlparse.readthedocs.io/en/latest/)… LESS GOOD.

• pyparsing

  • https://pyparsing.wikispaces.com/file/view/select_parser.py

  • http://pyparsing.wikispaces.com/file/view/simpleSQL.py

  • http://sacharya.com/parsing-sql-with-pyparsing/

  • https://bazaar.launchpad.net/~eleybourn/temporal-proxy/devel/view/head:/parser.py

  • http://pyparsing.wikispaces.com/file/view/select_parser.py/158651233/select_parser.py

  • Maximum recursion depth exceeded:

    http://pyparsing.wikispaces.com/share/view/11262229

  • Match-first (|) versus match-longest (^):

    http://pyparsing.wikispaces.com/HowToUsePyparsing

  • How to do arithmetic recursive parsing:

    https://stackoverflow.com/questions/1345039

  • Somebody else’s MySQL parser:

    https://github.com/gburns/mysql_pyparsing/blob/master/src/sqlparser.py

  • … or generic SQL:

    http://pyparsing.wikispaces.com/file/view/select_parser.py/158651233/select_parser.py

ANSI SQL syntax:

• https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

  … particularly the formal specifications in chapter 5 on words

class cardinal_pythonlib.sql.sql_grammar.SqlGrammar

Base class for implementing an SQL grammar.

classmethod get_column_spec() → pyparsing.core.ParserElement

Returns a parser element representing an SQL column name, such as

somecol
sometable.somecol
somedb.sometable.somecol

classmethod get_expr() → pyparsing.core.ParserElement

Returns a parser element representing an SQL expression, such as

somecol
17
NOT(LENGTH(LEFT(somecol, 5)) + 3 < othercol % 4)

classmethod get_grammar() → pyparsing.core.ParserElement

Returns the full SQL grammar parser.

classmethod get_join_constraint() → pyparsing.core.ParserElement

Returns a parser element representing an SQL join constraint, such as

ON customer.id = sale.customer_id

classmethod get_join_op() → pyparsing.core.ParserElement

Returns a parser element representing an SQL JOIN operation, such as

JOIN
INNER JOIN
LEFT OUTER JOIN

classmethod get_result_column() → pyparsing.core.ParserElement

Returns a parser element representing an SQL result column, such as

somecol
somecol AS somealias
COUNT(*) AS total
3

classmethod get_select_statement() → pyparsing.core.ParserElement

Returns a parser element representing an SQL SELECT statement, such as

SELECT a, b FROM sometable WHERE c = 5;
SELECT a, COUNT(*) FROM sometable INNER JOIN othertable ON
    sometable.c = othertable.c GROUP BY a;

classmethod get_table_spec() → pyparsing.core.ParserElement

Returns a parser element representing an SQL table name, such as

sometable
somedb.sometable

classmethod get_where_clause() → pyparsing.core.ParserElement

Returns a parser element representing an SQL WHERE clause (the WHERE keyword followed by a WHERE expression), such as

WHERE a > 3
WHERE LENGTH(a) < c + 2

classmethod get_where_expr() → pyparsing.core.ParserElement

Returns a parser element representing an SQL WHERE expression (the condition without the WHERE keyword itself), such as

a > 3
LENGTH(a) < c + 2

classmethod is_quoted(identifier: str) → bool

Determines whether an SQL identifier (e.g. table or column name) is already quoted.

classmethod quote_identifier(identifier: str) → str

Quotes an SQL identifier (e.g. table or column name); e.g. some databases use [name], some use `name`).

classmethod quote_identifier_if_required(identifier: str, debug_force_quote: bool = False) → str

Transforms a SQL identifier (such as a column name) into a version that is quoted if required (or, with debug_force_quote=True, is quoted regardless).

classmethod requires_quoting(identifier: str) → bool

Determines whether an SQL identifier (e.g. table or column name) requires to be quoted.

classmethod test(test_expr: bool = True) → None

Runs self-tests.

Parameters:test_expr – include tests of expressions (which can be slow).

classmethod test_dialect_specific_1() → None

Override this to add dialect-specific tests (#1).

classmethod test_dialect_specific_2() → None

Override this to add dialect-specific tests (#2).

classmethod test_expr() → None

Tests parsing of basic expressions.

classmethod test_identifiers() → None

Tests parsing of SQL keywords.

classmethod test_select(text: str) → None

Tests the SELECT statement against the specified text, and ensure it parses successfully.

classmethod test_select_fail(text: str) → None

Tests the SELECT statement against the specified text, and ensure it fails to parse.

classmethod test_sql_core() → None

Tests some core SQL features.