Import json with missing keys into postgres with Flask-Sqlalchemy

Question

I have a question you guys might be able to answer.

I have a json file that looks something like this:

[
{
        "address": "some address",
        "full_time_school": false,
        "name": "some name",
        "official_id": "722154",
        "school_type": "Grundschule",
        "school_type_entity": "Grundschule",
        "state": "BW"
    },
    {
        "address": "some other address",
        "name": "some other name",
        "official_id": "722190",
        "state": "BW"
    }
]

The point is that not every entry has all keys.

I have a flask-sqlalchemy model that looks like this:

class School(db.Model):

    __tablename__ = "school"  # pragma: no cover

    address = db.Column(db.String)
    full_time_school = db.Column(db.Boolean)
    name = db.Column(db.String)
    official_id = db.Column(db.Integer)
    school_type = db.Column(db.String)
    school_type_entity = db.Column(db.String)
    state = db.Column(db.String)

    def __repr__(self):
        return f"<name {self.name}"

And I have a python script to add the json entries into my postgresql database that looks like this:

from my_project import db
from my_project.models import School

import json
import os

# insert data
for filename in os.listdir("datamining"):
    if filename.endswith(".json"):

        file = open(os.path.join("datamining", filename))

        print(f"Add schools from {filename.strip('.json')}")

        data = json.load(file)

        cleaned_data = {school["official_id"]: school for school in data}.values()
        print(f"Adding {len(data)} schools to the database.")

        for school in cleaned_data:
            entry = School(
                    id=school["official_id"]
                )
            for key, value in school.items():
                entry.key = value
            
            db.session.add(entry)
            db.session.commit()
        file.close()

print("Added all schools!!!")

I don't know why but somehow every cell is NULL except the official_id field. How so and how can I fix that? I'm at the end of my wits right now. Every pointer or help is much appreciated.

EDIT: What I found out so far is, that entry.key is not interpreted as entry.state for example, but actually creates a reference entry.key = "BW" for example. Why is that?

score 0 · Accepted Answer · answered Dec 13 '21 at 21:35

0

Your problem is

entry.key = value

You are just writing your values over and over into the attribute 'key' within your School model. I'm actually surprised SQLAlchemy doesn't raise some kind of error here... Just pass all your values into the constructor and you should be fine:

school["id"] = school.pop("official_id")
entry = School(**school)

EDIT: It's "BW" because this happens to be the last value that is written into the attribute.

answered Dec 13 '21 at 21:35

thisisalsomypassword

1,423
1
6
17

I just realized that `official_id` is a field in your database. I'm not familiar with Flask-SQLAlchemy and how to pass ids. I could imagine that 'id' is actually set by the database and updated in the session on a flush. Then you could omit the line where I pop school[`official_id`]. – thisisalsomypassword Dec 13 '21 at 21:55

Stefanov.sm · Answer 2 · 2021-12-14T12:29:40.470

0

You can do this much easier and faster all in one go by executing this native parameterized query passing the text contents of the JSON file as parameter jsontext:

insert into school 
 select * from jsonb_populate_recordset(null::school, :jsontext::jsonb);

edited Dec 14 '21 at 12:29

answered Dec 13 '21 at 21:50

Stefanov.sm

11,215
2
21
21

Thanks! I went with the above solution, but I will try your solution the next time I have to import json data :) – bagdenaj Dec 17 '21 at 20:19

Import json with missing keys into postgres with Flask-Sqlalchemy

2 Answers2