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
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://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:
Match-first (|) versus match-longest (^):
How to do arithmetic recursive parsing:
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[source]
Base class for implementing an SQL grammar.
- classmethod get_column_spec() ParserElement [source]
Returns a parser element representing an SQL column name, such as
somecol sometable.somecol somedb.sometable.somecol
- classmethod get_expr() ParserElement [source]
Returns a parser element representing an SQL expression, such as
somecol 17 NOT(LENGTH(LEFT(somecol, 5)) + 3 < othercol % 4)
- classmethod get_join_constraint() ParserElement [source]
Returns a parser element representing an SQL join constraint, such as
ON customer.id = sale.customer_id
- classmethod get_join_op() ParserElement [source]
Returns a parser element representing an SQL JOIN operation, such as
JOIN INNER JOIN LEFT OUTER JOIN
- classmethod get_result_column() ParserElement [source]
Returns a parser element representing an SQL result column, such as
somecol somecol AS somealias COUNT(*) AS total 3
- classmethod get_select_statement() ParserElement [source]
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() ParserElement [source]
Returns a parser element representing an SQL table name, such as
sometable somedb.sometable
- classmethod get_where_clause() ParserElement [source]
Returns a parser element representing an SQL WHERE clause (the
WHERE
keyword followed by a WHERE expression), such asWHERE a > 3 WHERE LENGTH(a) < c + 2
- classmethod get_where_expr() ParserElement [source]
Returns a parser element representing an SQL WHERE expression (the condition without the
WHERE
keyword itself), such asa > 3 LENGTH(a) < c + 2
- classmethod is_quoted(identifier: str) bool [source]
Determines whether an SQL identifier (e.g. table or column name) is already quoted.
- classmethod quote_identifier(identifier: str) str [source]
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 [source]
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 [source]
Determines whether an SQL identifier (e.g. table or column name) requires to be quoted.
- classmethod test(test_expr: bool = True) None [source]
Runs self-tests.
- Parameters:
test_expr¶ – include tests of expressions (which can be slow).
- classmethod test_dialect_specific_1() None [source]
Override this to add dialect-specific tests (#1).
- classmethod test_dialect_specific_2() None [source]
Override this to add dialect-specific tests (#2).
- classmethod test_select(text: str) None [source]
Tests the SELECT statement against the specified text, and ensure it parses successfully.