数据管理技术-大作业

数据管理技术-大作业实验报告

描述

• 本次期中⼤作业要求同学们从后端数据库的⻆度，对数据库进⾏设计，以及对数据库进⾏优化。本次⼤作业的⽬的是让同学们对数据库的基本原理有⼀个更加深刻的理解，同时也是为了让同学们对数据库的基本操作更加熟悉。
• 本次实验需要针对数据库的CURD操作以及索引进⾏后端开发，并且向应⽤层提供RESTful接⼝服务。（具体的接⼝要求下⾯详细说明）
• 作为⼀个可选项，同学们可以尝试将全部服务部署到docker镜像中。

项目结构

- src
 - ORM.py
 - RESTful.py
- insert.sh
- start.sh
- readme.md
- 实验报告.pdf

各部分功能

ORM.py

实现ORM框架以及CURD服务

RESTful.py

实现RESTful服务

insert.sh

运行ORM.py，实现表的创建以及数据的插入。

start.sh

运行RESTful.py，创建RESTful服务器。

使用的技术

• 通过ORM框架建立题目所需表。
• 利用数据库CURD将数据注入到对应表中。
• 设计RESTful接口，实现增删改查的接口提供。
• 设计*.sh批处理文件，提供以上服务的启动脚本。

开发思路

表的创建

利用ORM框架，定义表的相应类，这里以demp_manager表为例

class Dept_manager(db.Model) :
    emp_no = db.Column(db.Integer, db.ForeignKey("employees.emp_no", ondelete='CASCADE'), nullable=False)
    dept_no = db.Column(db.CHAR(4), db.ForeignKey("departments.dept_no", ondelete='CASCADE'), nullable=False, index=True)
    from_date = db.Column(db.Date, nullable=False)
    to_date = db.Column(db.Date, nullable=False)

    __table_args__ = (
        db.PrimaryKeyConstraint('emp_no', 'dept_no'),
    )

    def to_dict(self):
        return {
            'emp_no' : self.emp_no,
            'dept_no': self.dept_no,
            'from_date' : self.from_date,
            'to_date': self.to_date,
        }

类的属性即为该表的不同列。db.Integer为该列的数据类型定义，db.ForeignKey为外键的设置，nullable=False将该列设置为非空，index=True为表设置该列的索引，db.PrimaryKeyConstraint设置联合主键。

with app.app_context() :
    db.drop_all()
    db.create_all()

最终通过create_all创建所有表。

触发器的创建

首先设计触发器的sql语句,以插入触发器为例

sql_trigger_insert = text("""
create trigger after_insert
after insert on dept_manager
for each row
begin
    insert into dept_manager_title
    values(new.emp_no, new.from_date, new.to_date);
end;
""")

调用engine的方法，执行sql语句。

with engine.connect() as connect:
    connect.execute(sql_trigger_insert)
    connect.execute(sql_trigger_delete)

数据注入

首先将数据从csv文件中读出，设计方法：

def read_csv_file(file_path):
    rows = []
    with open(file_path, 'r', newline='') as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            rows.append(row)
    return rows

之后遍历数据，将数据转化为表的相应对象，以employees为例

# employees插入
reader = read_csv_file('./employees.csv')
rows = []
for row in reader:
    employee = Employees(
        emp_no=row['emp_no'],
        birth_date=row['birth_date'],
        first_name=row['first_name'],
        last_name=row['last_name'],
        gender=row['gender'],
        hire_date=row['hire_date']
    )
    rows.append(employee)

利用session的bulk_save_objects方法将数据批量注入表中

session.bulk_save_objects(rows)
session.commit()

RESTful接口的实现

以更新数据接口为例

# 更新数据
@app.route('/api/v1/<table_name>', methods=['PUT'])
def update_data(table_name):
    data = request.json
    cursor = db.cursor()
    if table_name == 'departments' or table_name == 'employees':
        op = 1
    elif table_name == 'dept_emp' or table_name == 'dept_manager':
        op = 2
    elif table_name == 'titles':
        op = 3
    else:
        op = None
    keys = list(data.keys())
    key1 = ' and '.join([f"{key}='{data[key]}'" for key in keys[:op]])
    key2 = ', '.join([f"{key}='{data[key]}'" for key in keys[op:]])

    sql = f'update {table_name} set {key2} where {key1}'
    cursor.execute(sql)
    db.commit()
    return jsonify({'message': f' row updated successfully'}), 200

由于主键不同，因此需要首先判断更新表是哪一个，再通过查询主键来进行更改。