CodingPython

Python – str.maketrans()

Working on a Python code, I had a requirement for removing the single/double quotes and open/close brackets from the string of below format —

>>> text = """with summary as (select '
...  'p.col1,p.col2,p.col3, ROW_NUMBER() '
...  'OVER(PARTITION BY p.col1,p.col3 ORDER BY '
...  'p.col2) AS rk from (select * from (select '
...  'col2, col1, col3, '
...  'sum(col4) as col6 from '
...  '"demo"."tab1" a join '
...  "(select lpad(col5, 12, '0') as col5 from demo1.tab11) ta on "
...  '(a.col1 = ta.col5) where col4 >= 0 and '
...  "(col3 like 'A%' or col3 in ('complete', 'random', "
...  "'mobile')) group by col2, "
...  'col1, col3) where col6>=1 order by '
...  'col2) p)\n'
...  '\n'
...  'SELECT s.*\n'
...  '  FROM summary s where s.rk=1 order by '
...  's.col1,s.col2')"""
>>>

Below is the desired output format of the same string —

>>> text
'with summary as select  p.col1,p.col2,p.col3, ROW_NUMBER  OVERPARTITION BY p.col1,p.col3 ORDER BY  p.col2 AS rk from select * from select  col2, col1, col3,  sumcol4 as col6 from  demo.tab1 a join  select lpadcol5, 12, 0 as col5 from misc.tab11 ta on  a.col1 = ta.col5 where col4 >= 0 and  col3 like A% or col3 in complete, random,  mobile group by col2,  col1, col3 where col6>=1 order by  col2 p  SELECT s.*   FROM summary s where s.rk=1 order by  s.col1,s.col2'

To achieve it, one of the simplest ways I could find is using str.translate with str.maketrans —

text = text.translate(str.maketrans({"'":None,'"':None, "(":None, ")":None, "\n":None}))

Reference – https://docs.python.org/3.5/library/stdtypes.html?highlight=maketrans#str.translate

Leave a Reply