关于flask入门教程-图书借阅系统-数据库设计和ORM映射

python与大数据分析

发布于 2022-03-11 16:59:02

5380

发布于 2022-03-11 16:59:02

关于数据库设计比较偷懒，就采用了百度百科的说法了。

数据库设计是指根据用户的需求，在某一具体的数据库管理系统上，设计数据库的结构和建立数据库的过程。

数据库设计是建立数据库及其应用系统的技术，是信息系统开发和建设中的核心技术。由于数据库应用系统的复杂性，为了支持相关程序运行，数据库设计就变得异常复杂，因此最佳设计不可能一蹴而就，而只能是一种“反复探寻，逐步求精”的过程，也就是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。

总而言之，就是数据库设计是系统开发的核心，数据库的设计关系到整个系统的性能，一般建议采用第三范式，当然随着微服务框架的发展和各种非结构化数据库涌现，数据库的设计理念可能有所变化，可以适当的采用一些冗余措施。一般搞IT开发的对图书借阅比较清楚，本文采用的也并非最佳实践，仅作为一个小小的例子而已。

对象关系映射即ORM，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。

我算是半个搞数据库出身的，所以更喜欢直接写SQL，可控加高效，对ORM一直持有半怀疑态度；当然ORM有ORM的好处，可以比较容易的适配各种数据库，在数据库之间复用和迁移。

flask-sqlalchemy是flask中比较常用的一种ORM框架，在此为了学习就认真走了一遍。

第一个是关于图书借阅系统的E-R关系图，用visio画的，可以从整体上了解系统的数据库设计。

第二个图是图书借阅系统的物理结构，可以据此进行数据库表的构建。

第一段代码是从网上抄袭的，主要是基于sqlite3的基本DDL、DML语法。

import sqlite3
import os

# 删除数据库
os.unlink("test.db")

