如何用Python从存储过程文件中提取含嵌套的完整SQL语句？

Approach #

Forget using split()—it fails hard on nested queries. Instead, use a regex pattern that accounts for nested parentheses and quoted strings to correctly identify the top-level semicolon ending each target statement.

Solution Code #

import re

def extract_sql_statements(file_path):
    with open(file_path, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # Regex pattern to match SELECT/INSERT/UPDATE/DELETE statements (case-insensitive)
    # Ignores semicolons inside parentheses, single quotes, double quotes
    pattern = r'''
        (?i)\b(SELECT|INSERT|UPDATE|DELETE)\b  # Match start keyword (case-insensitive)
        (?:
            [^'"();]+                          # Characters that aren't quotes, parentheses, semicolon
            |
            '([^'\\]|\\.)*'                    # Single-quoted strings (escape-aware)
            |
            "([^"\\]|\\.)*"                    # Double-quoted strings (escape-aware)
            |
            \(                                 # Opening parenthesis
                (?:[^()]+|(?R))*               # Recursively match content inside parentheses
            \)                                 # Closing parenthesis
        )*
        ;                                      # Ending semicolon
    '''
    
    # Find all matches
    statements = re.findall(pattern, content, re.VERBOSE)
    
    # Clean up matches to get full valid statements
    cleaned_statements = []
    for match in statements:
        full_stmt = ''.join(filter(None, match)).strip()
        if not full_stmt.endswith(';'):
            full_stmt += ';'
        cleaned_statements.append(full_stmt)
    
    return cleaned_statements

# Example usage
if __name__ == "__main__":
    statements = extract_sql_statements("stored_procedure.sql")
    for idx, stmt in enumerate(statements, 1):
        print(f"--- Statement {idx} ---")
        print(stmt)
        print()

Key Details #

• The regex uses recursive matching via (?R) to handle nested parentheses, which is critical for capturing nested queries without splitting early.
• It skips semicolons inside single/double quoted strings to avoid false splits.
• The (?i) flag ensures the start keyword matches any case (e.g., select, INSERT are both captured).

Example Input & Output #

Sample Input #

CREATE PROCEDURE GetUserOrders
AS
BEGIN
    -- Get active users
    SELECT u.id, u.name FROM Users u WHERE u.is_active = 1;
    
    -- Insert a test order
    INSERT INTO Orders (user_id, total) 
    VALUES ((SELECT id FROM Users WHERE name = 'Alice'), 99.99);
    
    UPDATE Users SET last_login = GETDATE() 
    WHERE id IN (SELECT user_id FROM Orders WHERE order_date > '2024-01-01');
    
    DELETE FROM OrderItems WHERE order_id IN (
        SELECT id FROM Orders WHERE status = 'Cancelled'
    );
END

Expected Output #

1. SELECT u.id, u.name FROM Users u WHERE u.is_active = 1;
2. INSERT INTO Orders (user_id, total) VALUES ((SELECT id FROM Users WHERE name = 'Alice'), 99.99);
3. UPDATE Users SET last_login = GETDATE() WHERE id IN (SELECT user_id FROM Orders WHERE order_date > '2024-01-01');
4. DELETE FROM OrderItems WHERE order_id IN (SELECT id FROM Orders WHERE status = 'Cancelled');

内容的提问来源于stack exchange，提问作者dbNovice