flask中一般使用flask-SQLAlchemy来操作数据库，使用起来比较简单，易于操作。而flask-SQLAlchemy是SQLAlchemy的flask实现。
在Flask-SQLAlchemy中，查询操作是通过query对象操作数据。最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。
本部分之所以迟迟未交差，主要是flask-SQLAlchemy关于query的用法实在太少了，而且没有几个是比较全面的，动手加验证花了不少的时间，而且很多等效的语法，想比较出来个优劣势，选择最适合自己的。
本文主要验证了单表的全表查询、单表的单字段查询（like、==、>、<、in等等），单表的多字段查询(and_、or_)进行组合，多表的组合查询等等。感觉这里面like有多种形式，filter和filter_by在等值上符号不同，in转换和理解上有些困难，总之就是在对象编程和sql编程之间的困扰。
代码语言：javascript复制def testquery():
# --单表查询
print('----------------单表 全表查询------------------')
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
print('BookInfo.query.all()')
queryinfo = BookInfo.query.all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher,i.publicationdate, i.booktype, i.stockdate)
print('db.session.query(BookInfo).all()')
queryinfo = db.session.query(BookInfo).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher,i.publicationdate, i.booktype, i.stockdate)
print('db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher...).all()')
queryinfo = db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher,BookInfo.publicationdate, BookInfo.booktype, BookInfo.stockdate).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher,i.publicationdate, i.booktype, i.stockdate)
print('db.session.execute(sql)')
sql='select * from bookinfo'
queryinfo = db.session.execute(sql)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher,i.publicationdate, i.booktype, i.stockdate)print('----------------单表 单字段查询 精确查询------------------')
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.isbnno ='ISBN0001'
isbnno = 'ISBN0001'
# --单表查询，单字段查询1
from sqlalchemy import select
print('select(BookInfo).where(BookInfo.isbnno == isbnno)')
queryinfo = select(BookInfo).where(BookInfo.isbnno == isbnno)
print(queryinfo)
# --单表查询，单字段查询1
print('BookInfo.query.filter_by(isbnno=isbnno).all()')
queryinfo = BookInfo.query.filter_by(isbnno=isbnno).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.query(BookInfo).filter(BookInfo.isbnno==isbnno).all()')
queryinfo = db.session.query(BookInfo).filter(BookInfo.isbnno==isbnno)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher...).filter(BookInfo.isbnno==isbnno).all()')
queryinfo = db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher, BookInfo.publicationdate,
BookInfo.booktype, BookInfo.stockdate).filter(BookInfo.isbnno==isbnno).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.execute(sql, params)')
sql = 'select * from bookinfo where isbnno=:isbnno'
params = {'isbnno': 'ISBN0001'}
queryinfo = db.session.execute(sql, params)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('----------------单表 单字段查询 like模糊查询------------------')
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.publisher like '%电子工业%'
publisher = '%电子工业%'
print('BookInfo.query.filter(BookInfo.publisher.ilike(publisher))')
queryinfo = BookInfo.query.filter(BookInfo.publisher.ilike(publisher))
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
publisher = '电子工业'
print('BookInfo.query.filter(BookInfo.publisher.contains(publisher))')
queryinfo = BookInfo.query.filter(BookInfo.publisher.contains(publisher))
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
publisher = '电子工业'
print("db.session.query(BookInfo).filter(BookInfo.publisher.like('%{keyword}%'.format(keyword=publisher))).all()")
queryinfo = db.session.query(BookInfo).filter(
BookInfo.publisher.like('%{keyword}%'.format(keyword=publisher))).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
publisher = '%电子工业%'
print("db.session.query(BookInfo).filter(BookInfo.publisher.like('{keyword}'.format(keyword=publisher))).all()")
queryinfo = db.session.query(BookInfo).filter(
BookInfo.publisher.like('{keyword}'.format(keyword=publisher))).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.execute(sql, params)')
sql = 'select * from bookinfo where publisher like :publisher'
params = {'publisher': '%电子工业%'}
queryinfo = db.session.execute(sql, params)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('----------------单表 单字段查询 ><查询------------------')
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.publicationdate >'2012'
publicationdate = '2012'
print("db.session.query(BookInfo).filter(BookInfo.publicationdate>publicationdate).all()")
queryinfo = db.session.query(BookInfo).filter(BookInfo.publicationdate>publicationdate).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.publicationdate <'2009'
publicationdate = '2009'
print("db.session.query(BookInfo).filter(BookInfo.publicationdate<publicationdate).all()")
queryinfo = db.session.query(BookInfo).filter(BookInfo.publicationdate < publicationdate).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('----------------单表 单字段查询 in查询------------------')
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.isbnno in ('ISBN0001','ISBN0003','ISBN0005','ISBN0007')
print('db.session.execute(sql1)')
isbnnolist = ['ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007']
# sql = "select * from bookinfo where isbnno in ({})".format(','.join(map(str,isbnnolist)))
# select * from bookinfo where isbnno in (ISBN0001,ISBN0003,ISBN0005,ISBN0007)
sql = 'select * from bookinfo where isbnno in ({isbnnolist})'.format(isbnnolist=', '.join("'" item "'" for item in isbnnolist))
# select * from bookinfo where isbnno in ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
queryinfo = db.session.execute(sql)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.execute(sql2)')
isbnnolist = ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
sql = 'select * from bookinfo where isbnno in {}'.format(isbnnolist)
# select * from bookinfo where isbnno in ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
queryinfo = db.session.execute(sql)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
# print('db.session.execute(sql3)')
# isbnnolist = ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
# sql = 'select * from bookinfo where isbnno in (:isbnnolist)'
# params={'isbnnolist':['ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007']}
#
# # select * from bookinfo where isbnno in ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
# queryinfo = db.session.execute(sql,params)
# for i in queryinfo:
# print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('BookInfo.query.filter(BookInfo.isbnno in isbnnolist)') ################
isbnnolist = ('ISBN0001', 'ISBN0003', 'ISBN0005', 'ISBN0007')
queryinfo = BookInfo.query.filter(BookInfo.isbnno in (isbnnolist,))
# queryinfo = BookInfo.query.filter(BookInfo.isbnno in (isbnnolist))
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
isbnnolist=['ISBN0001','ISBN0003','ISBN0005','ISBN0007']
print("BookInfo.query.filter(BookInfo.isbnno.in_(isbnnolist)).all()")
queryinfo = BookInfo.query.filter(BookInfo.isbnno.in_(isbnnolist)).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print("db.session.query(BookInfo).filter(BookInfo.isbnno.in_(isbnnolist)).all()")
queryinfo = db.session.query(BookInfo).filter(BookInfo.isbnno.in_(isbnnolist)).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)print('----------------单表 多字段查询------------------')
# --单表查询，多字段查询1
# select book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate from bookinfo book
# where book.isbnno =? and book.bookname like ? and book.publisher like ? and book.bookstockdate>?
# order by book.isbnno
publisher1 = '机械工业出版社'
publisher2 = '电力出版社'
stockdate1='2021-07-01'
stockdate2 = '2021-07-01'
print('BookInfo.query.filter(*book_filter).all()')
book_filter = {
or_(
and_(
BookInfo.publisher == publisher1,
BookInfo.stockdate > stockdate1
),
and_(
BookInfo.publisher.like('%' publisher2 '%'),
BookInfo.stockdate > stockdate2
)
)
}
queryinfo=BookInfo.query.filter(*book_filter).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher...).filter(*book_filter).all()')
queryinfo = db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher, BookInfo.publicationdate,
BookInfo.booktype, BookInfo.stockdate).filter(*book_filter).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
print('db.session.execute(sql, params)')
publisher1 = '机械工业出版社'
publisher2 = '%电力出版社%'
stockdate1 = '2021-07-01'
stockdate2 = '2021-07-01'
sql = 'select * from bookinfo where (publisher = :publisher1 and stockdate>:stockdate1) or (publisher like :publisher2 and stockdate>:stockdate2)'
params = {'publisher1': publisher1,
'publisher2': publisher2,
'stockdate1': stockdate1,
'stockdate2': stockdate2}
queryinfo = db.session.execute(sql, params)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)
publisher = '邮电出版社'
stockdate = '2021-07-11'
# stockdate = ''
bookname = ''
print('db.session.query(BookInfo.isbnno, BookInfo.bookname,..).filter(')
from sqlalchemy import text
queryinfo=db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher, BookInfo.publicationdate,
BookInfo.booktype, BookInfo.stockdate).filter(
BookInfo.publisher.contains(publisher) if publisher is not None else text("") , # like %邮电出版社%
BookInfo.bookname.contains(bookname) if bookname is not None else text(""), # like %
BookInfo.stockdate>=stockdate if stockdate!='1900-01-01' else text("") #>'2021-07-11'
).all()
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate)print('----------------多表 多字段查询------------------')
# --多表查询，多字段查询1
# 实现目标
# select distinct book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate,
# bookstock.purchasenum, bookstock.stocknum,
# author.authorid, author.authorname, author.authorcard, author.authornationality
# from bookinfo book,bookstockinfo bookstock,bookauthorinfo bookauthor,authorinfo author
# where book.isbnno=bookstock.isbnno and book.isbnno=bookauthor.isbnno and bookauthor.authorid=author.authorid
# book.isbnno =? and book.bookname like ? and book.publisher like ? and book.bookstockdate>?
# and author.authorname like ? and author.authornationality like ?
# order by book.isbnno
book_filter = {
or_(
and_(
BookInfo.isbnno.in_(['ISBN0001','ISBN0003','ISBN0005','ISBN0007']),
BookInfo.publisher.ilike('%电子工业出版社%')
)
)
}
queryinfo=db.session.query(BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher, BookInfo.publicationdate, BookInfo.booktype, BookInfo.stockdate,
BookStockInfo.purchasenum, BookStockInfo.stocknum)
.join(BookStockInfo,BookInfo.isbnno==BookStockInfo.isbnno)
.join(BookAuthorinfo,BookInfo.isbnno==BookAuthorinfo.isbnno)
.join(AuthorInfo,BookAuthorinfo.authorid==AuthorInfo.authorid).filter(*book_filter).distinct()#.all()
# query.join(BookStockInfo, BookInfo.isbnno==BookStockInfo.isbnno) # explicit condition
# query.join(BookInfo.isbnno) # specify relationship from left to right
# query.join(BookStockInfo, BookInfo.isbnno) # same, with explicit target
# query.join('bookstockinfo') # same, using a string
# SELECT DISTINCT bookinfo.isbnno AS bookinfo_isbnno, bookinfo.bookname AS bookinfo_bookname, bookinfo.publisher AS bookinfo_publisher,
# bookinfo.publicationdate AS bookinfo_publicationdate, bookinfo.booktype AS bookinfo_booktype, bookinfo.stockdate AS bookinfo_stockdate,
# bookstockinfo.purchasenum AS bookstockinfo_purchasenum, bookstockinfo.stocknum AS bookstockinfo_stocknum
# FROM bookinfo JOIN bookstockinfo ON bookinfo.isbnno = bookstockinfo.isbnno
# JOIN bookauthorinfo ON bookinfo.isbnno = bookauthorinfo.isbnno
# JOIN authorinfo ON bookauthorinfo.authorid = authorinfo.authorid
# WHERE bookinfo.isbnno IN ([POSTCOMPILE_isbnno_1])
# AND lower(bookinfo.publisher) LIKE lower(?)
for i in queryinfo:
print(i.isbnno, i.bookname, i.publisher, i.publicationdate, i.booktype, i.stockdate,i.purchasenum,i.stocknum)
# --多表查询，多字段查询2
# 实现目标
# select borrowerinfo.borrowername,borrowerinfo.address, borrowerinfo.telephone, borrowerinfo.registerdate,
# book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate,
# borrowerbook.borrowbookid,borrowerbook.borrowerid,borrowerbook.isbnno,borrowerbook.borrowdate,borrowerbook.returndate,borrowerbook.returnflag
# from borrowerbook borrowerbook
# left outer join bookinfo book
# on borrowerbook.isbnno=book.isbnno
# left outer join borrowerinfo borrowerinfo
# on borrowerbook.borrowerid=borrowerinfo.borrowerid
# and book.isbnno =?
# and book.bookname like ?
# and book.bookstockdate>?
# and borrowerinfo.borrowername = ?
isbnno = '机械工业出版社'
bookname = '电力出版社'
bookstockdate = '2021-07-01'
borrowername = '2021-07-01'
print('BookInfo.query.filter(*book_filter).all()')
book_filter = {
or_(
and_(
BookInfo.publisher.ilike('%'),
BookInfo.stockdate > stockdate1
),
and_(
BookInfo.publisher.like('%' publisher2 '%'),
BookInfo.stockdate > stockdate2
)
)
}
queryinfo = db.session.query(BorrowerInfo.borrowername,BorrowerInfo.birthday,BorrowerInfo.address,
BookInfo.isbnno, BookInfo.bookname, BookInfo.publisher, BookInfo.publicationdate,
BorrowerBook.borrowdate, BorrowerBook.returndate, BorrowerBook.returnflag)
.outerjoin(BorrowerInfo, BorrowerBook.borrowerid == BorrowerInfo.borrowerid)
.outerjoin(BookInfo, BorrowerBook.isbnno == BookInfo.isbnno)
.filter(*book_filter) # .all()
# SELECT borrowerinfo.borrowername AS borrowerinfo_borrowername, borrowerinfo.birthday AS borrowerinfo_birthday, borrowerinfo.address AS borrowerinfo_address,
# bookinfo.isbnno AS bookinfo_isbnno, bookinfo.bookname AS bookinfo_bookname, bookinfo.publisher AS bookinfo_publisher, bookinfo.publicationdate AS bookinfo_publicationdate,
# borrowerbook.borrowdate AS borrowerbook_borrowdate, borrowerbook.returndate AS borrowerbook_returndate, borrowerbook.returnflag AS borrowerbook_returnflag
# FROM borrowerbook
# LEFT OUTER JOIN borrowerinfo
# ON borrowerbook.borrowerid = borrowerinfo.borrowerid
# LEFT OUTER JOIN bookinfo
# ON borrowerbook.isbnno = bookinfo.isbnno
# WHERE bookinfo.publisher = ? AND bookinfo.stockdate > ? OR bookinfo.publisher LIKE ? AND bookinfo.stockdate > ?
for i in queryinfo:
print(i.borrowername,i.birthday,i.address,
i.isbnno, i.bookname, i.publisher, i.publicationdate,
i.borrowdate, i.returndate, i.returnflag)
