[Python] How can I generate SQL using sqlfluff?

I have the following example SQL (it gets more complex):

select
*
from
(select
a,
b
from
agents)


I want to parse such a string using sqlfluff

from sqlfluff.core import Parser
p = Parser(dialect='ansi')
parsed = p.parse_string(query)


and split the query into two parts (and ideally into two trees/ParsedString), the outer one and the inner one. The inner query should be inserted into a CTAS statement and the outer query should reference it.

Expected pseudocode:

outer_tree.set_innermost_token('reagents')
inner_tree.set_outermost_tokens('create or replace reagents as')
print(outer_tree.generate_SQL())
# !!! for simplicity I am removing the indentation
# select * from reagents
print(inner_tree.generate_SQL())
# !!! for simplicity I am removing the indentation
# create or replace reagents as select a, b from agents


Please bear in mind, this is just a simple example, I know a rudimentary regexp could help in this cases, however, I am aiming to modify more complex queries. Additionally, the SQL queries are already written, therefore I cannot go about writing only a generator.

Thanks for the pointers!

I looked at the cli.py and also on the internals of Linter, but I could only generate the same SQL code as was provided (by setting the variable filtered_source_patches to None on line 222).

Continue reading...