Python连接MySQL数据库并从EXCLE读取文件实现增删改查

import pymysql
import xlrd
import xlsxwriter

conn = pymysql.connect('localhost', 'root', '123456', 'student')


class AddressBook:
    def selectInfo(self):
        cursor = conn.cursor()  # 使用Cursor()方法获取操作游标
        result = cursor.execute('select * from address')
        print("编号", '姓名', '电话', '地址')
        result = cursor.fetchall()
        for row in result:
            id = row[0]
            name = row[1]
            tel = row[2]
            add = row[3]
            print(id, name, tel, add)
        cursor.close()

    def insertInfo(self):
        cursor = conn.cursor()
        id = input('请输入编号：')
        name = input('请输入姓名：')
        tel = input('请输入电话：')
        add = input('请输入地址：')
        try:
            mysql = 'insert into address(编号,姓名,电话,地址)values(%s,%s,%s,%s)'
            cursor.execute(mysql, (id, name, tel, add))
        except:
            conn.rollback()
        cursor.close()

    def deleteInfo(self):
        cursor = conn.cursor()
        id = input('请输入要删除的编号')
        try:
            cursor.execute('delete from address where 编号=%s', (id.strip()))
            print('删除成功!')
            conn.commit()
        except:
            conn.rollback()
        cursor.close()

    def updateInfo(self):
        cursor = conn.cursor()
        id = input('请输入要修改的编号：')
        upName = input('请输入要修改的项目：')
        upInfo = input('请输入要修改的值：')
        try:
            if upName == '编号':
                cursor.execute('update address set 编号=%s where 编号=%s', (upInfo.strip(), id.strip()))
            elif upName == '姓名':
                cursor.execute('update address set 姓名=%s where 编号=%s', (upInfo.strip(), id.strip()))
            elif upName == '电话':
                cursor.execute('update address set 电话=%s where 编号=%s', (upInfo.strip(), id.strip()))
            elif upName == '地址':
                cursor.execute('update address set 地址=%s where 编号=%s', (upInfo.strip(), id.strip()))
            conn.commit()
        except:
            conn.rollback()
        cursor.close()


def readExcel():
    in_work = xlrd.open_workbook('../text/Address.xlsx')
    worksheet = in_work.sheet_by_index(0)
    ls = []
    for i in range(worksheet.nrows):
        ls.append(worksheet.row_values(i))
    return ls


def writeSql(ls):
    cursor = conn.cursor()
    delsql = 'drop table if exists address'
    cursor.execute(delsql)
    cursor.execute('''
    create table address(编号 varchar(100) primary key not null,
    姓名 text not null,
    电话 varchar(100) not null,
    地址 varchar(100) not null)
    ''')
    for i in range(len(ls) - 1):
        sqlstr = '''insert into address(编号,姓名,电话,地址)values(%s,%s,%s,%s)'''
        # ls[i+1]从第二行开始
        parm = (ls[i + 1][0], ls[i + 1][1], ls[i + 1][2], ls[i + 1][3])
        cursor.execute(sqlstr, parm)
    conn.commit()
    cursor.close()
    print('存入数据库完成')


def main():
    ls = readExcel()
    writeSql(ls)
    while True:
        print('[1查找]', end=',')
        print('[2增加]', end=',')
        print('[3修改]', end=',')
        print('[4删除]', end=',')
        choice = int(input('请输入你的选择(0-4)\n'))
        if choice < 0 or choice > 4:
            break
            # 查找 重写父类方法
        if choice == 1:
            select = AddressBook()
            select.selectInfo()
            # 增加
        if choice == 2:
            insert = AddressBook()
            insert.insertInfo()
            # 修改
        if choice == 3:
            update = AddressBook()
            update.updateInfo()
            # 删除
        if choice == 4:
            delete = AddressBook()
            delete.deleteInfo()


if __name__ == '__main__':
    main()
    conn.close()