python(三)基于flask返回json数据接口：flask-sqlalchemy详细介绍

最新推荐文章于 2023-04-20 11:55:43 发布

networksu

最新推荐文章于 2023-04-20 11:55:43 发布

阅读量1.1w

点赞数 7

分类专栏： python 文章标签： flask-sqlalchemy

本文链接：https://blog.csdn.net/networksu/article/details/84332695

版权

python 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

因为不关心前台页面的开发，前台工程师给我提供了一份json样式。让我实现，然后给出了筛选条件。还是接着上篇文章，将ORM这块仔细写写。这篇文章也将关于flask-sqlalchemy的总结，我会把用户遇到的格式问题都总结在这里面。我写博客的原则，用过什么写什么，没涉及的也不瞎写了。

首先看前台工程师给我提供的json返回格式样例。大概就是以song为对象的list，里面有嵌着多对多关系的歌手和一对多的版权

{
  "Songs": [
    {
      "ID": 28,
      "artist": [
        "吴克群"
      ], 
      "lyric": "", 
      "title": "大舌头"
      "copyrights": {
        "lyric": [
          {
            "OP": "成果音乐版权有限公司", 
            "SP": "", 
            "disctrict": "", 
            "endDate": "永久", 
            "lyricists": "吴克群", 
            "share": "50.0%", 
            "startDate": ""
          }
        ]
      }
    }
   ], 
  "paging": {
    "page-index": 1, 
    "page-size": 20, 
    "total": 25
  }
}

上篇文章的时候我已经将model层的代码粘了出来，这里再粘贴一次

from mock import db

artist_songs = db.Table('artist_songs',
    db.Column('song_id', db.Integer, db.ForeignKey('songs.id')),
    db.Column('artist_id', db.Integer, db.ForeignKey('artists.id'))
    )  

class songs(db.Model):
    __tablename__ = 'songs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(255), nullable=True)
    lyrics = db.Column(db.Text(), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False)
    artists=db.relationship('artists', secondary=artist_songs,backref = db.backref('artists'),lazy="select")
    lyric_copies=db.relationship('lyric_copies',backref='songs',lazy='select')
  
     def jsonstr(self):
        artist=[]
        if self.artists is not None and len(self.artists)!=0:
            for art in self.artists:
                artist.append(art.name)
        
        lyricarr=[]
        if self.lyric_copies is not None and len(self.lyric_copies)!=0:
            for lyr in self.lyric_copies:
                lyricarr.append(lyr.jsonstr())
                
        jsondata = { 
            'ID': self.id, 
            'title': self.title,
            'artist': artist,
            'lyric':self.lyrics,
            'copyrights':{
                'lyric':lyricarr
            }
        }
        return jsondata

class artists(db.Model):
    __tablename__ = 'artists'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=True)
    gender = db.Column(db.Integer, nullable=True)
    location = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False)
    updated_at = db.Column(db.DateTime, nullable=False)

class lyric_copies(db.Model):
    __tablename__ = 'lyric_copies'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=True)
    end_date=db.Column(db.DateTime, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False)
    updated_at = db.Column(db.DateTime, nullable=False)
    song_id = db.Column(db.Integer, db.ForeignKey('songs.id'),nullable=False)

    def jsonstr(self):
        enddatestr=''
        if self.end_date is not None and self.end_date!='':
            enddatestr=self.end_date.strftime('%Y-%m-%d')
        else:
            enddatestr='永久'
  
        jsonstr={
            "lyricists": self.name,      
            "endDate": enddatestr
        }
        return jsonstr

这里有几个知识点：flask-sqlalchemy的文档不是很好，所有的注释都在源代码里，可以连接过去看看源代码。

1)引入在初始化时生成db对象

2)所有model类要继承db.Model 类

3）__tablename__表示对应的表名

主键：primary_key=True，如果是联合主键在多个列标注上

自增长：autoincrement=True，只有主键是Integer时才行，只能有一列

外键：db.ForeignKey('songs.id')，songs表示songs类，songs.id表示songs表的id列

索引：index=True，说明该列建了索引

不能为空：nullable=True时，表示该列不能为空，如果nullable=False，表示default=NULL

默认值：server_default，eg：Column('y', DateTime, server_default=text('NOW()'))

唯一值：unique=True时，表示该列数值独一无二。此时该列默认index=True

系统列：system=True，表示该列是python程序自己列，并不会跟数据库关联

5）类每个属性对应数据库表中每列的名称

数据库类型	Sqlalchemy类型	Python类型	说明
INT	Integer	int	普通整数,一般是32位
SMALLINT	SmallInteger	int	取值范围小的整数,一般是16位
BIGINT	BigInteger	int或long	不限制精度的整数
FLOAT、REAL	Float	float	浮点数
VARCHAR	String	string
TEXT	Text	string
NUMERIC、DECIMAL	Numeric	decimal.Decimal	定点数
NVARCHAR、unicode	Unicode	unicode
Date	Date	datetime.date	日期
Time	Time	datetime.time	时间
DateTime	DateTime	datetime.datetime	日期和时间

