Replacing different substrings without clear pattern in python
<p>I need to replace part of some queries (strings) which <strong>don't</strong> always have the same substring to replace. </p>

<pre><code>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 """
</code></pre>

<p>I want to replace the part regarding date after <em>group by</em>. </p>

<p>This part could be any of the following strings:</p>

<pre><code>'YEAR(utimestamp), MONTH(utimestamp), DAY(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp),'
'YEAR(utimestamp), MONTH(utimestamp),'
'YEAR(utimestamp),'
</code></pre>

<p>My idea is to search for "<em>(utimestamp),</em>" 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.</p>

<p>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.</p>

<p>Is there an easier, neat way of doing this? Am I missing something?</p>

<p><strong>EXAMPLE:</strong></p>

<p>Input string that needs replacement:</p>

<p>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 """</p>

<p>or </p>

<pre><code>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 """
</code></pre>

<p>or</p>

<pre><code>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 """
</code></pre>

<p>etc.</p>

<p>Desired result:</p>

<pre><code>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 """
</code></pre>

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

<p>Following @Efferalgan answer I came up with this:</p>

<pre><code>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
</code></pre>

<p>If I'm not mistaken, you don't want to get rid of the <code>(utimestamp)</code> part, only the <code>YEAR</code>, <code>MONTH</code>, etc. Or maybe I got it wrong but this solution is trivial to adapt in that case: just adapt the <code>rep</code> dict to cover your needs.</p>

<p>In any case, I would use regular expressions for that. This should take care of what you want (I think) in a single pass and in a (fairly) simple way.</p>

<pre class="lang-python prettyprint-override"><code>import re

rep = {
    'YEAR': 'y',
    'MONTH': 'm',
    'WEEK': 'w',
    'DAY': 'd',
}

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 """

rep = dict((re.escape(k), v) for k, v in rep.iteritems())
pattern = re.compile("|".join(rep.keys()))
replaced = pattern.sub(lambda m: rep[re.escape(m.group(0))], query)

print("Processed query: {}\n".format(replaced))
</code></pre>

<p>That's just the basic example. Here's a more complete one with comments explaining what the code does, including a test at the end for all the possible patterns you mentioned:</p>

<pre class="lang-python prettyprint-override"><code>import re

# Several possible patterns like you mentioned.
# Only used for testing further down.
patterns = [
    'YEAR(utimestamp), MONTH(utimestamp), DAY(utimestamp)',
    'YEAR(utimestamp), MONTH(utimestamp), WEEK(utimestamp)',
    'YEAR(utimestamp), MONTH(utimestamp)',
    'YEAR(utimestamp)'
]

# These are the several patterns to be matched and their replacements.
# The keys are the patterns to match and the values are what you want
# to replace them with.
rep = {
    'YEAR': 'y',
    'MONTH': 'm',
    'WEEK': 'w',
    'DAY': 'd',
}

# The query string template, where we'll replace {} with each of the patterns.
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 {}, id """

# A dictionary with escaped patterns (the keys) suitable for use in regex.
rep = dict((re.escape(k), v) for k, v in rep.iteritems())

# We join each possible pattern (the keys in the rep dict) with | so that the
# regex engine considers them all when matching, i.e., "hey, regex engine,
# please match YEAR or MONTH or WEEK or DAY". This builds the matching patter
# we'll use and we also pre-compile the regex to make it faster.
pattern = re.compile("|".join(rep.keys()))

# This is the trick part: we're using pattern.sub() to replace our pattern from
# above with what we want (the values in the rep dict). We're telling the regex
# engine to call a function for each occurrence of the pattern in order to get
# the value we're replacing it with. In our case, we want to get the value from
# the rep dict, using the key which is the found match. m is the match object,
# m.group(0) is the first match, re.escape() escapes the value and we finally
# use this as the key to fetch the value from the rep dict.
q = query.format(patterns[0])
print("Query: {}\n".format(q))
replaced = pattern.sub(lambda m: rep[re.escape(m.group(0))], q)
print("Processed query: {}\n".format(replaced))

# Now to test it with the examples you gave let's iterate over the patterns
# dict, form a new query string using each of them and run the regex against
# each one.
print("###########################")
print("Test each pattern:\n")
print("---------------------------")
for p in patterns:
    q = query.format(p)
    print("Pattern: {}".format(p))
    print("Original query: {}\n".format(q))

    replaced = pattern.sub(lambda m: rep[re.escape(m.group(0))], q)
    print("Processed query: {}\n".format(replaced))
    print("---------------------------\n")
</code></pre>

<p>You can read more about how <a href="https://docs.python.org/3/library/re.html#re.sub" rel="nofollow"><code>re.sub()</code></a> works.</p>

python string replace