How to serialize json to sql?

Viewed 92

I have a list of words and I should send requests to an API and get the information about the words. I want to convert the API data which is in JSON format to SQL(my DB is PostgreSQL) format in Django. How can I do that? Do you know any good source to learn to serialize json to sql? I have just started learning Django.

It is the API's JSON data:

  [
    {
      "word": "hello",
      "phonetics": [
        {
          "text": "/həˈloʊ/",
          "audio": "https://lex-audio.useremarkable.com/mp3/hello_us_1_rr.mp3"
        },
        {
          "text": "/hɛˈloʊ/",
          "audio": "https://lex-audio.useremarkable.com/mp3/hello_us_2_rr.mp3"
        }
      ],
      "meanings": [
        {
          "partOfSpeech": "exclamation",
          "definitions": [
            {
              "definition": "Used as a greeting or to begin a phone conversation.",
              "example": "hello there, Katie!"
            }
          ]
        },
        {
          "partOfSpeech": "noun",
          "definitions": [
            {
              "definition": "An utterance of “hello”; a greeting.",
              "example": "she was getting polite nods and hellos from people",
              "synonyms": [
                "greeting",
                "welcome",
                "salutation",
                "saluting",
                "hailing",
                "address",
                "hello",
                "hallo"
              ]
            }
          ]
        },
        {
          "partOfSpeech": "intransitive verb",
          "definitions": [
            {
              "definition": "Say or shout “hello”; greet someone.",
              "example": "I pressed the phone button and helloed"
            }
          ]
        }
      ]
    }
  ]

this is my models.py:

class Words(models.Model):
    word = models.CharField(max_length=50)
    american_phonetic= models.CharField(max_length=50)
    american_audio=  models.URLField(max_length = 200)
    british_phonetic= models.CharField(max_length=50)
    british_audio=  models.URLField(max_length = 200)

###########################################################################

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    birth_date = models.DateField('birth date')
    field= models.CharField(max_length=50)
    location = models.CharField(max_length=30, blank=True)
    interest= models.IntegerField() # for example :  1 for science , 2 for art , 3 for sport etc.
    education= models.IntegerField() # for example : 1 for highschool , 2 for bachelor , 3 for master and 4 for phd

@receiver(post_save, sender=User)
def create_user_profile(sender, instance, created, **kwargs):
    if created:
        Profile.objects.create(user=instance)

@receiver(post_save, sender=User)
def save_user_profile(sender, instance, **kwargs):
    instance.profile.save()

class UserLearned(models.Model):
    Profile_id = models.ForeignKey(Profile, on_delete=models.CASCADE)
    word_id = models.ForeignKey(Words, models.SET_NULL, blank=True, null=True,)

    def __str__(self):
        return self.word_id


############################################################################

class Meanings(models.Model):
    word_id = models.ForeignKey(Words, on_delete=models.CASCADE)
    partOfSpeech = models.CharField(max_length=30)

class Definitions(models.Model):
    word_id = models.ForeignKey(Words, on_delete=models.CASCADE)
    Meaning_id = models.OneToOneField(Meanings, on_delete=models.CASCADE, primary_key=True)
    definition = models.TextField()

    def __str__(self):
        return self.definition


class Examples(models.Model):
    word_id = models.ForeignKey(Words, on_delete=models.CASCADE)
    Meaning_id = models.OneToOneField(Meanings, on_delete=models.CASCADE, primary_key=True)
    example = models.TextField()

    def __str__(self):
        return self.example

class Synonyms(models.Model):
    word_id = models.ForeignKey(Words, on_delete=models.CASCADE)
    Meaning_id = models.ForeignKey(Meanings, on_delete=models.CASCADE)
    synonym = models.CharField(max_length=50)

    def __str__(self):
        return self.synonym
1 Answers

You can use the json library to serialise the json data and vice versa (use json.loads() & json.dumps() methods).

For Example:

import json

data = "{'name': 'Jack', 'age': 30}"     #json data
python_dict = json.loads(data)           #now it is a python dict

Now you can directly assign the values of the python dict to any model attributes in Django.

Following are some good sources to start learning the library:

Related