程序设计实训 - 题目 19 实现数据库的操作

[2019301248-侯星宇]

程序设计实训 - Python

题目 19

思路分析

SQLite 是一种用 C 语言写的轻量级嵌入式数据库，它的数据库就是一个文件。SQLite 不需要一个单独的服务器进程或操作的系统，不需要配置，这意味着不需要安装或管理，所有的维护都来自于 SQLite 软件本身

1. 导入 sqlite3 模块

sqlite3 是内置模块，不需要安装，直接 import 导入即可：

import sqlite3

2. 创建与 SQLite 数据库的连接

使用 sqlite3.connect() 函数连接数据库，返回一个 Connection 对象，我们就是通过这个对象与数据库进行交互。如果该数据库文件不存在，那么它会被自动创建。

# 创建与数据库的连接
conn = sqlite3.connect(path)

建立与数据库的连接后，需要创建一个游标 cursor 对象，该对象的 .execute() 方法可以执行 sql 命令，让我们能够进行数据操作。

#创建一个游标 cursor
cur = conn.cursor()

3. 在 SQLite 数据库中创建表

使用 cur.execute(sql) 完成创建，主要的创建内容在 sql 语句中

4. 常用方法

• sqlite3.connect(database [,timeout ,other optional arguments]) --- 该 API 打开一个到 SQLite 数据库文件 database 的链接。如果给定的数据库名称 filename 不存在，则该调用将创建一个数据库。
• cursor.execute(sql [, optional parameters]) --- 该例程执行一个 SQL 语句
• connection.cursor() --- 创建一个 cursor，在 Python 数据库编程中用到
• connection.commit() --- 该方法提交当前的事务
• connection.close() --- 该方法关闭数据库连接。请注意，这不会自动调用 commit()。如果之前未调用 commit() 方法，就直接关闭数据库连接，所做的所有更改将全部丢失

代码实现

# NPU 程序设计实训 HW19 实现数据库的操作
# 函数 ()
import sqlite3
path = 'D:/python project test/19_test.db'

# 1，初始化数据库
def create_db(path):

    # 创建与数据库的连接
    conn = sqlite3.connect(path)
    # 创建一个游标 cursor
    cur = conn.cursor()

    # 创建 person 表
    person_table = """ CREATE TABLE Persons(
                        NAME VARCHAR(32) NOT NULL,
                        GENDER VARCHAR(2) NOT NULL,
                        BIRTH VARCHAR NOT NULL,
                        ID VARCHAR(18) PRIMARY KEY NOT NULL,
                        FOREIGN KEY (POSITIONID) REFERENCES Position(POSITIONID)
                        );"""
    # 创建 position 表
    position_table = """ CREATE TABLE Position(
                        POSITIONID VARCHAR  PRIMARY KEY,
                        SALARY     INTEGER    NOT NULL
                        );"""
    # position 表插入数据
    position_table_insert = 'insert into Position values(?,?);'

    # 建立两个表
    try:
        cur.execute(position_table)
        cur.execute(person_table)
        cur.executemany(position_table_insert, [('A', 10000), ('B', 6000), ('C', 3000), ('D', 1000)])
        conn.commit()
    except Exception as e:
        print(e)
        return -1
    else:
        return 0
    finally:
        cur.close()
        conn.close()

# 2，新进人员
def new_employee(person, level):
    # 创建链接和游标
    conn = sqlite3.connect(path)
    cur = conn.cursor()

    new_person = """INSERT INTO PERSON VALUES (?,?,?,?,?);"""
    try:
        cur.execute(new_person, person+(level,))
        conn.commit()
    except Exception as e:
        print(e)
        return -1
    else:
        return 0
    finally:
        cur.close()
        conn.close()

# 3，删除人员
def delete_employee(person):
    conn = sqlite3.connect(path)
    cur = conn.cursor()

    delete_person = f'DELETE FROM Person WHERE ID={person};'
    try:
        cur.execute(delete_person)
        conn.commit()
    except Exception as e:
        print(e)
        return -1
    else:
        return 0
    finally:
        cur.close()
        conn.close()


# 4. 设置岗位薪水
def set_level_salary(level, salary):
    if level not in ['A', 'B', 'C', 'D'] or not isinstance(salary, int):
        return -1
    conn = sqlite3.connect(path)
    cur = conn.cursor()

    sql = f'UPDATE Position SET SALARY={salary} WHERE POSITIONID="{level}";'
    try:
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        return -1
    else:
        return 0
    finally:
        cur.close()
        conn.close()


# 5. 统计薪水开支
def get_total_salary():
    conn = sqlite3.connect(path)
    cur = conn.cursor()

    select = """SELECT SALARY FROM Person,Position WHERE Person.POSITIONID = Position.POSITIONID;"""
    try:
        cur.execute(select)
        ret = cur.fetchall()
    except Exception as e:
        print(e)
        return -1
    else:
        return sum([i[0] for i in ret])
    finally:
        cur.close()
        conn.close()