day12 sqlalchemy ORM 学员管理系统
一、ORM简介
ORM英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。
优点：
- 隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
- ORM使我们构造固化数据结构变得简单易行。
缺点：
- 无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。
二、sqlalchemy
1、连接db和创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, create_engine
from sqlalchemy.orm import mapper
"""
url类型:
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
"""
engine = create_engine("mysql+pymysql://root:starcor@192.168.120.71/day12?charset=utf8", echo=True)
Base = declarative_base() # 申明基类
class User(Base): # 继承基类
__tablename__ = "user" # 对应数据库中的表名
id = Column(Integer, primary_key=True)
name = Column(String(64))
password = Column(String(64))
Base.metadata.create_all(engine) # 父类调用子类，创建表
user2 = Table("user2", MetaData(), # 另一种创建表的方式，不常用
Column("id", Integer, primary_key=True),
Column("name", String(64)),
Column("password", String(64))
)
class User2(object):
def __init__(self, name, password):
self.name = name
self.password = password
mapper(User2, user2) # 使用第二种方式创建的时候需要映射
2、ORM的query的操作
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Glen
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, MetaData, create_engine, desc
from sqlalchemy.orm import mapper, sessionmaker
engine = create_engine("mysql+pymysql://root:starcor@192.168.120.71/day12?charset=utf8", echo=True) # 创建数据库引擎
session_class = sessionmaker(bind=engine) # 创建数据库会话，返沪session class
session = session_class() # 生成session实例
Base = declarative_base() # 申明基类
class User(Base): # 继承基类
__tablename__ = "user" # 对应数据库中的表名
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64))
password = Column(String(64))
def __repr__(self):
return "%s %s" % (self.id, self.name)
# u1 = User(name="", password="3333")
# u2 = User(name="", password="gg")
# session.add_all([u1, u2]) # insert 用户信息
# session.commit() # 修改操作都需要提交
"""
`filter_by`和`filter`都是过滤条件，只是用法有区别`filter_by`里面不能用`!= `还有`> <` 等等，
所有`filter`用得更多,`filter_by`只能用`=`。
"""
# q1 = session.query(User).filter_by(name="Glen")
# q1 = session.query(User).get(1) # 通过主键查询，1是id
# q1 = session.query(User).filter(User.name == "Glen").limit(2) # limit 从第一条到第2条
# q1 = session.query(User).filter(User.name == "Glen").offset(2) # offset 从第三条开始返回结果
q1 = session.query(User).filter(User.name == "Glen").slice(2, 3) # 相当于MySQL中的limit 2，3
print("q1:", q1) # 返回的sql查询语句
q2 = session.query(User).filter(User.name == "Glen").all()
# q2 = session.query(User).filter(User.name == "Glen").first() # 返回第一条
print(q2) # 查询的是User，返回User类的列表
# order_by 排序默认升序，desc 降序
q3 = session.query(User.id, User.name).filter(User.name == "Glen").order_by(desc(User.id)).all()
print(q3) # 返回[(1, Glen),(2, Glen)]
q4 = session.query(User.id, User.name).filter(User.name.like("%gg%")) # 字符串匹配，需要加%
# q4 = session.query(User.id, User.name).filter(User.name.notilike("%gg"))
print(q4)
"""
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE user.name LIKE %(name_1)s
"""
q5 = session.query(User.id, User.name).filter(User.name.in_(["Glen", "lg"]))
# q5 = session.query(User.id, User.name).filter(User.name.notin_(["Glen", "lg"]))
print(q5)
"""
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE user.name IN (%(name_1)s, %(name_2)s)
"""
# 空判断 None 不等于 ""，""是字符串
print(session.query(User).filter(User.name == None).all())
print(session.query(User).filter(User.name.is_(None)).all())
print(session.query(User).filter(User.name.isnot(None)).all())
# 多条件查询
print(session.query(User).filter(User.name == "Glen", User.id == 1))
"""
SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password
FROM user
WHERE user.name = %(name_1)s AND user.id = %(id_1)s
"""
# 选择条件
from sqlalchemy import or_, and_, all_, any_
print(session.query(User).filter(or_(User.name == 'jingqi', User.password == 'qwe123')))
"""
SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password
FROM user
WHERE user.name = %(name_1)s OR user.password = %(password_1)s
"""
print(session.query(User).filter(and_(User.name == 'jingqi2', User.password == '111')))
"""
SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password
FROM user
WHERE user.name = %(name_1)s AND user.password = %(password_1)s
"""
# 聚合函数的使用
from sqlalchemy import func,extract
print(session.query(User.password, func.count(User.id)).group_by(User.password))
"""
SELECT user.password AS user_password, count(user.id) AS count_1
FROM user GROUP BY user.password
"""
print(session.query(User.password, func.count(User.id)).group_by(User.password).having(func.count(User.id) > 1))
"""
SELECT user.password AS user_password, count(user.id) AS count_1
FROM user GROUP BY user.password
HAVING count(user.id) > %(count_2)s
"""
# print(session.query(User.password, func.sum(User.id)).group_by(User.password).all())
# print(session.query(User.password, func.max(User.id)).group_by(User.password).all())
# print(session.query(User.password, func.min(User.id)).group_by(User.password).all())
# #使用extract提取时间中的分钟或者天来分组
# print( session.query(extract('minute', User.creatime).label('minute'),func.count('*').label('count')).group_by('minute').all() )
# print( session.query(extract('day', User.creatime).label('day'),func.count('*').label('count')).group_by('day').all() )
3、ORM多表操作
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Glen
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import Column, Integer, String, DateTime, create_engine, ForeignKey
import datetime
engine = create_engine("mysql+pymysql://root:starcor@192.168.120.71/day12?charset=utf8", echo=True) # 创建数据库引擎
Base = declarative_base()
class User(Base): # 继承基类
__tablename__ = "user" # 对应数据库中的表名
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64))
password = Column(String(64))
def __repr__(self):
return "<User %s %s>" % (self.id, self.name)
class UserInfo(Base):
__tablename__ = "user_info"
id = Column(Integer, primary_key=True, autoincrement=True)
id_card = Column(Integer, nullable=False, unique=True)
last_login = Column(DateTime, default=datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))
login_num = Column(Integer, default=0)
user_id = Column(Integer, ForeignKey("user.id"))
# addr_id = Column(Integer, ForeignKey("addr.id")) # 可以关联多个外键
# `relationship`会在`User`表里面添加一个"info"属性，通过这个属性就可以查询对应的`user_details`表中的所有字段。省去了很多的代码。
user = relationship("User", backref="info", cascade="all", foreign_keys=[user_id])
# addr = relationship("Addr", backref="info", cascade="all", foreign_keys=[addr_id]) # 多外键时需要指定外键
def __repr__(self):
return "<UserInfo %s %s>" % (self.id, self.user_id)
"""
CREATE TABLE user_info (
id INTEGER NOT NULL AUTO_INCREMENT,
id_card INTEGER NOT NULL,
last_login DATETIME,
login_num INTEGER,
user_id INTEGER,
PRIMARY KEY (id),
UNIQUE (id_card),
FOREIGN KEY(user_id) REFERENCES user (id)
)
"""
"""
这里要注意`relationship`默认是一对多的关系，使用`uselist=False`则表示一对一的关系，`cascade` 是自动关系处理，就和MySQL中的`ON DELETE`类似，但是有区别，参数选项如下：
`cascade` 所有的可选字符串项是:
- *all* , 所有操作都会自动处理到关联对象上.
- *save-update* , 关联对象自动添加到会话.
- *delete* , 关联对象自动从会话中删除.
- *delete-orphan* , 属性中去掉关联对象, 则会话中会自动删除关联对象.
- *merge* , `session.merge()` 时会处理关联对象.
- *refresh-expire* , `session.expire()` 时会处理关联对象.
- *expunge* , `session.expunge()` 时会处理关联对象.
"""
Base.metadata.create_all(engine) # 父类调用子类，创建表
session_class = sessionmaker(bind=engine) # 创建数据库会话，返沪session class
session = session_class() # 生成session实例
# info1 = UserInfo(id_card=556, login_num=6, user_id=2)
# info2 = UserInfo(id_card=553, login_num=7, user_id=5)
# info3 = UserInfo(id_card=111, login_num=66, user_id=3)
# info4 = UserInfo(id_card=188, login_num=66, user_id=3)
# session.add_all([info1, info3, info2, info4])
# session.commit()
# 表关系查询 外键关联
info1 = session.query(UserInfo).first() # 通过info查询user
print(info1.user.name)
user1 = session.query(User).get(2) # 通过user查询info
print(user1.info[0].last_login)
# 多表查询
# 内连接
print(session.query(User, UserInfo).filter(User.id == UserInfo.user_id).all()) # 返回两个对象 [(<User 2 Glen>, <UserInfo 1 2>), (<User 2 Glen>, <UserInfo 2 2>)]
q1 = session.query(User.name, UserInfo.last_login).join(UserInfo, UserInfo.id == User.id).all()
print(q1)
"""
SELECT user.name AS user_name, user_info.last_login AS user_info_last_login
FROM user INNER JOIN user_info ON user_info.id = user.id
"""
# 外连接
q2 = session.query(User.name, UserInfo.last_login).outerjoin(UserInfo, UserInfo.id == User.id)
print(q2)
"""
SELECT user.name AS user_name, user_info.last_login AS user_info_last_login
FROM user LEFT OUTER JOIN user_info ON user_info.id = user.id
"""
# union 关联
q3 = session.query(User.id)
q4 = session.query(UserInfo.user_id)
print(q3.union(q4))
"""
SELECT anon_1.user_id AS anon_1_user_id
FROM (SELECT user.id AS user_id
FROM user UNION SELECT user_info.user_id AS user_info_user_id
FROM user_info) AS anon_1
"""
# 子查询
from sqlalchemy import all_, any_
q5 = session.query(UserInfo.id).subquery() # 定义一个子查询
print(session.query(User).filter(User.id > all_(q5)).all()) # 比q5里面的所有值都大
"""
SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password
FROM user
WHERE user.id > ALL (SELECT user_info.id
FROM user_info)
"""
print(session.query(User).filter(User.id < any_(q5)).all()) # 比q5里面的任意一个值小
"""
SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password
FROM user
WHERE user.id < ANY (SELECT user_info.id
FROM user_info)
"""
# 执行原生sql
res = session.execute("select * from user")
print(res.fetchone())
print(res.fetchall())
4、多对多
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Glen
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import Column, Integer, String, DateTime, create_engine, ForeignKey, Table
import datetime
engine = create_engine("mysql+pymysql://root:starcor@192.168.120.71/day12?charset=utf8", echo=True) # 创建数据库引擎
Base = declarative_base()
session_class = sessionmaker(engine)
session = session_class()
class Book(Base):
__tablename__ = "book"
id = Column(Integer, primary_key=True)
name = Column(String(64))
pub_data = Column(DateTime, default=datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))
def __repr__(self):
return "<Class Book {name}>".format(name=self.name)
book_author = Table("book2author", Base.metadata,
Column("book_id", Integer, ForeignKey("book.id")),
Column("author_id", Integer, ForeignKey("author.id"))
)
class Author(Base):
__tablename__ = "author"
id = Column(Integer, primary_key=True)
name = Column(String(64))
books = relationship("Book", secondary=book_author, backref="authors") # 关联book 和 book_author
def __repr__(self):
return "<Class Author {name}>".format(name=self.name)
Base.metadata.create_all(engine) # 父类调用子类，创建表
a1 = Author(name="Glen")
a2 = Author(name="alice")
a3 = Author(name="张小东")
b1 = Book(name="《海》")
b2 = Book(name="《山海经》")
b3 = Book(name="《浮生》")
b4 = Book(name="《空天一色》")
b1.authors = [a1, a3, a2]
b2.authors = [a1, a3]
a2.books = [b3, b4, b1]
# session.add_all([a1, a2, a3, b1, b2, b3, b4])
# session.commit()
"""
mysql> select * from book;
+----+--------------------+---------------------+
| id | name | pub_data |
+----+--------------------+---------------------+
| 1 | 《海》 | 2018-10-23 17:07:46 |
| 2 | 《浮生》 | 2018-10-23 17:07:46 |
| 3 | 《空天一色》 | 2018-10-23 17:07:46 |
| 4 | 《山海经》 | 2018-10-23 17:07:46 |
+----+--------------------+---------------------+
mysql> select * from author;
+----+-----------+
| id | name |
+----+-----------+
| 1 | Glen |
| 2 | 张小东 |
| 3 | alice |
+----+-----------+
mysql> select * from book2author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 4 | 1 |
| 4 | 2 |
| 3 | 3 |
+---------+-----------+
"""
# 通过author查询book
authors = session.query(Author).filter(Author.name == "张小东").all()
for author in authors:
print("---", author.id, author.name, author.books)
# 通过book查询author
books = session.query(Book).filter(Book.name == "《山海经》").all()
for book in books:
print("---", book.id, book.name, book.authors)
# 关联删除
# author_obj = authors[0]
# book_obj = books[0]
# book_obj.authors.remove(author_obj) # 从书的作者中移除一个作者
# session.commit()
"""
mysql> select * from author;
+----+-----------+
| id | name |
+----+-----------+
| 1 | Glen |
| 2 | 张小东 |
| 3 | alice |
+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from book;
+----+--------------------+---------------------+
| id | name | pub_data |
+----+--------------------+---------------------+
| 1 | 《海》 | 2018-10-23 17:44:39 |
| 2 | 《浮生》 | 2018-10-23 17:44:39 |
| 3 | 《空天一色》 | 2018-10-23 17:44:39 |
| 4 | 《山海经》 | 2018-10-23 17:44:39 |
+----+--------------------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from book2author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 3 |
| 3 | 3 |
| 1 | 3 |
| 1 | 1 |
| 4 | 1 |
| 1 | 2 |
+---------+-----------+
6 rows in set (0.00 sec)
"""
# 直接删除
# glen = session.query(Author).filter(Author.name == "Glen").first()
# session.delete(glen) # 删除作者时，会把这个作者跟所有书的关联关系数据也自动删除
# session.commit()
三、学员管理系统
主题：学员管理系统
需求：
- 用户角色，讲师＼学员， 用户登陆后根据角色不同，能做的事情不同，分别如下
- 讲师视图
- 管理班级，可创建班级，根据学员qq号把学员加入班级
- 可创建指定班级的上课纪录，注意一节上课纪录对应多条学员的上课纪录， 即每节课都有整班学员上， 为了纪录每位学员的学习成绩，需在创建每节上课纪录是，同时 为这个班的每位学员创建一条上课纪录
- 为学员批改成绩， 一条一条的手动修改成绩
- 学员视图
- 提交作业
- 查看作业成绩
- 一个学员可以同时属于多个班级，就像报了Linux的同时也可以报名Python一样， 所以提交作业时需先选择班级，再选择具体上课的节数
- 附加：学员可以查看自己的班级成绩排名
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import Column, Integer, DateTime, ForeignKey, String, create_engine, Enum, UniqueConstraint
import datetime
engine = create_engine("mysql+pymysql://root:starcor@192.168.120.71/stu?charset=utf8", echo=False) # 创建数据库引擎
Base = declarative_base()
class Student(Base):
__tablename__ = "student"
id = Column(Integer, primary_key=True)
name = Column(String(64))
class_records = relationship("ClassRecord", backref="student")
def __repr__(self):
return "<Class Student {name}>".format(name=self.name)
class Teacher(Base):
__tablename__ = "teacher"
id = Column(Integer, primary_key=True)
name = Column(String(64))
major = Column(Enum("python", "java", "linux"))
def __repr__(self):
return "<Class Teacher {name}>".format(name=self.name)
class ClassStudentRelation(Base):
"""学生班级对应关系表"""
__table_args__ = (UniqueConstraint("student_id", "class_id", name="student_class"),)
__tablename__ = "class_student_relation"
id = Column(Integer, primary_key=True, autoincrement=True)
student_id = Column(Integer, ForeignKey("student.id"))
class_id = Column(Integer, ForeignKey("class.id"))
class ClassInfo(Base):
__tablename__ = "class"
id = Column(Integer, primary_key=True)
major = Column(Enum("python", "java", "linux"))
current_times = Column(Integer, comment="当前第几节")
total_times = Column(Integer, comment="该课程总共几节")
teacher_id = Column(Integer, ForeignKey("teacher.id"))
students = relationship("Student", secondary="class_student_relation", backref="classes")
def __repr__(self):
return "<Class ClassInfo {name}>".format(name=self.major)
class ClassRecord(Base):
__table_args__ = (UniqueConstraint("student_id", "class_id", "class_times", name="student_class_times"),)
__tablename__ = "class_record"
id = Column(Integer, primary_key=True, autoincrement=True)
class_id = Column(Integer, ForeignKey("class.id"))
class_times = Column(Integer, comment="课时,第几节")
student_id = Column(Integer, ForeignKey("student.id"))
grade = Column(Integer)
status = Column(Integer, comment="0未上课，1上课")
create_time = Column(DateTime, default=datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))
modify_time = Column(DateTime, default=datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))
def __repr__(self):
return "<Class ClassRecord {name}>".format(name=self.student_id)
# Base.metadata.create_all(engine) # 父类调用子类，创建表
from day12.student_manager.base_init import *
from sqlalchemy import and_, or_
# session_class = sessionmaker(bind=engine) # 创建数据库会话，返沪session class
# session = session_class() # 生成session实例
class TeacherClient(object):
def __init__(self, tea_id):
self.id = tea_id
session_cla = sessionmaker(bind=engine)
self.session = session_cla()
# self.teacher = self.session.query(Teacher).get(tea_id).first() # 创建数据库会话，返沪session class 生成session实例
def create_class(self, course, total_times):
"""创建班级"""
class_obj = ClassInfo(major=course, current_times=1, total_times=total_times, teacher_id=self.id)
self.session.add(class_obj)
self.session.commit()
def add_user_to_class(self, stu_id, class_id):
"""根据学生id将该学生添加到班级"""
stu_obj = self.session.query(Student).get(stu_id) # 查询学生
class_obj = self.session.query(ClassInfo).get(class_id) # 获取班级
class_obj.students.append(stu_obj)
# self.session.add_all([stu_obj, class_obj])
self.session.commit()
def create_class_record(self, class_id):
"""每节课创建一个班级的上课记录, 同时课程的课时加1"""
class_obj = self.session.query(ClassInfo).get(class_id) # 获取班级
stu_objs = class_obj.students
for student in stu_objs:
record_obj = ClassRecord(class_id=class_id, class_times=class_obj.current_times, student_id=student.id,
grade=0, status=0) # 创建记录
student.class_records.append(record_obj) # 添加记录
class_obj.current_times += 1 if class_obj.current_times < class_obj.total_times else 0 # 判断课时
if class_obj.total_times == class_obj.current_times: # 判断是否是最后一节课
print("最后一节课")
self.session.commit()
elif class_obj.current_times < class_obj.total_times:
self.session.commit()
else:
print("该课程已经结束")
def correct_homework(self, stu_id, class_id, class_times, grade):
"""修改学生上课记录的成绩"""
record_obj = self.session.query(ClassRecord).filter(and_(ClassRecord.class_id == class_id,
ClassRecord.student_id == stu_id,
ClassRecord.class_times == class_times)).first()
if record_obj.status == 0:
print("没交作业")
else:
record_obj.grade = grade
self.session.commit()
def cmd_help():
print("""
操作命令如下
创建班级：create_class(课程,节数)
添加学生：add_user_to_class(stu_id, class_id)
创建上课记录：create_class_record(class_id)
批改作业：correct_homework(self, stu_id, class_id, class_times, grade)
退出：q
帮助：help
""")
print("please enter teacher id")
t_id = int(input(">>"))
teacher_client = TeacherClient(t_id)
cmd_help()
while True:
cmd = input(">>")
if cmd == "q":
exit()
elif cmd == "help":
cmd_help()
else:
eval("teacher_client.{cmd}".format(cmd=cmd))
from day12.student_manager.base_init import *
from sqlalchemy import and_, or_, desc
class StudentClient(object):
def __init__(self, stu_id):
self.id = stu_id
session_class = sessionmaker(bind=engine)
self.session = session_class()
def submit_homework(self, class_id, class_times):
"""交作业"""
record_obj = self.session.query(ClassRecord).filter(and_(ClassRecord.class_id == class_id,
ClassRecord.student_id == self.id,
ClassRecord.class_times == class_times)).first()
record_obj.status = 1
self.session.commit()
print("作业提交完成")
def show_grade(self, class_id, class_times):
"""查看成绩"""
record_objs = self.session.query(ClassRecord).filter(and_(ClassRecord.class_id == class_id,
ClassRecord.class_times == class_times)
).order_by(desc(ClassRecord.grade)).all()
print("排名 名字 分数")
for n, record_obj in enumerate(record_objs):
print("%s %s %s" % (n+1, record_obj.student.name, record_obj.grade))
def cmd_help():
print("""
操作命令如下
查看排名：show_grade(class_id, class_time)
提交作业：submit_homework(class_id, class_times)
退出：q
帮助：help
""")
print("please enter student id")
stu_id = int(input(">>"))
student_client = StudentClient(1)
cmd_help()
while True:
cmd = input(">>")
if cmd == "q":
exit()
elif cmd == "help":
cmd_help()
else:
eval("student_client.{cmd}".format(cmd=cmd))
