Writing the value of certain keys of a json file to a postgresql table with sqlalchemy

Question

How do I write the values of certain keys contained in the result.json file to the table I created. the values in the certain keys will be written in the columns I have created. I have id, title, score columns in my table. I want to write the value opposite the title key in the title column and the value opposite the score key in the score column.

result.son file:

[
  {
    "title": "Street Basketball Association",
    "appId": "com.sba.android.googleplay",
    "url": "https://play.google.com/store/apps/details?id=com.sba.android.googleplay",
    "icon": "https://play-lh.googleusercontent.com/a17t8kK_A4TMJtV0l6oTGzOvLbu_QlnKiWZM3L4RB-xprNL57z1YAJlal9ewEPrWxrW3=w240-h480-rw",
    "developer": "ShakaChen",
    "currency": "USD",
    "price": 0,
    "free": true,
    "summary": "We will bring you the hottest basketball game experience.You can invite other players to a real-time online competition or rank up while playing various league, cups and events in spectacular arenas.",
    "scoreText": "4.0",
    "score": 4.315321
  }
]

the name of my database is test :in this database, I have a table with one test_table.

My code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.dialects.postgresql import JSON


app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://username:password@localhost:5432/test'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


class Test(test.Model):
    __tablename__ = 'test_table'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))
    score = db.Column(db.Float)

with open('./result.json') as my_file:
    dicts = load(my_file)

for dictionary in dicts:
    test_data = Test(app_name=dictionary['title'],
                                    developer=dictionary['developer'],
                                    score=dictionary['score'])

    db.session.add(test_data)
    db.session.commit()

user_15561299 · Answer

First of all, your Person model has score field set to db.Integer, yet in the data you're getting, score is set to float. This will cause a Data MisMatch error.

Now, for the json part.

You import the load method from the json module so you're able to convert json objects to Python dicts.

from json import load

You open your file, preferably inside a context so the file gets automatically released once you're done with it.

with open('result.json') as my_file:
    dicts = load(my_file)

Now dicts is a list of dict objects on which you can operate. You initiate a new instance of your Person db.model and assign each of the values to the appropriate field.

for dictionary in dicts: #Remember, you have a list of dicts.
    person = Person(app_id=dictionary['appId'], 
                  title=dictionary['title'], score=dictionary['score']) 
                  #Remember to set the score field to the appropriate datatype. 
                  #i.e: db.Float
    db.session.add(person) #Add your new person instance to the session.
    db.session.commit() #Persists the user to your database.

Update: Based from what I understood from your comment. You can set your db.Model as follows :

class Person(db.Model):
    __tablename__ = 'test'
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    app_id = db.Column(db.String(10), index=True)
    title = db.Column(db.String(255))
    score = db.Column(db.Float) #You're getting float values.

Now, you have the id set by your database and the app_id from the json file. And you always have the option to search by the former or the latter.

Writing the value of certain keys of a json file to a postgresql table with sqlalchemy

1 Answers