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