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

Question

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.

mhawke · Accepted Answer · 2018-06-18 12:47:46Z

6

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.

edited Jun 18, 2018 at 12:47

answered Jun 16, 2018 at 13:54

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilja Everilä Over a year ago

The JSON1 support is coming: bitbucket.org/zzzeek/sqlalchemy/issues/3850/…

mhawke Over a year ago

@IljaEverilä: OK that's interesting. Don't know if it will arrive in time for the OP.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related