Phpmyadmin – Extract parts from sql sentences with pyhton script

As there are some inconsistency with the data structure, I have added manual processing steps to clean the data.

Handling commas inside quotes:

Values in SQL data (like description or category) might contain commas inside them. If we split by commas directly, we would incorrectly break the data into multiple values.We use a manual loop over the values_str to handle this.
inside_quotes is a flag that tracks whether we are inside a quoted value (‘), and current_value accumulates characters of the current value.
We toggle inside_quotes whenever we encounter a quote (‘). If we’re outside quotes and encounter a comma (,), it indicates the end of the current value, so we add it to the values list.

import json

def parse_sql_inserts(sql_content):
    lines = sql_content.strip().split("n")
    first_line = lines[0]
    
    # Extract columns from the first line by finding everything between `(` and `)`
    columns_part = first_line.split("(", 1)[1].split(")", 1)[0]
    columns = [col.strip().strip('`') for col in columns_part.split(",")]
  
    result = []
    
    # Iterate over the rest of the lines (data values)
    for line in lines[1:]:
        if line.strip().startswith("VALUES"):
            continue
        
        # Remove the parentheses surrounding the values and strip extra spaces
        values_str = line.strip().strip("()")
        
        # Split the values by commas, ensuring we correctly handle commas inside quotes
        values = []
        current_value = ""
        inside_quotes = False
        for char in values_str:
            if char == "'" and (len(current_value) == 0 or current_value[-1] != '\'):
                inside_quotes = not inside_quotes  # toggle the state of inside_quotes
                current_value += char  # add the quote itself to the current value
            elif char == "," and not inside_quotes:
                # When we're not inside quotes, this is the end of a value
                values.append(current_value.strip().strip("'"))
                current_value = ""
            else:
                current_value += char
        if current_value:
            values.append(current_value.strip().strip("'"))  # add the last value
        
        # Ensure the number of values matches the number of columns (sanity check)
        if len(values) != len(columns):
            print(f"Warning: Skipping line with incorrect number of values: {line}")
            continue
        
        insert_dict = dict(zip(columns, values))
        result.append(insert_dict)
    
    return result

sql_content = """
INSERT INTO `examle` (`myid`, `title`, `value`, `usefull`, `picture`, `short_description`, `description`, `wikipedia`, `category`, `date`)
VALUES
(1, 'Cat', '3,45', 'No', 'cat.jpg', 'A very useless domestic cat.', 'The cat (Felis catus), also known as the domestic cat or housecat to distinguish it from other felines and felids, is a small carnivorous mammal that is valued by humans for its companionship and its ability to hunt vermin and household pests. It has been associated with humans for at least 9,500 years and is currently the most popular pet in the world.', 'http://en.wikipedia.org/wiki/Cat', 'Sport,Food,Creature', '2009-10-27 16:37:49'),
(3, 'Basketball', '9,45', 'Yes', 'basketball.jpg', 'A Baskeball sport utility.', 'Basketball is a team sport in which two teams of 5 players try to score points against one another by placing a ball through a 10 foot (3.048 m) high hoop (the goal) under organized rules. Basketball is one of the most popular and widely viewed sports in the world.', 'http://en.wikipedia.org/wiki/Basketball', 'Sport', '2009-10-27 16:36:39')
"""

inserts = parse_sql_inserts(sql_content)

json_output = json.dumps(inserts, indent=4)
print(json_output)

Output

[
    {
        "myid": "1",
        "title": "Cat",
        "value": "3,45",
        "usefull": "No",
        "picture": "cat.jpg",
        "short_description": "A very useless domestic cat.",
        "description": "The cat (Felis catus), also known as the domestic cat or housecat to distinguish it from other felines and felids, is a small carnivorous mammal that is valued by humans for its companionship and its ability to hunt vermin and household pests. It has been associated with humans for at least 9,500 years and is currently the most popular pet in the world.",
        "wikipedia": "http://en.wikipedia.org/wiki/Cat",
        "category": "Sport,Food,Creature",
        "date": "2009-10-27 16:37:49')"
    },
    {
        "myid": "3",
        "title": "Basketball",
        "value": "9,45",
        "usefull": "Yes",
        "picture": "basketball.jpg",
        "short_description": "A Baskeball sport utility.",
        "description": "Basketball is a team sport in which two teams of 5 players try to score points against one another by placing a ball through a 10 foot (3.048 m) high hoop (the goal) under organized rules. Basketball is one of the most popular and widely viewed sports in the world.",
        "wikipedia": "http://en.wikipedia.org/wiki/Basketball",
        "category": "Sport",
        "date": "2009-10-27 16:36:39"
    }
]