Generic code or Python function for finding multiple occurrences of substrings and character in string using Python?

Question

I am a beginner in Python. I am trying to solve the following problem. I have a list of SQL queries in a .txt file (just for example):

SELECT first_name, last_name From table1 WHERE ID = 1234 AND date_of_birth = 19/01/1996;
SELECT last_name From table2 WHERE ID = 4563 AND date_of_birth =    10/04/1997 AND lisence_no = 2874;
SELECT first_name From table1 WHERE ID = 3762 AND age = 23 AND last_name = 'Maria';
SELECT first_name From table1 WHERE ID = 2894;

and so on...... now I want to replace the specific values like ID, last_name, lisence_no etc. in the query to simply '?'

so that I want to get an output like this:

SELECT first_name, last_name From table1 WHERE ID = '?' AND date_of_birth = '?';
SELECT last_name From table2 WHERE ID = '?' AND date_of_birth = '?' AND lisence_no = '?';
SELECT first_name From table1 WHERE ID = '?' AND age = '?' AND last_name = '?';
SELECT first_name From table1 WHERE ID = '?';

What function can I use in python to get this type of output for a huge list of sql queries?

I know functions like str.find, or str.index to search for only one occurrence and will involve some working around. I am expecting some expert python user know about a function or a combination of functions that will be useful in my case.

Thanks much!

you might need an sql parser (e.g., old question: [Parsing SQL with Python](http://stackoverflow.com/q/1394998/4279)) or if your input is regular enough then you could use regular expressions (`re` module). Or if it is something in between then you could create your own language to parse the file, [code example](http://stackoverflow.com/q/26184812/4279) — jfs, May 23 '16 at 15:56
Have you noticed that all the data you want to replace follows `=` signs .. if that's the case for the entire file it shouldn't be too hard. — henrikstroem, May 23 '16 at 16:13

erolkaya84 · Answer 1 · 2016-05-23T16:28:29.730

I wrote a small script for this. Hope it will work fine with you.

def replaceCustom(inputField, str):
    index = 0
    index2 = 1
    while(index<index2):
        index = str.find(inputField,index)
        index2 = str.find("AND",index2)

        str = str.replace((str[index:index2]), inputField+ " = '?' ")
        index +=1
        index2 +=1
        print (index, index2)

    return str     

str = """SELECT first_name, last_name From table1 WHERE ID = 1234 AND date_of_birth = 19/01/1996; 
SELECT last_name From table2 WHERE ID = 4563 AND date_of_birth =    10/04/1997 AND lisence_no = 2874;  
SELECT first_name From table1 WHERE ID = 3762 AND age = 23 AND last_name = 'Maria'; 
SELECT first_name From table1 WHERE ID = 2894;"""

str = replaceCustom("ID", str)
str = replaceCustom("date_of_birth", str)
str = replaceCustom("lisence_no", str)

I tested it and it seems work.

Generic code or Python function for finding multiple occurrences of substrings and character in string using Python?

1 Answers1