Python连接mysql数据

需要先安装Python连接mysql的连接中间件pymysql
使用命令安装：

pip install pymysql

以下是实现Python连接mysql数据库，进行CRUD的的简单操作方法：

import pymysql


def connect():
    # 获取数据库连接对象
    # conn = pymysql.connect(host='localhost', port=3306,
    #                        user='root', passwd='root',
    #                        database='hrs', charset='utf8')

    conn = pymysql.connect(host='localhost', port=3306,
                           user='root', password='root',
                           database='hrs', charset='utf8',
                           cursorclass=pymysql.cursors.DictCursor)
    '''
    cursorclass=pymysql.cursors.DictCursor
    表示设置游标类型为字典
    '''
    return conn


# 添加
def inset():
    conn = connect()
    try:
        with conn.cursor() as coursor:
            result = coursor.execute('insert into tb_dept values (60, "销售部", "成都")')
            if result == 1:
                print("添加成功")
                conn.commit()
    except pymysql.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()
    print(conn)


# 删除
def delete(no):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            result = cursor.execute('delete from tb_dept where dno=%s', (no,))
            if result == 1:
                print("删除成功")
                conn.commit()
    except pymysql.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()


# 更新
def update(loc, no):
    conn = connect()
    try:
        with conn.cursor() as cursor:
            result = cursor.execute("update tb_dept set dloc=%s where dno=%s", (loc, no))
            if result == 1:
                print("更新成功")
                conn.commit()
    except pymysql.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()


# 查询
def query():
    conn = connect()
    try:
        with conn.cursor() as cursor:
            cursor.execute("select dno,dname,dloc from tb_dept")  # 返回符合的数据条数
            #显示查询到的所有数据，返回值为一个元组
            result = cursor.fetchall()
            # 显示查询到的一条数据
            # cursor.fetchone()
            # # 显示查询到的n条数据
            # cursor.fetchmany(n)
            print(result)
            for row in result:
                # print('部门编号：{}'.format(row[0]))
                print(f'部门编号：{row[0]}')
                print(f'部门名称：{row[1]}')
                print(f'部门地址：{row[2]}')
                print('---'*20)

    except pymysql.MySQLError as err:
        print(err)
    finally:
        conn.close()


#  实现返回值为字典
def query1():
    conn = connect()
    try:
        with conn.cursor() as cursor:
            cursor.execute("select dno,dname,dloc from tb_dept")  # 返回符合的数据条数
            # 设置游标类型为字典时调用fetchall方法返回值为一个列表嵌套字典
            result = cursor.fetchall()
            print(result)
            for row in result:
                print(f"部门编号：{row['dno']}")
                print(f"部门名称：{row['dname']}")
                print(f"部门地址：{row['dloc']}")
                print("-"*20)
    except pymysql.MySQLError as err:
        print(err)
    finally:
        conn.close()


# ############
class Dept(object):
    def __init__(self, dno, dname, dloc):
        self.dno = dno
        self.dname = dname
        self.dloc = dloc

    def __str__(self):
        return f'{self.dno}\t{self.dname}\t{self.dloc}'


def query2():
    conn = connect()
    try:
        with conn.cursor() as cursor:
            cursor.execute("select dno,dname,dloc from tb_dept")  # 返回符合的数据条数
            # 设置游标类型为字典时调用fetchall方法返回值为一个列表嵌套字典
            result = cursor.fetchall()
            print(result)
            for row in result:
                # 定义一个类封装数据显示的格式，for循环将列表中的每个字典取出放在row中
                # 将row以关键字参数方式传入给定义的类，此时会自动解包将row字典的值拆分出来传入dept类，然后打印出来
                # 使用此方法时 定义的类中传入的参数名称要和数据库字段一致，
                # 如果查询时自定义了字段名称需要和自定义名称保持一致
                # 否则会报字段不匹配的错误
                dept = Dept(**row)  # 将字典传入时会自动解包
                print(dept)
    except pymysql.MySQLError as err:
        print(err)
    finally:
        conn.close()


if __name__ == '__main__':
    # inset()

    # no = input("请输入要删除的部门编号：")
    # delete(no)
    # no = input("请输入需要修改的部门编号：")
    # loc = input("请输入需要修改的地区名称：")
    # update(loc, no)
    # query1()
    query2()