Didina Deen Didina Deen - 2 months ago 7
Python Question

Replacing different substrings without clear pattern in python

I need to replace part of some queries (strings) which don't always have the same substring to replace.

query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value
from table
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
group by YEAR(utimestamp), MONTH(utimestamp), id """


I want to replace the part regarding date after group by.

This part could be any of the following strings:

'YEAR(utimestamp), MONTH(utimestamp), DAY(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp),'
'YEAR(utimestamp),'


My idea is to search for "(utimestamp)," and get the part from the left (YEAR, DAY, WEEK or MONTH) searching for the first blank space in the left. After having those removed I want to insert another substring, but how can I insert this substring now that I have blank spaces where the new substring should go.

I thought of getting the index everytime I removed a string and once there's no more to remove insert the substring there but I think I'm complicating things.

Is there an easier, neat way of doing this? Am I missing something?

EXAMPLE:

Input string that needs replacement:

query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value
from table
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
group by YEAR(utimestamp), MONTH(utimestamp), id """

or

query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value
from table
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
group by YEAR(utimestamp), id """


or

query = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value
from table
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
group by YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp), id """


etc.

Desired result:

query_replaced = """ SELECT DATE(utimestamp) as utimestamp, sum(value) as value
from table
where utimestamp BETWEEN '2000-06-28 00:00:00' AND '2000-07-05 00:00:00'
group by MY_COOL_STRING, id """


If should work for all those cases (and more, the ones stated before)

Following @Efferalgan answer I came up with this:

query_1 = query.split("group by")[0]
utimestamp_list = query.split("(utimestamp)")
l = len(utimestamp_list)
query_2 = utimestamp_list[l-1]
query_3 = query_1 + " group by MY_COOL_STRING" + query_2

Answer

From what you asked, I would go for

query = query.split("group by")[0] + " group by MY_COOL_STRING" + query.split("(utimestamp)")[-1]

It concatenates the part before the group by, then MY_COOL_STRING and then first thing before the first (utimestamp).