以上是基本列的声明。其实用到也就那么几个，相信数据库端设计也不会太超出常规。

下面着重对flask-sqlalchemy的一对多和多对多的声明进行讲解：

一）1：N关系

一首歌有多个版权商，每个版权对应某首歌，所以在版权表里有song_id表示的是songs表的id，这里作为外键。在表述列的时候要如此描述：

song_id = db.Column(db.Integer, db.ForeignKey('songs.id'),nullable=False)

那么对于songs来说，它就有多个lyric_copies.

将lyric_copies看作songs的一个对象来看，描述的方法如下：

lyric_copies=db.relationship('lyric_copies',backref='songs',lazy='select')
'lyric_copies'表示lyric_copies类、backref表示反向对应的类，lazy可以理解lyric_copies的加载时机，后面详见，默认是select

二）N:N关系

多对多的关系一般都会有个中间表用来保存彼此关系。在本例中歌手与歌曲的关系，被保存在artist_songs表中。

表也很简单声明相对应列对应哪些表的外键。因为要在songs里面引用artist_songs对象，所以声明放在songs类之前。

artists=db.relationship('artists', secondary=artist_songs,backref = db.backref('artists'),lazy="select")

第一个参数'artists',表示对应的那个类

secondary表示中间表是哪个，等于artist_songs对象

backref = db.backref('artists')表示反向查找，说实话没弄明白

lazy同上表示该对象的加载时机。

怎么通过Flask-sqlalchemy构造SQL语言

说完了关于数据库到对象的映射描述以后，我们接下来介绍一下一对一，一对多的增删改查操作。在谈具体细节之前，想说一下我对ORM框架的理解，因为之前一直在拼装SQL或将业务逻辑在存储过程里面完成。对ORM将结果映射在对象的做法无法理解。在学习sqlalchemry以后觉得，到底在用ORM还是SQL这个问题上，增删改和简单查询用ORM比较好、方便快捷。但是复杂的报表开发，用ORM那就是自掘坟墓了。所以看情况使用，当然作为ORM的初学者，一点微薄个人见解。

我将所有ORM编写都放在了BSL层里，先从最关键的查询写起。

在aritistbsl.py里面，先引用相关类型，db为上一文中，初始化生成对象

from mock import db
from mock.model.mockmodel import songs,artists,lyric_copies
from sqlalchemy.sql.expression import or_, and_

查询所有歌手

class ArtistBSL   
    def QueryArtist(self):
        return db.session.query(Artist)

这样返回的就是查询Artist表的sql语句，类型是Basequery，只有当Basequery后面跟上.all(),.first(),.count(),paginate(int,int,bool)以后才真正转换为相应对象。

.all()返回所有结果list

.first()返回第一个对象

.count()返回int型数据，行数

.pageinate()返回list对象，结果集分页(pageindex,pagesize,false) 表示（第几页，每页行数，遇到error是否raise）

构造SQL语句

#查询歌手
db.session.query(Artists)
#查询name为吴克群的歌手
db.session.query(Artists).filter(Artists.name=='吴克群')
#查询姓吴的歌手
db.session.query(Artists).filter(Artists.name.like('吴%'))
#查询姓吴歌手并且是男性
db.session.query(Artists).filter(and_(Artists.name.like('吴%'),Artists.gender=='1'))
#查询姓吴或姓周的歌手
db.session.query(Artists).filter(or_(Artists.name.like('吴%'),Artists.name.like('周%')))


#songs关联歌手表查询
db.session.query(songs).join(songs.artists).filter(songs.title=='大舌头',artists.name=='吴克群')
#查询songs关联artists，输出歌手姓名和歌曲
db.session.query(songs.title,artists.name).join(songs.artists).filter(songs.title=='大舌头',artists.name=='吴克群')
#查询songs关联artists，输出歌手姓名和歌曲,并重命名列名
db.session.query(songs.title.label('songname'),artists.name.label('artistname')).join(songs.artists).filter(songs.title=='大舌头',artists.name=='吴克群')
#查询songs并左关联版权
db.session.query(songs).outjoin(songs.lyrice_copies)

#所有歌名并且去重
db.session.query(songs.name).distinct()

动态增加筛选条件

|id |title |
| 1 |song1 |
| 2 | |
| 3 |NULL |
------------------
当前台传来的参数title为''时，我们想查询到所有的结果
ORM:
db.session.query(songs).filter(songs.title.like('%'+parm+'%'))
相当于SQL:
select * from songs where songs.title.like '%%'

可是这样我们会把id=3为空的筛除掉。这时候就需要动态创建查询SQL，条件中就不增加where语句。
python应该为

