CODEWITHSUNDEEP
jsonsqliteflasksqlalchemy
user2156115
user2156115

Reputation: 1257

Is there a way how to save json with flask_sqlalchemy with sqlite backend

I am trying to save data in form of JSON (returned as result from POST request)

def get_data(...):
    ...

    try:
        _r = requests.post(
            _url_list,
            headers=_headers
        )

        return _r.json()
    except Exception as ee:
        print('Could not get data: {}'.format(ee))
        return None

Into a table in SQLITE database as backend.

def add_to_flight_data(_data):
    if _data:
        try:
            new_record = FlightData(data=_data)
            db.session.add(new_record)
            db.session.commit()
            print('Data instertedto DB!')
            return "Success"
        except Exception as e:
            print('Data NOT instertedto DB! {}'.format(e))
            pass

This is my simple flask code

import os
import time
import auth
import json
import requests
import datetime
from flask import Flask
from flask_marshmallow import Marshmallow
from flask_sqlalchemy import SQLAlchemy
# from safrs.safrs_types import JSONType

project_dir = os.path.dirname(os.path.abspath(__file__))
database_file = "sqlite:///{}".format(os.path.join(project_dir, "2w.sqlite"))

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = database_file
db = SQLAlchemy(app)
ma = Marshmallow(app)


class FlightData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, server_default=db.func.now())
    json_data = db.Column(db.JSONType, default={})

    def __init__(self, data):
        self.data = data

It seems like there is perhaps no option to save JSON in sqlite json_data = db.Column(db.JSONType, default={})

Please ADVISE Thanks.

Upvotes: 3

Views: 14044

Answers (1)

mhawke
mhawke

Reputation: 87134

I believe that you should be using db.JSON, not db.JSONType as there is no such column type in sqlalchemy.

Regardless of that, SQLite has no JSON data type, so sqlalchemy won't be able to map columns of type db.JSON onto anything. According to the documentation only Postgres and some MySQL are supported. There is support for JSON in SQLite with the JSON1 extension, but sqlalchemy will not be able to make use of it.

Your best bet then is to declare the column as db.Text and use json.dumps() to jsonify the data on write. Alternatively modify your get_data() function to check for a JSON response (check the Content-type header or try calling _r.json() and catching exceptions), and then return _r.content which will already be a JSON string.

Use json.loads() to read data back from the db.

Upvotes: 6

Related Questions