# 默认创建数据库
# -----------------创建数据表-----------------
conn = sqlite3.connect('test.db')
print("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print("Table created successfully")
conn.commit()
conn.close()

# -----------------insert插入语句-----------------
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  VALUES (1,'Paul', 32,'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2,'Allen', 25,'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3,'Teddy', 23,'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4,'Mark', 25,'Rich-Mond', 65000.00 )")
conn.commit()
print("Records created successfully")
conn.close()

# -----------------select查询语句-----------------
conn = sqlite3.connect('test.db')
c = conn.cursor()
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
    print("ID = ", row[0])
    print("NAME = ", row[1])
    print("ADDRESS = ", row[2])
    print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()

# -----------------update更新语句-----------------
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print("Total number of rows updated :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
    print("ID = ", row[0])
    print("NAME = ", row[1])
    print("ADDRESS = ", row[2])
    print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()

# -----------------delete删除语句-----------------
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
    print("ID = ", row[0])
    print("NAME = ", row[1])
    print("ADDRESS = ", row[2])
    print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()

第二段代码是基于sqlite3和传统SQL的图书借阅系统的实现，除了数据被我抠走了一些，全部都是可以运行的。

import sqlite3
import os

# 删除数据库
os.unlink("test.db")

# 默认创建数据库
# -----------------创建数据表-----------------
conn = sqlite3.connect('test.db')
print("Opened database successfully")
c = conn.cursor()

authorinfotable = '''
create table authorinfo(
        authorid   INTEGER    PRIMARY KEY    ,
        authorname varchar(20)       NOT NULL,
        authorcard varchar(20)       ,
        authornationality  varchar(20)
);'''
bookinfotable = '''
create table bookinfo(
        isbnno varchar(20)    PRIMARY KEY    NOT NULL,
        bookname   varchar(50)       NOT NULL,
        publisher  varchar(50)       ,
        publicationdate    varchar(20)       ,
        booktype   varchar(20)       ,
        stockdate  date      NOT NULL,
        FOREIGN KEY(isbnno) REFERENCES bookinfo(isbnno)
);'''
bookstockinfotable = '''
create table bookstockinfo(
        isbnno varchar(20)    PRIMARY KEY    NOT NULL,
        purchasenum    int       NOT NULL,
        stocknum   int       NOT NULL
);'''
bookauthorinfotable = '''
create table bookauthorinfo(
        bookauthorid   INTEGER    PRIMARY KEY    NOT NULL,
        isbnno varchar(20)       NOT NULL,
        authorid   int       NOT NULL,
        FOREIGN KEY(isbnno) REFERENCES bookinfo(isbnno),
        FOREIGN KEY(authorid) REFERENCES authorinfo(authorid)
);'''
borrowerinfotable = '''
create table borrowerinfo  (
        borrowid   INTEGER    PRIMARY KEY    NOT NULL,
        borrowername   varchar(20)       NOT NULL,
        sex    varchar(2)    ,
        birthday   date      ,
        postcard   varchar(20)       ,
        address    varchar(50)       ,
        telephone  varchar(20)       ,
        registerdate   date      NOT NULL
);'''
borrowerpasswordinfotable = '''
create table borrowerpasswordinfo(
        borrowid   INTEGER    PRIMARY KEY    NOT NULL,
        password   varchar(20)       NOT NULL
);'''
borrowerlogintable = '''
create table borrowerlogin(
        borrowerloginid    INTEGER    PRIMARY KEY    NOT NULL,
        borrowid   int       NOT NULL,
        logindatetime  datetime      NOT NULL,
        logoutdatetime datetime      ,
        FOREIGN KEY(borrowid) REFERENCES borrowerinfo(borrowid)
);'''
borrowbooktable = '''
create table borrowbook(
        borrowbookid   INTEGER    PRIMARY KEY    NOT NULL,
        borrowid   int       NOT NULL,
        isbnno varchar(20)       NOT NULL,
        borrowdate date      NOT NULL,
        returndate date      ,
        returnflag char(1)       ,
        FOREIGN KEY(borrowid) REFERENCES borrowerinfo(borrowid),
        FOREIGN KEY(isbnno) REFERENCES bookinfo(isbnno)
);'''
c.execute(bookinfotable)
print("bookinfotable created successfully")
c.execute(bookstockinfotable)
print("bookstockinfotable created successfully")
c.execute(bookauthorinfotable)
print("bookauthorinfotable created successfully")
c.execute(authorinfotable)
print("authorinfotable created successfully")
c.execute(borrowerinfotable)
print("borrowerinfotable created successfully")
c.execute(borrowerpasswordinfotable)
print("borrowerpasswordinfotable created successfully")
c.execute(borrowerlogintable)
print("borrowerlogintable created successfully")
c.execute(borrowbooktable)
print("borrowbooktable created successfully")
conn.commit()
conn.close()

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("INSERT INTO authorinfo (authorid,authorname,authorcard,authornationality)  VALUES (1,'巴里','' ,'美国')")
c.execute("INSERT INTO authorinfo (authorid,authorname,authorcard,authornationality)  VALUES (2,'白鳝','' ,'中国')").....conn.commit()
print("authorinfo Records created successfully")

c.execute("INSERT INTO bookinfo  VALUES ('ISBN0001','可爱的Python','2009','电子工业出版社','图书','2021-07-01')")
c.execute("INSERT INTO bookinfo  VALUES ('ISBN0002','Python绝技','2018','电子工业出版社','图书','2021-07-01')").....conn.commit()

c.execute("INSERT INTO bookstockinfo  VALUES ('ISBN0001',2,2)")
c.execute("INSERT INTO bookstockinfo  VALUES ('ISBN0002',2,2)")

.....

conn.commit()

c.execute("INSERT INTO bookauthorinfo(isbnno,authorid)  VALUES ('ISBN0001',23)")

.....

conn.commit()

c.execute(
    "INSERT INTO borrowerinfo  VALUES (1,'张三','男','2000-01-01','400111200001010043','海口市','13800100001','2021-09-01')")
c.execute(
    "INSERT INTO borrowerinfo  VALUES (2,'李四','男','1982-10-01','400111198210010041','海口市','13800300002','2021-09-01')")

.....

conn.commit()

c.execute("INSERT INTO borrowerpasswordinfo  VALUES (1,'1qaz!QAZ')")

.....

conn.commit()

c.execute("INSERT INTO borrowerlogin  VALUES (1,1,'2021-9-1 10:01','2021-9-1 12:01')")

.....

conn.commit()

c.execute("INSERT INTO borrowbook  VALUES (1,1,'ISBN0001','2021-09-01','2021-09-02','1')")
.....
conn.commit()

conn.close()

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
cursor = c.execute("SELECT authorid,authorname,authorcard,authornationality  from authorinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from bookinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from bookstockinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from bookauthorinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from borrowerinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from borrowerpasswordinfo")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from borrowerlogin")
for row in cursor:
    print(row)
cursor = c.execute("SELECT *  from borrowbook")
for row in cursor:
    print(row)
print("Operation done successfully")
conn.close()

第三段代码是基于flask-sqlalchemy的图书借阅系统的实现，这里有两个坑，网上说的未必都对。

第一个是# Error：Flask-Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set.错误

首先app.config中要用绝对路径

其次是/问题，从window拷出来是\，要改

最后是顺序问题，先实例化Flask对象，再进行数据库配置，再实例化db

第二个问题是关于自增键值的初始化问题，自增键值其实挺讨厌的，主键还好可以随时变，但外键信息要保持不变还是挺难的，在这里有个小技巧，就是在__init__的时候，对于主键值可以设置一个缺省值，如果是缺省值就让他自增好了，如果不是则直接用初始化数据写死。

第三个问题，到底是用业务主键还是逻辑主键（rowid,自增值）的问题，这个其实很难有答案，各有各的利弊，在这里两种混合使用了，比如图书ISBN号是唯一的，就用了ISBN号做业务主键，代价是如果ISBN号录入错了，就需要调整两次才能确保数据是OK的；像借阅者和作者用的是逻辑主键（自增值），这样的代价是作者会重复，为什么不用身份证呢？实际上也是可以的，但是身份证太过于敏感了，这样的代价是要在身份证号码上加一个唯一索引，而且作者也未必有身份证，所以最终选了逻辑主键，当然像图书借阅信息，用户登陆日志，用逻辑主键就OK了。

第四个问题，是多对多连接用dt.table好，还是model好，也是无解的，为啥，网上可查的资源太少了，一般倾向于用dt.table，但对于dt.table的了解太少了，除非阅读源代码。

第五个问题，全部用ORM可行吗？估计比较悬，ORM会存在性能问题，构造复杂的SQL也够呛，所幸这里用到的暂时都比较简单，走一步算一步吧，估计会结合SQL一起用。

第六个问题，其实不是问题，而是一个小技巧，[dict(zip(bookinfoname, list)) for list in bookinfolist]，把字段名和原始数据构造成了一个字典列表用于初始化数据，不至于看起来太傻。

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

import os
from datetime import datetime

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'test.db')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///C:/Python/Pycharm/knn_web/templates/test.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Error：Flask-Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set.
db = SQLAlchemy(app)


class AuthorInfo(db.Model):
    __tablename__ = "authorinfo"
    authorid = db.Column(db.Integer, primary_key=True)
    authorname = db.Column(db.String(20), nullable=False)
    authorcard = db.Column(db.String(20))
    authornationality = db.Column(db.String(20))
    # relate_bookauthor = db.relationship('BookAuthorInfo', backref=db.backref('authorinfo', lazy='dynamic'))
    def __init__(self, authorname, authorcard, authornationality, authorid=0):
        self.authorname = authorname
        self.authorcard = authorcard
        self.authornationality = authornationality
        if authorid != 0:
            self.authorid = authorid

    def __repr__(self):
        return '<author %r>' % self.authorname


class BookInfo(db.Model):
    __tablename__ = "bookinfo"
    isbnno = db.Column(db.String(20), primary_key=True)
    bookname = db.Column(db.String(50), nullable=False)
    publisher = db.Column(db.String(50))
    publicationdate = db.Column(db.String(20))
    booktype = db.Column(db.String(20))
    stockdate = db.Column(db.Date)

    # relate_bookauthor = db.relationship('BookAuthorInfo', backref=db.backref('bookinfo', lazy='dynamic'))
    def __init__(self, isbnno, bookname, publisher, publicationdate, booktype, stockdate):
        self.isbnno = isbnno
        self.bookname = bookname
        self.publisher = publisher
        self.publicationdate = publicationdate
        self.booktype = booktype
        self.stockdate = stockdate

    def __repr__(self):
        return '<book %r>' % self.isbnno


class BookStockInfo(db.Model):
    __tablename__ = "bookstockinfo"
    isbnno = db.Column(db.String(20), db.ForeignKey('bookinfo.isbnno'), primary_key=True)
    purchasenum = db.Column(db.Integer, nullable=False)
    stocknum = db.Column(db.Integer, nullable=False)
    # 后面追加
    # 其中realtionship描述了BookStockInfo和BookInfo的关系。
    # 第一个参数为对应参照的类"BookInfo"
    # 第二个参数backref为类BookStockInfo申明新属性的方法
    # 第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
    relate_book = db.relationship('BookInfo', backref=db.backref('bookstockinfo', lazy='dynamic'))

    def __init__(self, isbnno, purchasenum, stocknum):
        self.isbnno = isbnno
        self.purchasenum = purchasenum
        self.stocknum = stocknum

    def __repr__(self):
        return '<book %r>' % self.isbnno


BookAuthor = db.Table('bookauthor',
                      db.Column('bookauthorid', db.Integer, primary_key=True),
                      db.Column('isbnno', db.String(20), db.ForeignKey('bookinfo.isbnno'), nullable=False),
                      db.Column('authorid', db.Integer, db.ForeignKey('authorinfo.authorid'), nullable=False))


class BookAuthorinfo(db.Model):
    __tablename__ = "bookauthorinfo1"
    bookauthorid = db.Column(db.Integer, primary_key=True)
    isbnno = db.Column(db.String(20), db.ForeignKey('bookinfo.isbnno'), nullable=False)
    authorid = db.Column(db.Integer, db.ForeignKey('authorinfo.authorid'), nullable=False)
    relate_book = db.relationship('BookInfo', backref=db.backref('bookauthorinfo1', lazy='dynamic'))
    relate_author = db.relationship('AuthorInfo', backref=db.backref('bookauthorinfo1', lazy='dynamic'))

    def __init__(self, isbnno, authorid, bookauthorid=0):
        self.isbnno = isbnno
        self.authorid = authorid
        if bookauthorid != 0:
            self.bookauthorid = bookauthorid

    def __repr__(self):
        return '<book %r>' % self.isbnno


class BorrowerInfo(db.Model):
    __tablename__ = "borrowerinfo"
    borrowerid = db.Column(db.Integer, primary_key=True)
    borrowername = db.Column(db.String(20), nullable=False)
    sex = db.Column(db.String(2))
    birthday = db.Column(db.Date)
    postcard = db.Column(db.String(20))
    address = db.Column(db.String(50))
    telephone = db.Column(db.String(20))
    registerdate = db.Column(db.Date)

    def __init__(self, borrowername, sex, birthday, postcard, address, telephone, registerdate, borrowerid=0):
        self.borrowername = borrowername
        self.sex = sex
        self.birthday = birthday
        self.postcard = postcard
        self.address = address
        self.telephone = telephone
        self.registerdate = registerdate
        if borrowerid != 0:
            self.borrowerid = borrowerid

    def __repr__(self):
        return '<borrower name %r>' % self.borrowername


class BorrowerPasswordInfo(db.Model):
    __tablename__ = "borrowerpasswordinfo"
    borrowerid = db.Column(db.Integer, db.ForeignKey('borrowerinfo.borrowerid'), primary_key=True)
    password = db.Column(db.String(20), nullable=False)
    relate_borrower = db.relationship('BorrowerInfo', backref=db.backref('borrowerpasswordinfo', lazy='dynamic'))

    def __init__(self, borrowerid, password):
        self.borrowerid = borrowerid
        self.password = password

    def __repr__(self):
        return '<borrower name %r>' % self.borrowername


class BorrowerLoginInfo(db.Model):
    __tablename__ = "borrowerlogininfo"
    borrowerloginid = db.Column(db.Integer, primary_key=True)
    borrowerid = db.Column(db.Integer, db.ForeignKey('borrowerinfo.borrowerid'))
    logindatetime = db.Column(db.DateTime, nullable=False)
    logoutdatetime = db.Column(db.DateTime)
    relate_borrower = db.relationship('BorrowerInfo', backref=db.backref('borrowerlogininfo', lazy='dynamic'))

    def __init__(self, borrowerid, logindatetime, logoutdatetime, borrowerloginid=0):
        self.borrowerid = borrowerid
        self.logindatetime = logindatetime
        self.logoutdatetime = logoutdatetime
        if borrowerloginid != 0:
            self.borrowerloginid = borrowerloginid

    def __repr__(self):
        return '<borrower name %r>' % self.borrowername


class BorrowerBook(db.Model):
    __tablename__ = "borrowerbook"
    borrowbookid = db.Column(db.Integer, primary_key=True)
    borrowerid = db.Column(db.Integer, db.ForeignKey('borrowerinfo.borrowerid'), nullable=False)
    isbnno = db.Column(db.String(20), db.ForeignKey('bookinfo.isbnno'), nullable=False)
    borrowdate = db.Column(db.Date)
    returndate = db.Column(db.Date)
    returnflag = db.Column(db.String(2))
    relate_borrower = db.relationship('BorrowerInfo', backref=db.backref('borrowerbook', lazy='dynamic'))
    relate_book = db.relationship('BookInfo', backref=db.backref('borrowerbook', lazy='dynamic'))

    def __init__(self, borrowerid, isbnno, borrowdate, returndate, returnflag='0', borrowbookid=0):
        self.borrowerid = borrowerid
        self.isbnno = isbnno
        self.borrowdate = borrowdate
        self.returnflag = returnflag
        if returndate != '':
            self.returndate = returndate
        if borrowbookid != 0:
            self.borrowbookid = borrowbookid

    def __repr__(self):
        return '<borrower name %r isbnno %r >' % self.borrowername, self.borrowername


if __name__ == '__main__':

    db.drop_all()
    db.create_all()
    author = AuthorInfo('巴里', '', '美国')
    db.session.add(author)
    author = AuthorInfo('白鳝', '', '中国')
    db.session.add(author)

.....

    db.session.commit()

    for author in AuthorInfo.query.all():
        print(author.authorid, author.authorname, author.authorcard, author.authornationality)

    bookinfolist = [['ISBN0001', '可爱的Python', '2009', '电子工业出版社', '图书', '2021-07-01'],
                    ['ISBN0002', 'Python绝技', '2018', '电子工业出版社', '图书', '2021-07-01'],
                    .....

                    ['ISBN0022', 'IT项目管理那些事儿', '2009', '电子工业出版社', '图书', '2021-07-01']]
    bookinfoname = ['isbnno', 'bookname', 'ublisher', 'publicationdate', 'booktype', 'stockdate']
    # bookinfodictlist=[]
    # for bookinfo in bookinfolist:
    #     bookinfodictlist.append(dict(zip(bookinfoname,bookinfo)))
    bookinfodictlist = [dict(zip(bookinfoname, list)) for list in bookinfolist]

    bookobjlist = []
    for i in bookinfodictlist:
        isbnno, bookname, publisher, publicationdate, booktype, stockdate = i.values()
        bookobj = BookInfo(isbnno=isbnno, bookname=bookname, publisher=publisher,
                           publicationdate=publicationdate, booktype=booktype,
                           stockdate=datetime.strptime(stockdate, '%Y-%m-%d'))
        bookobjlist.append(bookobj)

    db.session.add_all(bookobjlist)
    db.session.commit()

    for book in BookInfo.query.all():
        print(book.isbnno, book.bookname, book.publisher, book.publicationdate, book.booktype, book.stockdate)

    print('------------------Book Stock Process-------------------')
    bookstockinfolist = [['ISBN0001', 2, 2],
                         ['ISBN0002', 2, 2],
                         .....

                         ['ISBN0022', 2, 2]]
    bookstockinfoname = ['isbnno', 'purchasenum', 'stocknum']

    bookstockinfodictlist = [dict(zip(bookstockinfoname, list)) for list in bookstockinfolist]

    bookstockobjlist = []
    for i in bookstockinfodictlist:
        isbnno, purchasenum, stocknum = i.values()
        bookstockobj = BookStockInfo(isbnno=isbnno, purchasenum=purchasenum, stocknum=stocknum)
        bookstockobjlist.append(bookstockobj)

    db.session.add_all(bookstockobjlist)
    db.session.commit()

    for bookstock in BookStockInfo.query.all():
        print(bookstock.isbnno, bookstock.purchasenum, bookstock.stocknum)

    # db.relationship应用
    # 查询isbnno == 'ISBN0008'的库存，返回BookStockInfo对象值
    # 可relate_book当做为BookStockInfo的属性，直接返回BookInfo对象
    bookstockquery = BookStockInfo.query.filter(BookStockInfo.isbnno == 'ISBN0008').first()
    bookstockquery.purchasenum  # 2
    bookstockquery.relate_book.bookname  # PYTHON技术手册
    bookauthorlist = [['ISBN0001', 23],
                      ['ISBN0002', 24],
                      .....

                      ['ISBN0019', 15]]

    bookauthorinfoname = ['isbnno', 'authorid']

    bookauthorinfodictlist = [dict(zip(bookauthorinfoname, list)) for list in bookauthorlist]

    bookauthorobjlist = []
    for i in bookauthorinfodictlist:
        isbnno, authorid = i.values()
        bookauthorobj = BookAuthorinfo(isbnno=isbnno, authorid=authorid)
        bookauthorobjlist.append(bookauthorobj)

    db.session.add_all(bookauthorobjlist)
    db.session.commit()

    for bookauthor in BookAuthorinfo.query.all():
        print(bookauthor.bookauthorid, bookauthor.isbnno, bookauthor.authorid)

    # borrowid,borrowername,sex,birthday,postcard,address,telephone,registerdate
    borrowerinfolist = [[1, '张三', '男', '2000-01-01', '400111200001010043', '海口市', '13800100001', '2021-09-01'],
                        .....
                        [4, '赵六', '女', '1989-10-11', '400111198910110041', '海口市', '13800600002', '2021-09-03']]
    borrowerinfoname = ['borrowid', 'borrowername', 'sex', 'birthday', 'postcard', 'address', 'telephone',
                        'registerdate']
    borrowerinfodictlist = [dict(zip(borrowerinfoname, list)) for list in borrowerinfolist]

    borrowerobjlist = []
    for i in borrowerinfodictlist:
        borrowerid, borrowername, sex, birthday, postcard, address, telephone, registerdate = i.values()
        borrowerrbj = BorrowerInfo(borrowername=borrowername, sex=sex, birthday=datetime.strptime(birthday, '%Y-%m-%d'),
                                   postcard=postcard,
                                   address=address, telephone=telephone,
                                   registerdate=datetime.strptime(registerdate, '%Y-%m-%d'), borrowerid=borrowerid)
        borrowerobjlist.append(borrowerrbj)

    db.session.add_all(borrowerobjlist)
    db.session.commit()

    for borrowerinfo in BorrowerInfo.query.all():
        print(borrowerinfo.borrowername, borrowerinfo.sex, borrowerinfo.birthday, borrowerinfo.postcard,
              borrowerinfo.address, borrowerinfo.telephone, borrowerinfo.registerdate, borrowerinfo.borrowerid)

    borrowerpasswordlist = [[1, '1qaz!QAZ'],
                             .....
                            [4, '1qaz!QAZ']]
    borrowerpasswordname = ['borrowerid', 'password']
    borrowerpassworddictlist = [dict(zip(borrowerpasswordname, list)) for list in borrowerpasswordlist]

    borrowerpasswordobjlist = []
    for i in borrowerpassworddictlist:
        borrowerid, password = i.values()
        borrowerpasswordobj = BorrowerPasswordInfo(borrowerid=borrowerid, password=password)
        borrowerpasswordobjlist.append(borrowerpasswordobj)

    db.session.add_all(borrowerpasswordobjlist)
    db.session.commit()

    for borrowerpasswordinfo in BorrowerPasswordInfo.query.all():
        print(borrowerpasswordinfo.borrowerid, borrowerpasswordinfo.password)

    borrowerloginlist = [[1, '2021-09-01 10:01:00', '2021-09-01 12:01:00', 1],
                         ....
                         [1, '2021-09-03 10:01:00', '2021-09-03 12:01:00', 5]]
    borrowerloginname = ['borrowerid', 'logindatetime', 'logoutdatetime', 'borrowerloginid']
    borrowerlogindictlist = [dict(zip(borrowerloginname, list)) for list in borrowerloginlist]

    borrowerloginobjlist = []
    for i in borrowerlogindictlist:
        borrowerid, logindatetime, logoutdatetime, borrowerloginid = i.values()
        borrowerloginobj = BorrowerLoginInfo(borrowerid=borrowerid,
                                             logindatetime=datetime.strptime(logindatetime, '%Y-%m-%d %H:%M:%S'),
                                             logoutdatetime=datetime.strptime(logoutdatetime, '%Y-%m-%d %H:%M:%S'),
                                             borrowerloginid=borrowerloginid)
        borrowerloginobjlist.append(borrowerloginobj)

    db.session.add_all(borrowerloginobjlist)
    db.session.commit()

    for borrowerlogininfo in BorrowerLoginInfo.query.all():
        print(borrowerlogininfo.borrowerid, borrowerlogininfo.logindatetime, borrowerlogininfo.logoutdatetime,
              borrowerlogininfo.borrowerloginid)

    borrowerbooklist = [[1, 'ISBN0001', '2021-09-01', '2021-09-02', '1', 1],
                        ....
                        [1, 'ISBN0005', '2021-09-03', '', '0', 6]]
    borrowerbookname = ['borrowerid', 'isbnno', 'borrowdate', 'returndate', 'returnflag', 'borrowbookid']
    borrowerbookdictlist = [dict(zip(borrowerbookname, list)) for list in borrowerbooklist]

    borrowerbookobjlist = []
    for i in borrowerbookdictlist:
        borrowerid, isbnno, borrowdate, returndate, returnflag, borrowbookid = i.values()
        if returndate != '':
            returndate = datetime.strptime(returndate, '%Y-%m-%d')
        borrowerbookobj = BorrowerBook(borrowerid=borrowerid,
                                       isbnno=isbnno,
                                       borrowdate=datetime.strptime(borrowdate, '%Y-%m-%d'),
                                       returndate=returndate,
                                       returnflag=returnflag,
                                       borrowbookid=borrowbookid)
        borrowerbookobjlist.append(borrowerbookobj)

    db.session.add_all(borrowerbookobjlist)
    db.session.commit()

    for borrowerbookinfo in BorrowerBook.query.all():
        print(borrowerbookinfo.borrowerid, borrowerbookinfo.isbnno, borrowerbookinfo.borrowdate,
              borrowerbookinfo.returndate, borrowerbookinfo.returnflag, borrowerbookinfo.borrowbookid)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-09-18，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法