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)
