flask-sqlalchemy 怎么设置json格式的字段

Question

flask-sqlalchemy 怎么设置json格式的字段

Answer 1

有两种方法：
1.sqlalchemy中PickleType可以对应任何python对象.
2.可以通过编写方法的方式生成json.

具体代码如下：

#!usr/bin/env python
# coding:utf-8
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:psw@localhost/test_json'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

class Users(db.Model):
    __tablename__ = 'users'
    user_id = db.Column(db.Integer, primary_key=True)
    user_info = db.Column(db.PickleType)

    def __repr__(self):
        return self.user_info


class Students(db.Model):
    __tablename__ = 'students'
    student_id = db.Column(db.Integer, primary_key=True)
    student_name = db.Column(db.String(80), unique=True)
    student_age = db.Column(db.Integer)
    student_sex = db.Column(db.String(20))

    def to_json(self):
        json_student = {
            'student_id': self.student_id,
            'student_name': self.student_name,
            'student_sex': self.student_sex,
            'student_age': self.student_age
        }

        return json_student

    def __repr__(self):
        return '%r' % self.student_id


if __name__ == "__main__":
    #-----------------------生成-----------------------
    USER_INFO = {"name":"nisiwa", "mail":"[e-mail]"}
    db.create_all()
    USER = Users(user_info=USER_INFO)
    STUDENT = Students(student_name="nisiwa", student_age="28", student_sex="male")
    db.session.add(USER)
    db.session.add(STUDENT)
    db.session.commit()
    #-----------------------调用------------------------
    user = Users.query.filter_by(user_id=1).first()
    student = Students.query.filter_by(student_id=1).first()
    print 'student:', student.to_json()
    print 'user:', user.user_info

结果如下：

Answer 2

今天我也碰见这个需求了，PickleType在mysql应该是存储为二进制类型。MySQL从版本5.7开始支持JSON格式，因此想在flask-sqlalchemy使用JSON字段的话，先确保MySQL版本是否满足要求。

具体代码如下，其中template字段为JSON格式

db = SQLAlchemy()

# 表结构
class CompareTemplate(db.Model):
    __tablename__ = "compare_templates"

    _id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    template = db.Column(db.JSON, nullable=False)

    @property
    def serialize(self):
        return {
                '_id': self._id,
                'name': self.name,
                'template': self.template,
                }

# 插入数据
def save_compare():
    data = request.get_json()
    name = data['name']
    template = data['template']
    obj = CompareTemplate(name=name, template=template)
    db.session.add(obj)
    db.session.commit()

Navicat中查看（navicat版本需要>11）