CODEWITHSUNDEEP
jsonpython-3.xflask-sqlalchemy
DangerPaws
DangerPaws

Reputation: 846

Python: import JSON file into SQLAlchemy JSON field

I'm relatively new to Python so I'm hoping that I've just missed something really obvious... But all the similar questions/answers here on StackOverflow seem really overly complex for the simple task that I am trying to achieve.

I have a few hundred text files containing JSON data (the actual data structure isn't important, this block below is just to show you what kind of thing I have, the actual structure of the data could be wildly different but it will always be valid JSON data).

{
    "config": {
        "item1": "value1",
        "item2": "value2"
    },
    "data": [
        {
            "dataA1": "valueA1",
            "itemA2": "valueA2"
        },
        {
            "dataB1": "valueB1",
            "itemB2": "valueB2",
            "itemB3": "valueB3"
        }
    ]
}

My Model is something like this:

class ModelName(db.Model):
    __tablename__ = 'table_name'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    data1 = db.Column(db.JSON)
    data2 = db.Column(db.JSON)

I have multiple data columns here, data1 and data2, simply so I can do a visual comparison of the inserted data. The final model will only have a single data field.

Here is the data insert where everything seems to be going wrong:

import json

new_record = ModelName(
    name='Foo',
    data1=open('./filename.json').read(),
    data2=json.dumps(open('./filename.json').read(), indent=2)
)
try:
    db.session.add(new_record)
    db.session.commit()
    print('Insert successful')
except:
    print('Insert failed')

The data that ends up in data1 and data2 get littered with varying numbers of \ to escape double quotes and line breaks, plus it wraps the whole data insert in a set of double-quotes. As a result, the data is simply unusable. So I'm currently having to copy and paste the data into the DB manually which although this tedious task works fine, it is far from the right thing to have to do.

I don't need to edit, manipulate, or do anything to the data in any way. I simply want to read the JSON string from a given file and then insert its content into a record in the database, that is it, end of story, nothing else.

Is there really no SIMPLE way to achieve this?

Upvotes: 1

Views: 1950

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169274

When you read in a file you need json.loads().
And there's no indent kwarg for that.
So instead do: 

    data2=json.loads(open('filename.json').read())

Upvotes: 2

Related Questions