condition=set()
if parm !='':
condition.add(songs.title.like('%'+parm+'%'))
db.session.query(songs).filter(*condition)

当parm为''时，SQL相当于
select * from songs

复合条件嵌套查询
db.session.query(songs).filter(and_(*condition1,or_(*condition2)))
常见条件运算符号

SQL	PYTHON	备注用法
AND	and_	and_(条件1，条件2）
OR	or_	or_(条件1，条件2）
IN	in_	songs.title.in_(条件1，条件2）或者in_(list<>)
not IN	notin_	同上
Between	between	lyricescopies.share.between(0,100)包括边界值
LIKE	like	songs.title.like('%'+条件1+‘%’)
NOT LIKE	notlike	同上

查询结果怎么用？返回JSON！JSON怎么构造？

现在大部分系统已经做到前后台分离，中间靠JSON来传递。而我们查到的结果又需要一些特殊处理，比如当我们查到授权结束时间为空时，需要将输出字段显示为“永久”，或者输出键为“lyricists”对应对象name，那么这种情况都需要自己构造返回JSON格式，python里面最符合JSON的就是dict类型。在songs类里面添加jsonstr方法。类似下面情况，可以把一对多和多对多对象当list来处理。

class songs(db.Model):
    __tablename__ = 'songs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(255), nullable=True)
    lyrics = db.Column(db.Text(), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False)
    artists=db.relationship('artists', secondary=artist_songs,backref = db.backref('artists'),lazy="select")
    lyric_copies=db.relationship('lyric_copies',backref='songs',lazy='select')
  
     def jsonstr(self):
        artist=[]
        if self.artists is not None and len(self.artists)!=0:
            for art in self.artists:
                artist.append(art.name)
        
        lyricarr=[]
        if self.lyric_copies is not None and len(self.lyric_copies)!=0:
            for lyr in self.lyric_copies:
                lyricarr.append(lyr.jsonstr())
                
        jsondata = { 
            'ID': self.id, 
            'title': self.title,
            'artist': artist,
            'lyric':self.lyrics,
            'copyrights':{
                'lyric':lyricarr
            }
        }
        return jsondata

class lyric_copies(db.Model):
    __tablename__ = 'lyric_copies'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=True)
    end_date=db.Column(db.DateTime, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False)
    updated_at = db.Column(db.DateTime, nullable=False)
    song_id = db.Column(db.Integer, db.ForeignKey('songs.id'),nullable=False)

    def jsonstr(self):
        enddatestr=''
        if self.end_date is not None and self.end_date!='':
            enddatestr=self.end_date.strftime('%Y-%m-%d')
        else:
            enddatestr='永久'
  
        jsonstr={
            "lyricists": self.name,      
            "endDate": enddatestr
        }
        return jsonstr

根据结果集自动生成JSON

那么我们的结果可能千奇百怪，有些时候我们需要根据结果的列名自动生成JSON。比如我们要获取songs的id、title，比如：

[

{"Label":"世界末日","Value":1},

{"Label":"稻香","Value":2},

{"Label":"大舌头","Value":3}

]

ORM语句我们会这样写：

datalist=db.session.query(songs.title.label('label'),songs.id.label('Value')).all()

这时候datalist就是一个<class 'sqlalchemy.util._collections.result'>的list集合

sqlalchemy.util._collections.result对象保存列值也包括列名

写了一个静态方法，将list集合转为JSON数组。

class JSONHelper():
    @staticmethod
    def jsonBQlist(bqlist):
        result=[]
        for item in bqlist:
            jsondata={}
            for i in range(item.__len__()):
                tdic={item._fields[i]:item[i]}
                jsondata.update(tdic)  
            result.append(jsondata)
        return result

在flask的app下使用办法。

@app.route('/querytags', methods=['GET', 'POST'])
def querytags():
    tagslist=TagsBSL().QueryTags()
    return jsonify(JSONHelper.jsonBQlist(tagslist))

一对多、多对多关系复杂关系的声明和查询

接下来的问题困扰了很长时间，也查了很多代码。就是一对多和多对多关系声明的时候，可能需要联合条件才能行。怎么处理？比如如图所示：

networksu

关注

7
点赞
踩
35

收藏

觉得还不错? 一键收藏
3
评论
python(三)基于flask返回json数据接口：flask-sqlalchemy详细介绍

因为不关心前台页面的开发，前台工程师给我提供了一份json样式。让我实现，然后给出了筛选条件。还是接着上篇文章，将ORM这块仔细写写。这篇文章也将关于flask-sqlalchemy的总结，我会把用户遇到的格式问题都总结在这里面。我写博客的原则，用过什么写什么，没涉及的也不瞎写了。首先看前台工程师给我提供的json返回格式样例。大概就是以song为对象的list，里面有嵌着多对多关...
复制链接

扫一扫