Stored Procedure File search between 2 keywords to parse all SQL

Question

I want to search between 2 keywords (SELECT/INSERT/UPDATE/DELETE and semicolon ;) in my file of stored procedure code to filter all SQL queries in the file using python. 1 select(or even insert) can have nested select so if there is any select keyword it should continue till it find semicolon ignoring any other select/insert/delete/update keyword in between.

The SQL query can be start of line or may not be & it will be multiline.

select, insert, delete, update keyword can be in upper or lowercase.

Can anyone help with that?

I tried split function but didn't work.

Sample Text input file:

create procedure get_user ( in p_user_id int unsigned) 
begin
   declare v_id int; 
   select ADDR_ID into v_id from address where ADDR_ID = v_user_id;  
   new_v_id=v_id+1 
   delete from address_2 where ADDR_ID = new_v_id;
   select * from address_2 
   where ADDR_ID in
   (Select ADDR_ID from address 
     where ADDR_ID = v_user_id);  
 end

Required Output:

select ADDR_ID into v_id from address where ADDR_ID = v_user_id;  
delete from address_2 where ADDR_ID = new_v_id;
select * from address_2 where ADDR_ID in (Select ADDR_ID from address where ADDR_ID = v_user_id);

score 0 · Answer 1 · answered Jul 22 '22 at 20:11

Referred the link & solved this problem.

How to extract text between two substrings from a Python file

import re

start = ['SELECT','select','DELETE','delete','INSERT','insert','UPDATE','update']
end = ';'

for i in start:
    rx = r'{}.*?{}'.format(re.escape(i), re.escape(end)) # Escape special chars, build pattern dynamically
    with open('sp.txt') as myfile:
        copy = False
        contents = myfile.read()                     # Read file into a variable
        for match in re.findall(rx, contents, re.S): # Note re.S will make . match line breaks, too
            # Process each match individually
            print (match)

Stored Procedure File search between 2 keywords to parse all SQL

1 Answers1