flask-sqlalchemy数据库单表操作(三)
1、参数配置
对数据库连接字符串等信息进行配置，这里以类的形式配置
# 配置参数
class Config(object):
# 连接字符串
SQLALCHEMY_DATABASE_URL = 'mysql://root:root@127.0.0.1:3306/python_db'
# 设置SQL自动跟踪数据库
SQLALCHEMY_TRACE_MODIFICATIONS = True
# 查询显示SQL语句
SQLALCHEMY_ECHO = True
app.config.from_object(Config)
2、创建模型类
创建数据库模型类学生类和班级类
# 学生模型
class Student(db.Model):
__tablename__ = 'db_student1' # 指名数据库表名
# 指名主键 整型主键 自动递增
id = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(20))
sex = db.Column(db.String(2))
birthday = db.Column(db.Date)
address = db.Column(db.String(100))
cid = db.Column(db.Integer,db.ForeignKey('db_class1.id'))
# 给这个Student模型添加一个Class属性（关系表），
# Class为要连接的表，backref为定义反向引用
# 与生成表结构无关，仅用于查询方便
isClass = db.relationship('Class',backref='students')
#方便输出 类似java中tostring方法
def __repr__(self):
return ('姓名:%s 性别:%s 生日:%s 地址:%s' %(self.name,self.sex,self.birthday,self.address))
# 班级模型
class Class(db.Model):
__tablename__ = 'db_class1'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
3、单表操作
我们对表、文档此类的数据库实体就可以简化成对 Python 对象的操作。
3.1、添加
# db.create_all()
stu1 = Student(name='张三',sex='男',birthday='1992-02-02',address='河南省',cid=2)
db.session.add(stu1)
class1 = Class(name='安卓3班')
db.session.add(class1)
db.session.add(class1)
db.session.commit()
3.2、修改
修改数据需要先查询出数据才可以修改，这里以修改学生名字为例子
print('-----修改之前-----')
stu = db.session.query(Student).get(2)
print(stu)
stu.name = '李四'
db.session.commit()
print('-----修改之后-----')
stu = db.session.query(Student).get(2)
print(stu)
3.3、删除
删除其实同修改一样，需要存在这个要删除的对象，才可以删除。
print('-----删除之前-----')
stu = db.session.query(Student).get(2)
print(stu)
db.session.delete(stu)
db.session.commit()
print('-----修改之后-----')
stu = db.session.query(Student).get(2)
print(stu) # None
3.4、查询
3.4.1、查询所有
# 查询所有
# stus = db.session.query(Student).all()
# for row in stus:
# print(row)
stus = Student.query.all()
for row in stus:
print(row)
3.4.1、条件查询
指定列
stus = db.session.query(Student.id,Student.name.label('sname')).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS sname
# FROM db_student1
默认条件and
filter功能强大，比filter_by的功能更强大，支持比较运算符，支持or_、in_等语法。
# 默认条件 and
stus = Student.query.filter(Student.id==4,Student.name=='张三').all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.id = %s AND db_student1.name = %s
between之间
stus = Student.query.filter(Student.id.between(4,5)).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.id BETWEEN %s AND %s
in 包含
stus = Student.query.filter(Student.id.in_([3,4,5])).all()
for item in stus:
print(item)
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.id IN (%s, %s, %s)
and和or
and其实是查询多个条件的默认选项。
stus = Student.query.filter(and_(Student.id == 4,Student.name == '李四')).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.id = %s AND #db_student1.name = %s
stus = Student.query.filter(or_(Student.id == 4, Student.id == 5)).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.id = %s OR db_student1.id = %s
通配符
# 通配符
stus = Student.query.filter(Student.name.like('_三%')).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# WHERE db_student1.name LIKE %s
排序
升序与降序
stus = Student.query.filter().order_by(Student.birthday.desc()).all()
for item in stus:
print(item)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1 ORDER BY # db_student1.birthday DESC
分组
ret = db.session.query(
Student.sex,func.count(Student.id)
).group_by(Student.sex).all()
for item in ret:
print(ret)
# SQL语句
# SELECT db_student1.sex AS db_student1_sex, count(db_student1.id) AS count_1
# FROM db_student1 GROUP BY db_student1.sex
分组筛选
ret = db.session.query(
Users.dep_id,
func.count(Users.id),
).group_by(Users.dep_id).having(func.count(Users.id) >=2).all()
切片分页
stus = db.session.query(Student)[1:3]
for stu in stus:
print(stu)
# SQL语句
# SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid
# FROM db_student1
# LIMIT %s, %s
union 和 union all
Union All：对两个结果集进行并集操作，包括重复行，不进行排序
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
q1 = db.session.query(Student.name).filter(Student.id > 2)
q2 = db.session.query(Class.name).filter(Class.id < 2)
ret = q1.union(q2).all()
网友评论