Python操作mysql数据库封装工具类

1.封装工具类

import pymysql

class PDBC:
    # 构造对象，创建数据库连接对象，并创建数据库操作对象
    def __init__(self, *, host=None, port=None, user=None, password=None, database=None):
        self.db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
        self.cur = self.db.cursor()

    # 执行DML、DDL操作
    def execute(self, sql, params=None):
        n = 0
        try:
            n = self.cur.execute(sql, params)
            self.db.commit()
            print("execute: ", sql, params)
        except Exception as e:
            print(e)
            self.db.rollback()
        finally:
            return n

    # 执行DQL操作
    def query(self, sql, params=None):
        n = self.cur.execute(sql, params)
        return self.cur.fetchmany(n)

    def __del__(self):
        self.cur.close()
        self.db.close()


# 将数据库连接属性的配置写到类的外部
db_connection_prop = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "root",
    "database": "test"
}

2.使用工具类

from utils.PyDBC import *


#界面
def getUi():
    print('*' * 20 + '欢迎使用学生管理系统' + '*' * 20)
    print('1.添加学生  2.查看学生列表  3.查看学生信息  4.删除学生  5.修改学生信息 6.退出系统')


#添加
def addStudent():
    name = input('请输入学生姓名:')
    gender = input('请输入学生性别:')
    sid = input('请输入学生身份证号码:')
    phonenumber = input('请输入学生电话号码:')
    sql = "INSERT INTO students VALUES (null, %s, %s, %s, %s, %s)"
    values = (name, gender, sid, phonenumber, sid[6:14:1])
    db.execute(sql, values)


#查看指定数据
def showStuData():
    name = input('请输入学生姓名:')
    sql = "select * from students where name=%s"
    values = (name)
    names = db.query(sql, values)
    for id, name, gender, sid, pnum, birthday in names:
        print(f'id：{id} 姓名：{name} 性别：{gender} 身份证号码：{sid} 电话号码：{pnum} 出生日期：{birthday}')


#查看全部数据
def showStuList():
    sql = "select id,name from students"
    names = db.query(sql)
    print('----- 学生列表 -----')
    for id, name in names:
        print(f'id：{id} 姓名：{name}')


#删除数据
def delStudent():
    id = int(input('请输入要删除学生的id:'))
    sql = "DELETE  from students where id=%s"
    values = (id)
    a = db.execute(sql, values)
    if a == 1:
        print('删除成功')
    else:
        print('删除失败')


#更改数据
def updateStudent():
    id = int(input('请输入要更新学生的id:'))
    name = input('请输入学生姓名:')
    gender = input('请输入学生性别:')
    sid = input('请输入学生身份证号码:')
    phonenumber = input('请输入学生电话号码:')
    sql = "UPDATE students SET id=%s,name=%s,gender=%s,sid=%s,phonenumber=%s,birthday=%s where id=%s;"
    values = (id,name,gender,sid,phonenumber,sid[6:14:1],id)
    a = db.execute(sql, values)
    if a == 1:
        print('更新成功')
    else:
        print('更新失败')




while True:
    getUi()
    opt = input('请输入操作码：')
    db = PDBC(**db_connection_prop)
    if opt == '1':
        addStudent()
    elif opt == '2':
        showStuList()
    elif opt == '3':
        showStuData()
    elif opt == '4':
        delStudent()
    elif opt == '5':
        updateStudent()
    else:
        break