Skip to content

Instantly share code, notes, and snippets.

@lly365
Last active April 3, 2018 10:44
Show Gist options
  • Save lly365/1acada0706ed639030059a321e7a5781 to your computer and use it in GitHub Desktop.
Save lly365/1acada0706ed639030059a321e7a5781 to your computer and use it in GitHub Desktop.
sqlalchemy 处理 JSON 数据(添加、查找,聚合)
# 模型
from app import db
from sqlalchemy.dialects.postgresql import UUID, JSON
from sqlalchemy import text as sa_text
class User(db.Model):
id = db.Column(UUID(as_uuid=True), primary_key=True, default=sa_text("uuid_generate_v4()"))
username = db.Column(db.CHAR(16), unique=True, nullable=False)
profile = db.Column(JSON)
def __repr__(self):
return '<User %r>' % self.username
# 添加
@admin.route('/user/add')
def user_add():
from random import randint
# from uuid import uuid4
user = User(username='yesen' + str(randint(1, 9999)))
# profile = dict(age=randint(1, 99), face=str(uuid4()) + '.jpg', gender=randint(0, 1))
profile = dict(age=randint(1, 99), city='longnan', gender=randint(0, 1), rmb=randint(0, 999))
user.profile = profile
db.session.add(user)
db.session.commit()
return 'user added success: id:{}, username:{}, profile:{}'.format(user.id, user.username, user.profile)
# 查找
@admin.route('/user/find')
def user_find():
from sqlalchemy.types import Integer
# user = User.query.filter(User.profile['age'].astext.cast(Integer) >= 15).first()
# user = User.query.filter(User.profile['city'].astext == 'longnan').first()
# user = User.query.filter(User.profile['rmb'].astext.cast(Integer) >= 15).first()
user = User.query.filter(User.profile['gender'].astext.cast(Integer) == 1).first()
if user:
return 'user finded: id:{}, username:{}, profile:{}'.format(user.id, user.username, user.profile)
else:
return 'not found'
# 聚合(COUNT)
@admin.route('/user/count')
def user_count():
from sqlalchemy.types import Integer
uc = User.query.filter(User.profile['gender'].astext.cast(Integer) == 1).count()
return 'Count %r' % uc
# 聚合(SUM)
@admin.route('/user/sum')
def user_sum():
from sqlalchemy.types import Integer
from sqlalchemy.sql import func
s = User.query.with_entities(func.sum(User.profile['rmb'].astext.cast(Integer))).filter(
User.profile['gender'].astext.cast(Integer) == 1).scalar()
return 'sum %r' % s
@naamancampbell
Copy link

Can you post the JSON data as well?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment