关于flask入门教程-图书借阅系统-flask_SQLAlchemy高级用法

flask中一般使用flask-SQLAlchemy来操作数据库，使用起来比较简单，易于操作。而flask-SQLAlchemy是SQLAlchemy的flask实现。

在Flask-SQLAlchemy中，查询操作是通过query对象操作数据。最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。

本部分之所以迟迟未交差，主要是flask-SQLAlchemy关于query的用法实在太少了，而且没有几个是比较全面的，动手加验证花了不少的时间，而且很多等效的语法，想比较出来个优劣势，选择最适合自己的。

本文主要验证了单表的全表查询、单表的单字段查询（like、==、>、<、in等等），单表的多字段查询(and_、or_)进行组合，多表的组合查询等等。感觉这里面like有多种形式，filter和filter_by在等值上符号不同，in转换和理解上有些困难，总之就是在对象编程和sql编程之间的困扰。

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)