def test__api__parse_fail():
"""Basic failure mode of parse functionality."""
try:
sqlfluff.parse("Select (1 + 2 +++) FROM mytable as blah blah")
pytest.fail("sqlfluff.parse should have raised an exception.")
except Exception as err:
# Check it's the right kind of exception
assert isinstance(err, sqlfluff.api.APIParsingError)
# Check there are two violations in there.
assert len(err.violations) == 2
# Check it prints nicely.
assert (str(err) == """Found 2 issues while parsing string.
Line 1, Position 14: Found unparsable section: ' +++'
Line 1, Position 41: Found unparsable section: 'blah'""")
def _sqlfluff_extract_table_refs(self, relations, updated_at):
table_refs = []
for relation in relations:
if relation['type'] == 'text':
if relation['text']:
dialect = infer_dialect(relation)
query = clean_tableau_sql(relation['text'])
# Parse SQL using SQLFluff
try:
parsed = sqlfluff.parse(query)
external_tables = parsed.tree.get_table_references()
for ref in external_tables:
table_refs.append(
self.jsonify_dict({
'wb_id': relation['wb_id'],
'ds_id': relation['ds_id'],
'conn_id': relation['conn_id'],
'rel_id': relation['id'],
'id': f"{relation['wb_id']}:{relation['ds_id']}:{relation['conn_id']}:{relation['id']}:{uuid.uuid4()}",
'ref': ref,
'updated_at': updated_at
})
)
except sqlfluff.core.SQLBaseError as e:
logger.error(f"Falied to parse table refs: {e}")
pass
return table_refs
def test__api__invalid_dialect():
"""Test that SQLFluffUserError is raised for a bad dialect."""
# Load test SQL file.
with open("test/fixtures/api/config_path_test/config_path_test.sql",
"r") as f:
sql = f.read()
# Pass a fake dialect to the API and test the correct error is raised.
with pytest.raises(SQLFluffUserError) as err:
sqlfluff.parse(
sql,
dialect="not_a_real_dialect",
config_path=
"test/fixtures/api/config_path_test/extra_configs/.sqlfluff",
)
assert str(err.value) == "Error: Unknown dialect 'not_a_real_dialect'"
def test__api__parse_string():
"""Basic checking of parse functionality."""
parsed = sqlfluff.parse(my_bad_query)
# Check we can call `to_tuple` on the result
assert isinstance(parsed.to_tuple(), tuple)
# Check we can iterate objects within it
keywords = [keyword.raw for keyword in parsed.recursive_crawl("keyword")]
assert keywords == ["SeLEct", "as", "from"]
def test__api__parse_string():
"""Basic checking of parse functionality."""
parsed = sqlfluff.parse(my_bad_query)
# Check a JSON object is returned.
assert isinstance(parsed, dict)
# Load in expected result.
with open("test/fixtures/api/parse_test/parse_test.json", "r") as f:
expected_parsed = json.load(f)
# Compare JSON from parse to expected result.
assert parsed == expected_parsed
def test__api__parse_string():
"""Basic checking of parse functionality."""
parsed = sqlfluff.parse(my_bad_query)
# Check we can call `to_tuple` on the result
assert isinstance(parsed.to_tuple(), tuple)
# Check we can iterate objects within it
keywords = [keyword.raw for keyword in parsed.recursive_crawl("keyword")]
assert keywords == ["SeLEct", "as", "from"]
# Check we can get columns from it
col_refs = [
col_ref.raw for col_ref in parsed.recursive_crawl("column_reference")
]
assert col_refs == ["blah"]
# Check we can get table from it
tbl_refs = [
tbl_ref.raw for tbl_ref in parsed.recursive_crawl("table_reference")
]
assert tbl_refs == ["myTable"]
def test__api__config_path():
"""Test that we can load a specified config file in the Simple API."""
# Load test SQL file.
with open("test/fixtures/api/config_path_test/config_path_test.sql",
"r") as f:
sql = f.read()
# Pass a config path to the Simple API.
parsed = sqlfluff.parse(
sql,
config_path=
"test/fixtures/api/config_path_test/extra_configs/.sqlfluff",
)
# Load in expected result.
with open("test/fixtures/api/config_path_test/config_path_test.json",
"r") as f:
expected_parsed = json.load(f)
# Compare JSON from parse to expected result.
assert parsed == expected_parsed
# Fix the given string and get a string back which has been fixed.
fix_result_1 = sqlfluff.fix(my_bad_query, dialect="bigquery")
# fix_result_1 = 'SELECT *, 1, blah AS foo FROM myschema.mytable\n'
# We can also fix just specific rules.
fix_result_2 = sqlfluff.fix(my_bad_query, rules=["L010"])
# fix_result_2 = 'SELECT *, 1, blah AS fOO FROM mySchema.myTable'
# Or a subset of rules...
fix_result_3 = sqlfluff.fix(my_bad_query, rules=["L010", "L014"])
# fix_result_3 = 'SELECT *, 1, blah AS fOO FROM myschema.mytable'
# -------- PARSING ----------
# Parse the given string and return a JSON representation of the parsed tree.
parse_result = sqlfluff.parse(my_bad_query)
# parse_result = {'file': {'statement': {...}, 'newline': '\n'}}
# This JSON structure can then be parsed as required.
# An example usage is shown below:
def get_json_segment(
parse_result: Dict[str, Any], segment_type: str
) -> Iterator[Union[str, Dict[str, Any], List[Dict[str, Any]]]]:
"""Recursively search JSON parse result for specified segment type.
Args:
parse_result (Dict[str, Any]): JSON parse result from `sqlfluff.fix`.
segment_type (str): The segment type to search for.
result = sqlfluff.fix(my_bad_query, dialect="bigquery")
# result = 'SELECT *, 1, blah AS foo FROM mytable\n'
# We can also fix just specific rules.
result = sqlfluff.fix(my_bad_query, rules="L010")
# result = 'SELECT *, 1, blah AS fOO FROM myTable'
# Or a subset of rules...
result = sqlfluff.fix(my_bad_query, rules=["L010", "L014"])
# result = 'SELECT *, 1, blah AS fOO FROM mytable'
# -------- PARSING ----------
# NOTE: sqlfluff is still in a relatively early phase of its
# development and so until version 1.0.0 will offer no guarantee
# that the names and structure of the objects returned by these
# parse commands won't change between releases. Use with care
# and keep updated with the changelog for the project for any
# changes in this space.
parsed = sqlfluff.parse(my_bad_query)
# Get the structure of the query
structure = parsed.tree.to_tuple(show_raw=True, code_only=True)
# structure = ('file', (('statement', (('select_statement', (('select_clause', (('keyword', 'SeLEct'), ...
# Extract certain elements
keywords = [keyword.raw for keyword in parsed.tree.recursive_crawl("keyword")]
# keywords = ['SeLEct', 'as', 'from']
tbl_refs = [tbl_ref.raw for tbl_ref in parsed.tree.recursive_crawl("table_reference")]
# tbl_refs == ["myTable"]
"""This is an example of how to extract table names."""
import sqlfluff
query_with_ctes = """
WITH foo AS (SELECT * FROM bar.bar),
baz AS (SELECT * FROM bap)
SELECT * FROM foo
INNER JOIN baz USING (user_id)
INNER JOIN ban USING (user_id)
"""
# -------- PARSING ----------
parsed = sqlfluff.parse(query_with_ctes)
# -------- EXTRACTION ----------
# Under the hood we look for all of the table references
# which aren't also CTE aliases.
external_tables = parsed.tree.get_table_references()
# external_tables == {'bar.bar', 'bap', 'ban'}
def test__api__util_get_table_references(sql, table_refs):
"""Basic checking of lint functionality."""
parsed = sqlfluff.parse(sql)
external_tables = parsed.tree.get_table_references()
assert external_tables == table_refs
