Re: Extracting SQL identifiers from queries

[Andi Albrecht]

Hi Michael,

sorry for the late reply. To me the output looks (almost) good:

In the SELECT clause all identifiers are correct. The both None.* for
the CASE statements are a bit strange, but I'm not sure right now,
what parent would be in that case.

The identifiers from the FROM clause are ok too. You correctly print
the aliases and again there's no parent. However, it seems that there
is no function that, for example, returns "users" for "users as u".
I'd expected that get_real_name() would return that, but it doesn't.
I'll have to look a bit closer to the source why get_real_name()
returns the name that is used in the statement (i.e. "u") since it's
already the return value of get_name() here.

The WHERE part isn't ideal. One suggestion to improve your script is
to recurse through the tree in the while loop starting at line 25,
esp. line 29 where you search for the next token. There's an extra
level of nesting here. That is the reason why the identifiers in the
parentheses don't show up at all.

But your impression is right. This low-level API of sqlparse isn't
tuned and a bit hard to handle in some cases. Have you seen
https://github.com/andialbrecht/sqlparse/blob/master/examples/extract_table_names.py?
it's a similar script that extracts table names.

Best regards,

Andi

On Tue, Jan 21, 2014 at 4:35 PM, Michael Renner <refere...@gmail.com> wrote:
> Hi,
>
> I'm trying to extract all SQL identifiers (table and column references) from
> a given statement including the context of the references (SELECT, WHERE,
> GROUP, ORDER, etc.) but wasn't overly successful with sqlparse.
>
> Here's the script I hacked together:
> https://gist.github.com/terrorobe/8542101 and the output it produces:
> https://gist.github.com/terrorobe/8542065
>
> This has been tested against eebde5b6a80ec34089acd8f7422f0a621918e6f5 of
> sqlparse (16th Jan).
>
> Am I holding sqlparse wrong or is reliable identifier extraction something
> which isn't feasible in it's current state?
>
> best,
> Michael
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlparse" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlparse+u...@googlegroups.com.
> To post to this group, send email to sqlp...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlparse.
> For more options, visit https://groups.google.com/groups/opt_out.