Ejercicio
SQL A Texto Sin Formato
Objectivo
Desarrollar un programa Python para analizar comandos SQL INSERT y extraer sus datos en líneas de texto independientes, de la siguiente manera. Si el archivo de entrada contiene estas tres líneas:
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");
El archivo resultante debe tener en cada línea el nombre de un campo, seguido de dos puntos y su valor. Además, cada registro debe ir precedido del nombre de la tabla y seguido de una línea en blanco, de esta manera:
Tabla: people
name: smith, pedro address: your street age: 23
Tabla: people
name: juan address: calle cinco, 6 age: 24
Tabla: cities
code: a name: alicante
Ejemplo de ejercicio de Python
Mostrar código Python
# 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
Código de ejemplo copiado
Comparte este ejercicio de Python