Python regular expression extract MySQL slow query SQL itself, De-parameterize, change the parameter value?

Source: Internet
Author: User

I put the question on the StackOverflow, but I didn't answer it. Write it yourself.

My need is to show MySQL slow queries on the page. But if the original show, will take different parameters, not very good group and so on. The only thing we care about is SQL itself, like

--These two are actually a slow query .Select *  fromAwhereA>1  andB='R'  andC=3;Select *  fromAwhereA>2  andB='x'  andC=5;--I want to be able to handleSelect *  fromAwhereA>? andB='?'  andC=?

Because there is no proper module, you have to replace it with Regrex. Numbers are easy, strings need to be considered

    1. At its most basic, the replacement number can be independent of one or more consecutive numbers with R "\b\d+\b", so that numbers in objects such as col1 are not replaced
    2. Simply, the string can be used with R "' [^ ']* '" to represent all non-consecutive characters between 2 ', so that most cases can be applied, except that the string has spaces, such as ' a BDF C ' will not be
    3. In the most rigorous way, press ' divide an array of SQL. So all the strings inside are even members, and the other parts are odd members. Replace even-numbered members? Yes, you can.

The code is as follows import re

SQL= R"SELECT * from a where id= ' aaaaa haha wocao ' and id1= ' FFF ' and Xx=1 and a3= 4 and a4=3434343 and a5a>99"Sql.split ("'")#['SELECT * from a where id=','aaaaa haha Wocao','and id1=','FFF','and Xx=1 and a3= 4 and a4=3434343 and a5a>99']sarr= Sql.split ("'") sarr#['SELECT * from a where id=','aaaaa haha Wocao','and id1=','FFF','and Xx=1 and a3= 4 and a4=3434343 and a5a>99']sarr[::2]#['SELECT * from a where id=','and id1=','and Xx=1 and a3= 4 and a4=3434343 and a5a>99']sarr[1::2]#['aaaaa haha Wocao','FFF']sarr[1::2]= ['?'  forXinchSarr[1::2]]sarr#['SELECT * from a where id=','?','and id1=','?','and Xx=1 and a3= 4 and a4=3434343 and a5a>99']"'". Join (Sarr)#"SELECT * from a where id= '? ' and id1= '? ' and Xx=1 and a3= 4 and a4=3434343 and a5a>99"SQL#"SELECT * from a where id= ' aaaaa haha wocao ' and id1= ' FFF ' and Xx=1 and a3= 4 and a4=3434343 and a5a>99"

AA = "'". Join (Sarr)
re.sub (r "\b\d+\b", "?", AA)
# "SELECT * from a where id= '? ' and id1= '? ' and xx=? and a3=? and a4=? and A5a>? "

Python regular expression extract MySQL slow query SQL itself, De-parameterize, change the parameter value?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.