Python学习

2021-05-03  本文已影响0人  逛逛_堆栈

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()
上一篇下一篇

猜你喜欢

热点阅读