Not detecting multiple where clauses

[acscott]

While testing this, sqlparse doesn't seem to detect the second where clause in the sql statement below.

#!/usr/bin/env python2.7

import sqlparse
from sqlparse.tokens import Keyword, DML

sql = ("select * from names inner join addresses on nameid=nameid_fk where"
     " a like test% and 1 in (select * from testtables where a='d');")
stmts = sqlparse.parse(sql)

for item in stmts[0].tokens:

    print "result:",item
    if item.is_group():
        print "the result above is_group"

    if isinstance(item, sqlparse.sql.Where):
        print "Where clause found:",item
        for item2 in item.tokens:
            if not isinstance(item2, sqlparse.sql.Token):
                print "     item2", item2, "tokens", len(item2.tokens)
            if isinstance(item2, sqlparse.sql.Statement):
                print "     another statement", item2
            if isinstance(item2, sqlparse.sql.Where):
                print "     another where clause", item2


    if isinstance(item, sqlparse.sql.Comparison):
        print "result above is a comparison"

[vmuriart]

can you show the results of stmts[0]._pprint()?

[acscott]

There's not a stmts[0]._pprint()--see the bottom dir--but there is a stmts[0]._pprint_tree(). I'm using version: 0.1.19 as reported in PKG-INFO
And it is parsing out the 2nd 'where' token . I guess I can parse the tree to get what I need. Or is there a a better way than iterating through tokens other than getting unlazy and contribute back to this cool package :)?

>>> stmts[0]._pprint_tree()
 | 0 DML 'select'
 | 1 Whitespace ' '
 | 2 Wildcard '*'
 | 3 Whitespace ' '
 | 4 Keyword 'from'
 | 5 Whitespace ' '
 | 6 Keyword 'names'
 | 7 Whitespace ' '
 | 8 Keyword 'inner ...'
 | 9 Whitespace ' '
 +-10 Identifier 'addres...'
   | 0 Name 'addres...'
 | 11 Whitespace ' '
 | 12 Keyword 'on'
 | 13 Whitespace ' '
 +-14 Comparison 'nameid...'
   +-0 Identifier 'nameid'
     | 0 Name 'nameid'
   | 1 Comparison '='
   +-2 Identifier 'nameid...'
     | 0 Name 'nameid...'
 | 15 Whitespace ' '
 +-16 Where 'where ...'
   | 0 Keyword 'where'
   | 1 Whitespace ' '
   +-2 Identifier 'a'
     | 0 Name 'a'
   | 3 Whitespace ' '
   | 4 Keyword 'like'
   | 5 Whitespace ' '
   +-6 Identifier 'test%'
     | 0 Name 'test'
     | 1 Operator '%'
   | 7 Whitespace ' '
   | 8 Keyword 'and'
   | 9 Whitespace ' '
   | 10 Integer '1'
   | 11 Whitespace ' '
   | 12 Keyword 'in'
   | 13 Whitespace ' '
   +-14 Parenthesis '(selec...'
     | 0 Punctuation '('
     | 1 DML 'select'
     | 2 Whitespace ' '
     | 3 Wildcard '*'
     | 4 Whitespace ' '
     | 5 Keyword 'from'
     | 6 Whitespace ' '
     +-7 Identifier 'testta...'
       | 0 Name 'testta...'
     | 8 Whitespace ' '
       +-9 Where 'where ...'
       | 0 Keyword 'where'
       | 1 Whitespace ' '
       +-2 Comparison 'a='d''
         +-0 Identifier 'a'
           | 0 Name 'a'
         | 1 Comparison '='
         | 2 Single ''d''
     | 10 Punctuation ')'
   | 15 Punctuation ';'

dir(stmts[0])
['__class__', '__delattr__', '__doc__', '__format__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__subclasshook__', '__unicode__', '_get_first_name', '_get_repr_name', '_get_repr_value', '_groupable_tokens', '_pprint_tree', '_remove_quotes', '_to_string', 'flatten', 'get_alias', 'get_name', 'get_parent_name', 'get_real_name', 'get_sublists', 'get_token_at_offset', 'get_type', 'group_tokens', 'has_alias', 'has_ancestor', 'insert_after', 'insert_before', 'is_child_of', 'is_group', 'is_keyword', 'is_whitespace', 'match', 'normalized', 'parent', 'to_unicode', 'token_first', 'token_index', 'token_matching', 'token_next', 'token_next_by_instance', 'token_next_by_type', 'token_next_match', 'token_not_matching', 'token_prev', 'tokens', 'tokens_between', 'ttype', 'value', 'within']

[vmuriart]

That's the one function I meant. I tried to remember the function name without looking it up.
The code below should work for you; had to make some syntax changes to quiet down my linter. The function is based on the flatten function.

#!/usr/bin/env python2.7

from __future__ import print_function
import sqlparse

sql = """
SELECT * 
FROM   names 
       INNER JOIN addresses 
               ON nameid = nameid_fk 
WHERE  a LIKE test% 
   AND 1 IN (SELECT * 
             FROM   testtables 
             WHERE  a = 'd'); """


def funct(tlist):
    for token in tlist.tokens:
        yield token

        if token.is_group():
            for token_ in funct(token):
                yield token_

stmts = sqlparse.parse(sql)

for item in stmts[0].tokens:

    print("result:", item)
    if item.is_group():
        print("the result above is_group")

    if isinstance(item, sqlparse.sql.Where):
        print("Where clause found:", item)
        for item2 in funct(item):
            if not isinstance(item2, sqlparse.sql.Token):
                print("     item2", item2, "tokens", len(item2.tokens))
            if isinstance(item2, sqlparse.sql.Statement):
                print("     another statement", item2)
            if isinstance(item2, sqlparse.sql.Where):
                print("     another where clause", item2)


    if isinstance(item, sqlparse.sql.Comparison):
        print("result above is a comparison")

[vmuriart]

The reason your original code didn't work is that it wasn't iterating into the other group tokenlists. On the _pprint_tree() you can see that the second where is inside of a parenthesis group.

[vmuriart]

I think this can be closed