Altering an explicit select statement to select all: select a, b, c -> select *

[ThomasHepworth]

Hiya Toby and co.

Thanks again for all of the hard work on this.

I'll keep this one short - is there a neat way of altering a select statement such as: select a, b, c to select all or select a count of an input dataset (select count(*) as count or select *, for example)?

Additionally, it'd be useful in general to know how to remove/delete sections of SQL in the parsed tree too. I'm hoping is answered through ☝️.

Thanks,
Tom

[barakalon]

Hi Tom -

Certain expression types have methods for altering certain clauses. For example, see Select.select:

>>> expression = sqlglot.parse_one("select a, b, c")
>>> expression = expression.select("*", append=False)
>>> print(expression)
SELECT *

Note the append=False argument - this overwrites the selects instead of appending to the list.

By default, this method copies the expression before altering it. If you prefer to edit the expression in-place, set copy=False.

These "builder" methods can't yet be used to remove clauses, but I don't see why we couldn't add that functionality...

For now, to remove clauses, you can use set, which is available on all expression types:

>>> expression = sqlglot.parse_one("select a, b, c from x")
>>> expression.set("from", None)
>>> print(expression)
SELECT a, b, c

Here you need to know the "arg" name of the clause you want to remove. Those can be found in the arg_types class property of Expression types.

This was intended to be a lower-level method, so it mutates the expression in-place. In the future, we should probably make the behavior match methods like Select.select, but for now, if you don't want to mutate in-place, you can copy first:

>>> expression.copy().set("from", None)

Hope that helps.

And we'd be happy to get feedback/contributions on making this interface more intuitive :)

[ThomasHepworth]

Thanks so much for this, it's really useful.