How To modify table name and format the SQL?

[hanga...@gmail.com]

with this example we can extract the table names

What if I want to modify the table names automatically and make the SQL in string form again after modifying it?

Thanks 

https://github.com/andialbrecht/sqlparse/blob/master/examples/extract_table_names.py

like:

select K.a,K.b from (select H.b from (select G.c from (select F.d from (select E.e from A, B, C, D, E), F), G), H), I, J, K order by 1,2;  

should be auto automatically modified to 

select K.a,K.b from (select H.b from (select G.c from (select F.d from (select E.e from test1.A, test2.B, test3.C, test4.D, test5.E), test6.F), test7.G), test8.H), test9.I, test10.J, test11.K order by 1,2;  

[Andi Albrecht]

Hi,

this is only possible using an undocumented part of sqlparse. Here's
an example on how it could work, but be warned: the internals used
here may change at any time without any notice!

>>> import sqlparse
>>> p = sqlparse.parse("select * from foo")[0]
>>> p._pprint_tree()
|- 0 DML 'select'
|- 1 Whitespace ' '
|- 2 Wildcard '*'
|- 3 Whitespace ' '
|- 4 Keyword 'from'
|- 5 Whitespace ' '
`- 6 Identifier 'foo'
`- 0 Name 'foo'
>>> # as seen above, we need to change to last token. Note, it's nested!
>>> p.tokens[-1].tokens[-1].value = 'bar'
>>> str(p)
'select * from bar'

Hope that helps,

Andi

> --
> 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 https://groups.google.com/group/sqlparse.
> For more options, visit https://groups.google.com/d/optout.

[hanga...@gmail.com]

Hello,

Thank you very much.

And Next question is How to find all the TableName identifiers automatically.

When a SQL statement string is given, I need to find out which part to modify and make the modification automatically.

Thank you.

在 2019年3月21日星期四 UTC+8下午4:41:20，Andi Albrecht写道：

[Andi Albrecht]

Hi,

you'll have to walk through the tree as visualized by _pprint_tree().
Have a look at the tokens attribute of the parsed statement and again
the tokens attribute of the elements found in tokens.

Unfortunately it's not easy at the moment to know if a node is a
table. They're only marked as Identifiers (which in turn have a Name
child in their tokens attribute). But identifier not only means "table
name".

Andi

[hanga...@gmail.com]

Hello,

Thank you for your infomation.

The problem I am facing is that I have a lot of  SQLs like this:    (SELECT GREATEST(a, 1323.123456) FROM t1) UNION ALL  (SELECT GREATEST(a, 1323.123456) FROM t1 LIMIT 0);

I need to make batch modification on table names.

bye the help of you previous reply, I have figured out the way to find the table names, which is 

def loopallnode(p):

    for i in range(0,len(p.tokens)):

        if isinstance(p.tokens[i], Identifier) or isinstance(p.tokens[i], IdentifierList):

    #             p[i].value = "oooooooooooooooo"

          if i>=2 :

            m = p.tokens[i-2].value.upper()

            if  m == 'FROM' or m == 'INTO' or m == 'UPDATE' or m == 'TABLE' or m == 'DESC' or m == 'DESCRIBE' or m =='VIEW':

                 if hasattr(p.tokens[i],'tokens') :

                       for j in range (0, len(p.tokens[i].tokens)):

                          if isinstance(p.tokens[i].tokens[j], Identifier) :

                             print p.tokens[i].tokens[j].value

                             p.tokens[i].tokens[j].value = "ooo"

                 else:

                       print p.tokens[i].value

                       p.tokens[i].value = "kkk"

#

                       pass

        if  hasattr(p.tokens[i],'tokens') :

           loopallnode(p.tokens[i])

        else:

           pass

This could high probability work on my sqls (for the ones this function doesn't I can fix the sql manually) 

But , It is based on recursion function call, so I can not make change, and rejoin the parsed statement to the sql string again.

Is it possible to make changes when  recursion call (Is there an example code?), or is it possible to walk though the tree without  recursion function call (Is there an example code?)?

Thanks,

Han, Gang
在 2019年3月21日星期四 UTC+8下午9:01:04，Andi Albrecht写道：

[hanga...@gmail.com]

I've found the way to do this, Thank you.

在 2019年3月21日星期四 UTC+8下午9:01:04，Andi Albrecht写道：

Hi,