1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
|
import pymysql
class OperateEmployeeDB: def __init__(self): try: self.conn = pymysql.connect('localhost', '用户名', '密码', 'py_zuoye', 3306) self.cur = self.conn.cursor()
sql = '''CREATE TABLE IF NOT EXISTS py_zuoye.`tb_emp`( `eid` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `sex` VARCHAR(10) NOT NULL, `birthday` VARCHAR(40) NOT NULL, `intro` VARCHAR(255) NOT NULL, `profession` INT NOT NULL, `dept` INT NOT NULL, PRIMARY KEY ( `eid` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8''' self.cur.execute(sql) self.conn.commit() print('创建员工信息表tb_emp成功!') except Exception as e: print('创建数据库或创建外键失败', e)
def add(self, sql): try: self.cur.execute(sql) self.conn.commit() except Exception as e: raise e
def select(self, sql): try: cur1 = self.cur.execute(sql) return self.cur except Exception as e: raise e
def update(self, sql): try: self.cur.execute(sql) self.conn.commit() except Exception as e: raise e
def delete(self, sql): try: self.cur.execute(sql) self.conn.commit() except Exception as e: raise e
def __del__(self): self.cur.close() self.conn.close()
if __name__ == '__main__': employee = OperateEmployeeDB() print('1.添加记录:') try: empList = [('100001', 'rick', '男', '1990-10-10', '天才科学家', '2', '3'), ('100002', '沐橙', '女', '1999-10-11', '有丰富的会计工作经历', '4', '7'), ('100003', '派大星', '男', '1995-01-25', '身体强壮,有格斗经验', '6', '11'), ('100004', '王明', '男', '1997-09-19', '名校毕业,法学专业', '10', '5')] for emp in empList: addSql = f'insert INTO tb_emp VALUES({emp[0]},"{emp[1]}","{emp[2]}","{emp[3]}","{emp[4]}",{emp[5]},{emp[6]}) ' employee.add(addSql) print('添加记录成功!') except Exception as e: print('添加记录失败', e)
print('2.查询数据:') try: id = int(input('请输入要查询的员工编号eid:')) selectSql = f'select * from tb_emp where eid = {id}' cur1 = employee.select(selectSql) print('员工信息如下(专业编号和部门编号见专业表tb_profession和部门表tb_dept):') for row in cur1.fetchall(): print(f'编号:{row[0]},姓名:{row[1]},性别:{row[2]},出生日期:{row[3]},个人介绍:{row[4]},专业编号:{row[5]},部门编号:{row[6]}') except Exception as e: print('查询失败!请检查姓名是否输入正确', e)
print('3.更新数据:') try: print('更新记录中,请输入以下信息:') no = int(input('请输入要更新的员工编号:')) name = input('请输入更新后员工姓名:') sex = input('请输入更新后员工性别:') birthday = input('请输入更新后员工出生日期:') math = input('请输入更新后员工个人介绍:') english = int(input('请输入更新后员工专业编号:')) os = int(input('请输入更新后员工部门编号:')) updateSql = f'UPDATE tb_emp SET name = "{name}", sex = "{sex}", birthday = "{birthday}", intro = "{math}", profession = "{english}", dept = "{os}" where eid = {no}' employee.update(updateSql) print('更新记录成功!') except Exception as e: print('更新记录失败', e)
print('4.删除记录') try: no = int(input('请输入要删除员工编号:')) deleteSql = f'DELETE FROM tb_emp WHERE eid = {no}' employee.delete(deleteSql) print('删除记录成功!') except Exception as e: print('删除记录失败', e)
|