pythonsqlfilephpmyadminmigration

Extract parts from sql sentences with pyhton script

I have a .sql file with CREATE TABLE AND INSERTS TABLE sentences in it. Example:

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')

I have to do a migration of that data in a database with tables that have a different structure of the .sql ones.

My question is: how can I get all the inserts of the file and from each of them the columns and values separated? Each insert should be a dictionary in a list.

I tried to use a pyhton script using regex and split(', ') each line of the values, but as some field have "," it's difficult to get the values correctly split.

Solution

  • This is very brittle, but will give you a list of dictionaries for statements formatted like you have provided.

    Given:

    statement = """
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')
""".strip().split("\n")

    Then you can use the ast package to parse your statement like:

    import ast

## -------------------------
## Assume the first line is the INSERT clause
## -------------------------
fieldnames = statement[0].replace("`", "'")
fieldnames = ast.literal_eval(" ".join(fieldnames.split(" ")[3:-1]))
## -------------------------

## -------------------------
## Assume remaining lines are data
## -------------------------
data = ast.literal_eval(" ".join(statement[1:]))
## -------------------------

## -------------------------
## Construct our desired result
## -------------------------
results = [dict(zip(fieldnames, row)) for row in data]
## -------------------------

    you can see the results using the json package for formatting:

    import json

print(json.dumps(results, indent = 4))

    That should give you:

    [
    {
        "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"
    }
]