Flask-SQLAlchemy数据库操作

数据库基本操作

• 在Flask-SQLAlchemy中，插入、修改、删除操作，均由数据库会话管理。

  • 会话用 db.session 表示。在准备把数据写入数据库前，要先将数据添加到会话中然后调用 commit() 方法提交会话。

• 在 Flask-SQLAlchemy 中，查询操作是通过 query 对象操作数据。

  • 最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。

app主函数

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.debug = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///./db/pms.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)

@app.route('/')
def hello_world():
    return 'Hello World!'

if __name__ == '__main__':
    app.run()

在视图函数中定义模型类

 

class Department(db.Model):
   ...
   ...
class Employee(db.Model):
    ...
    department_id = db.Column(db.INTEGER, db.ForeignKey('department.id'))
    department = db.relationship('Department', backref=db.backref('employee', lazy='dynamic'))
    ...
   

• 其中realtionship描述了Employee和Department的关系。在此文中，第一个参数为对应参照的类"Department"
• 第二个参数backref为类Employee申明新属性的方法
• 第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
  • 如果设置为子查询方式(subquery)，则会在加载完Department对象后，就立即加载与其关联的对象，这样会让总查询数量减少，但如果返回的条目数量很多，就会比较慢
  • 另外,也可以设置为动态方式(dynamic)，这样关联对象会在被使用的时候再进行加载，并且在返回前进行过滤，如果返回的对象数很多，或者未来会变得很多，那最好采用这种方式

 

常用的SQLAlchemy查询过滤器

过滤器	说明
filter()	把过滤器添加到原查询上，返回一个新查询
filter_by()	把等值过滤器添加到原查询上，返回一个新查询
limit	使用指定的值限定原查询返回的结果
offset()	偏移原查询返回的结果，返回一个新查询
order_by()	根据指定条件对原查询结果进行排序，返回一个新查询
group_by()	根据指定条件对原查询结果进行分组，返回一个新查询

常用的SQLAlchemy查询执行器

方法	说明
all()	以列表形式返回查询的所有结果
first()	返回查询的第一个结果，如果未查到，返回None
first_or_404()	返回查询的第一个结果，如果未查到，返回404
get()	返回指定主键对应的行，如不存在，返回None
get_or_404()	返回指定主键对应的行，如不存在，返回404
count()	返回查询结果的数量
paginate()	返回一个Paginate对象，它包含指定范围内的结果

创建表：

db.create_all()

删除表

db.drop_all()

插入一条数据

ro1 = Department('质检部门')
db.session.add(ro1)
db.session.commit()
#再次插入一条数据
ro2 = Department('技术部门')
db.session.add(ro2)
db.session.commit()

插入excel多条数据

import pandas as pd
filepath = "E:\\员工信息表.xlsx"
data = pd.read_excel(filepath)
for n in range(0,len(data)):
    employees = Employee(...)
    db.session.add(employees)
    db.session.commit()

查询:filter_by精确查询

返回名字等于wang的所有人

Employee.query.filter_by(name='wang').all()

 

first()返回查询到的第一个对象

Employee.query.first()

all()返回查询到的所有对象

Employee.query.all()

 

filter模糊查询，返回名字结尾字符为g的所有数据。

Employee.query.filter(Employee.name.endswith('g')).all()

 

get():参数为主键，如果主键不存在没有返回内容

Employee.query.get()

逻辑非，返回名字不等于wang的所有数据

Employee.query.filter(Employee.name!='wang').all()

 

not_ 相当于取反

from sqlalchemy import not_
Employee.query.filter(not_(Employee.name=='chen')).all()
或

Employee.query.filter(~Employee.name=='chen').all()

 

逻辑与，需要导入and，返回and()条件满足的所有数据

from sqlalchemy import and_
Employee.query.filter(and_(Employee.name!='wang',Employee.salary >3000)).all()
或
Employee.query.filter(Employee.name!='wang',Employee.salary >3000).all()

 

逻辑或，需要导入or_

from sqlalchemy import or_
Employee.query.filter(or_(Employee.name!='wang',Employee.salary > 3000).all()

 

查询数据后删除

emp = Employee.query.first()
db.session.delete(emp)
db.session.commit()
Employee.query.all()

更新数据

emp = Employee.query.first()
emp.name = 'dong'
db.session.commit()
Employee.query.first()

 

关联查询示例：

角色和用户的关系是一对多的关系，一个角色可以有多个用户，一个用户只能属于一个角色。

• 查询部门的所有工人（切记要使用join进行联合其他表进行查找）

for emp in db.session.query(Department.name, Employee.salary).join(Department).filter(Department.name == '质检部门'):
    print(emp)

统计数量：

from sqlalchemy import func
db.session.query(func.count(Employee.gender),Employee.gender).group_by(Employee.gender).all()