SQL to Plain Text - Python Programming Exercise

# Python program to parse SQL INSERT commands and extract data

import re

def parse_sql_insert(input_file, output_file):
    """
    Parses SQL INSERT commands from an input file and writes the extracted data to an output file.
    """
    try:
        with open(input_file, 'r', encoding='utf-8') as infile, open(output_file, 'w', encoding='utf-8') as outfile:
            for line in infile:
                # Match the INSERT INTO statement
                match = re.match(r"INSERT INTO (\w+) \((.+?)\) VALUES \((.+?)\);", line.strip())
                if match:
                    table_name = match.group(1)  # Extract table name
                    fields = match.group(2).split(", ")  # Extract field names
                    values = match.group(3).split(", ")  # Extract values
                    
                    # Clean up values (removing quotes)
                    values = [value.strip('"') for value in values]
                    
                    # Write the table name
                    outfile.write(f"Table: {table_name}\n\n")
                    
                    # Write the fields and their corresponding values
                    for field, value in zip(fields, values):
                        outfile.write(f"{field}: {value}\n")
                    
                    outfile.write("\n")  # Blank line between records
        print(f"Extracted data written to '{output_file}'.")
    except FileNotFoundError:
        print(f"Error: The input file '{input_file}' was not found.")
    except Exception as e:
        print(f"Error: {str(e)}")


# Example usage
if __name__ == "__main__":
    # File paths
    input_file = "insert_commands.sql"       # Input file containing SQL INSERT commands
    output_file = "parsed_output.txt"        # Output file for extracted data

    parse_sql_insert(input_file, output_file)

 Output

Input (insert_commands.sql):

INSERT INTO people (name, address, age) VALUES ("smith, pedro", "your street", 23);
INSERT INTO people (name, address, age) VALUES ("juan", "calle cinco, 6", 24);
INSERT INTO cities (code, name) VALUES ("a", "alicante");

Output (parsed_output.txt):

Table: people

name: smith, pedro
address: your street
age: 23

Table: people

name: juan
address: calle cinco, 6
age: 24

Table: cities

code: a
name: alicante