关于数据库设计比较偷懒，就采用了百度百科的说法了。
数据库设计是指根据用户的需求，在某一具体的数据库管理系统上，设计数据库的结构和建立数据库的过程。
数据库设计是建立数据库及其应用系统的技术，是信息系统开发和建设中的核心技术。由于数据库应用系统的复杂性，为了支持相关程序运行，数据库设计就变得异常复杂，因此最佳设计不可能一蹴而就，而只能是一种“反复探寻，逐步求精”的过程，也就是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。
总而言之，就是数据库设计是系统开发的核心，数据库的设计关系到整个系统的性能，一般建议采用第三范式，当然随着微服务框架的发展和各种非结构化数据库涌现，数据库的设计理念可能有所变化，可以适当的采用一些冗余措施。一般搞IT开发的对图书借阅比较清楚，本文采用的也并非最佳实践，仅作为一个小小的例子而已。
对象关系映射即ORM，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。
我算是半个搞数据库出身的，所以更喜欢直接写SQL，可控加高效，对ORM一直持有半怀疑态度；当然ORM有ORM的好处，可以比较容易的适配各种数据库，在数据库之间复用和迁移。
flask-sqlalchemy是flask中比较常用的一种ORM框架，在此为了学习就认真走了一遍。
第一个是关于图书借阅系统的E-R关系图，用visio画的，可以从整体上了解系统的数据库设计。
第二个图是图书借阅系统的物理结构，可以据此进行数据库表的构建。
第一段代码是从网上抄袭的，主要是基于sqlite3的基本DDL、DML语法。
代码语言：javascript复制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的图书借阅系统的实现，除了数据被我抠走了一些，全部都是可以运行的。
代码语言：javascript复制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]，把字段名和原始数据构造成了一个字典列表用于初始化数据，不至于看起来太傻。
代码语言：javascript复制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)
