day32_创建Mysql账户，以及python *** 作Mysql

概述day32总结Python程序接入MySQL数据库~三方库:-mysqlclient--->C--->有可能会因为底层C语言的缺失而失败--->importMySQLdb-pymysql--->Python--->安装一定会成功--->importpymysqlexcept指定的一场类型是父类型，那么可以捕获到

day32

总结

Python程序接入MysqL数据库    ~ 三方库:        - MysqLclIEnt ---> C ---> 有可能会因为底层C语言的缺失而失败 ---> import MysqLdb        - pyMysqL ---> Python ---> 安装一定会成功 ---> import pyMysqLexcept 指定的一场类型是父类型，那么可以捕获到子类型的异常因为异常捕获需要遵循面向对象编程的里氏替换原则 （LSP）

创建MysqL账户,并授权以及回收权限

create user 'guest'@'%'IDentifIEd by 'Guest123';grant create, alter, insert, update, select on hrs.* to 'guest'@'%' with grant option;grant create, alter, insert, update, select on school.* to 'guest'@'%' with grant option;grant all privileges on school.* to 'guest'@'%' with grant option;grant all privileges on hrs.* to 'guest'@'%' with grant option;grant drop on hrs.* to 'guest'@'%';grant drop on school.* to 'guest'@'%';grant drop on crawp.* to 'guest'@'%';grant create on *.* to 'guest'@'%' with grant option;revoke create on *.* from 'guest'@'%';create database if not exists crawp default charset utf8mb4;grant all privileges on crawp.* to 'guest'@'%' with grant option;

查看MysqL执行计划

use hrs;explain select eno, ename, job from tb_emp where eno=7800;explain select eno, ename, job from tb_emp where ename='张三丰';create index as IDx_ename on hrs.tb_emp (ename);

python连接数据库

insert,update,delete

"""!./env python-*- Coding: utf-8 -*-@Time:  2021/6/10 10:44@Author:    三玖天下第一@file: example01.py@Software: PyCharmpip config set global.Index-url https://pypi.doubanio.com/simplepip install pyMysqL向关系型数据库的二维表中插入数据"""import pyMysqL# 第一步: 建立数据库连接# host - 主机，确定连接哪一台服务器， port - 端口,确定服务器上的端口# user - 用户名,  password - 密码, （提示:强烈建议不用root)# database和charset - 连接数据库和使用的字符集# autocommit - 自动提交conn = pyMysqL.connect(host='10.7.174.55', port=3306, user='guest',                       password='Guest123', database='hrs',                       charset='utf8mb4', autocommit=False)try:    try:        print('添加部门')        no = int(input('请输入编号:'))        name = input('请输入部门名称:')        location = input('请输入部门位置:')        # 第二步：获取游标对象        with conn.cursor() as cursor:            # 第三步：通过游标想数据库服务器发出SQL语句，获取执行结果            affected_rows = cursor.execute(                'insert into tb_dept(dno, dname, dloc) values (%s, %s, %s)',                (no, name, location)            )            # if conn.affected_rows() == 1:            if affected_rows == 1:                print('添加部门成功')            # 第四步：提交上面的 *** 作        conn.commit()    except pyMysqL.MysqLError as err:        print('添加失败', f'错误提示:{err}')        # 第四步： 回滚（ *** 作失败）        conn.rollback()    try:        no = int(input('请输入要删除部门的编号:'))        with conn.cursor() as cursor:            affected_rows = cursor.execute(                'delete from tb_dept where dno=%s',                no            )            if conn.affected_rows() == 1:                print(f'删除部门{no}成功...')        conn.commit()    except pyMysqL.MysqLError as err:        print('删除失败', f'错误提示{err}')        conn.rollback()    try:        no = int(input('请输入要修改部门的编号:'))        name = input('请输入修改后的部门名称:')        location = input('请输入修改后的部门位置:')        with conn.cursor() as cursor:            affected_rows = cursor.execute(                'update tb_dept set dname=%s,dloc=%s where dno=%s',                (name, location, no)            )            if affected_rows == 1:                print(f'更新部门{no}成功...')        conn.commit()    except pyMysqL.MysqLError as err:        print('更新失败', f'错误提示{err}')        conn.rollback()finally:    # 第五步：关闭连接    conn.close()

select

"""!./env python-*- Coding: utf-8 -*-@Time:  2021/6/10 14:06@Author:    三玖天下第一@file: example02.py@Software: PyCharm"""import pyMysqLimport openpyxlwb = openpyxl.Workbook()sheet = wb.activesheet.Title = '部门信息'sheet.append(['员工编号', '姓名', '职位', '隶属', '工资', '补贴', '部门名称', '部门所在位置', '部门编号'])# 第一步: 连接到数据库conn = pyMysqL.Connect(host='127.0.0.1', port=3306,                       user='guest', password='Guest123',                       database='hrs', charset='utf8mb4')try:    try:        # 第二步: 获取游标对象        # 可设置读取数据是字典还是元组        # with conn.cursor(cursor=pyMysqL.cursors.DictCursor) as cursor:        with conn.cursor() as cursor:            # 第三步: 通过游标执行sql            cursor.execute(                '''SELECT t1.eno as 员工编号, t1.ename 姓名, t1.job 职位, t3.ename 隶属, t1.sal 工资, t1.comm 补贴,                 dname 部门名称, dloc 部门所在位置, t1.dno 部门编号                FROM hrs.tb_emp t1 inner join hrs.tb_dept t2 on t1.dno=t2.dno                left join hrs.tb_emp t3 on t1.mgr=t3.eno'''            )            # 第四步: 通过游标获取数据            # cursor.fetchall() # 获取全部数据            # print(cursor.fetchone()) # 获取一行数据            # print(cursor.fetchmany(100))  # 获取100行数据            # 提示: 如果数据体量太大,fetchall最好不用,否则可能内存不足而崩溃,而且加载数据的时间也会特别长            # 迭代器            # for row in iter(lambda: cursor.fetchmany(2), []):            #     print(row)            for row in cursor:                sheet.append(row)    except pyMysqL.MysqLError as err:        print(err)finally:    wb.save(r'./file/hrs.xlsx')    wb.close()    conn.close()

sql数据转excel

"""从数据库中读取员工的编号、姓名、职位、月薪和部门名称，写入Excel文件"""import openpyxlimport pyMysqLconn = pyMysqL.connect(host='10.7.174.103', port=3306,                       user='guest', password='Guest.618',                       database='hrs', charset='utf8mb4')try:    with conn.cursor() as cursor:        cursor.execute(            'select eno, ename, job, sal, dname from tb_emp t1 '            'inner join tb_dept t2 on t1.dno=t2.dno'        )        wb = openpyxl.Workbook()        # ws = wb.create_sheet('员工表')        ws = wb.active        Titles = ('工号', '姓名', '职位', '月薪', '部门')        # openpyxl *** 作Excel时，行和列的索引都是从1开始的        for col_IDx, col_name in enumerate(Titles):            ws.cell(1, col_IDx + 1, col_name)        for row_IDx, emp_row in enumerate(cursor.fetchall()):            for col_IDx, col_value in enumerate(emp_row):                ws.cell(row_IDx + 2, col_IDx + 1, col_value)        wb.save('人力资源管理.xlsx')except pyMysqL.MysqLError as err:    print(err)finally:    conn.close()

excel转sql数据

"""从Excel文件中读取数据写入数据库create database stock default character set utf8mb4;use stock;create table tb_baba_stock(stock_ID bigint unsigned auto_increment comment '编号',Trade_date date not null comment '交易日',high_price decimal(12, 4) not null comment '最高价',low_price decimal(12, 4) not null comment '最低价',open_price decimal(12, 4) not null comment '开盘价',close_price decimal(12, 4) not null comment '收盘价',Trade_volume bigint unsigned not null comment '交易量',primary key (stock_ID));"""import openpyxlimport pyMysqLwb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')ws = wb.activeparams = []for row_IDx in range(2, ws.max_row + 1):    values = []    for col_IDx in range(1, ws.max_column):        values.append(ws.cell(row_IDx, col_IDx).value)    params.append(values)conn = pyMysqL.connect(host='10.7.174.103', port=3306,                       user='guest', password='Guest.618',                       database='stock', charset='utf8mb4')try:    with conn.cursor() as cursor:        # 执行批量插入 *** 作        cursor.executemany(            'insert into tb_baba_stock '            '   (Trade_date, high_price, low_price, open_price, close_price, Trade_volume) '            'values '            '   (%s, %s, %s, %s, %s, %s)',            params        )    conn.commit()except pyMysqL.MysqLError as err:    print(err)    conn.rollback()finally:    conn.close()

example

"""!./env python-*- Coding: utf-8 -*-@Time:  2021/6/10 15:26@Author:    三玖天下第一@file: example_insert.py@Software: PyCharmexcel 转sql"""import pyMysqLimport openpyxlwb = openpyxl.load_workbook(r'./file/阿里巴巴2020年股票数据.xlsx')ws = wb.activerow_iter = ws.iter_rows()conn = pyMysqL.Connect(host='127.0.0.1', port=3306,                       user='guest', password='Guest123',                       database='crawp', charset='utf8mb4')def insertmany():    try:        with conn.cursor() as cursor:            next(row_iter)            ws_data = []            def execute_sql():                cursor.executemany(                    '''insert into tb_alIData(ali_date, ali_hight, ali_low, ali_open, ali_close, ali_volume, ali_adj_close) values(%s,%s,%s,%s,%s,%s,%s)''',                    ws_data                )            index = 0            for row_data in row_iter:                index += 1                ws_data.append([cell.value for cell in row_data])                if index >= 100:                    index = 0                    execute_sql()                    ws_data.clear()            execute_sql()        conn.commit()    except pyMysqL.MysqLError as err:        print(err)        conn.rollback()def insert1():    try:        with conn.cursor() as cursor:            Titles = next(row_iter)            for row_data in row_iter:                row_values = [cell.value for cell in row_data]                cursor.execute(                    '''insert into tb_alIData(ali_date, ali_hight, ali_low, ali_open, ali_close, ali_volume, ali_adj_close) values(%s,%s,%s,%s,%s,%s,%s)''',                    row_values                )        conn.commit()    except pyMysqL.MysqLError as err:        print(err)        conn.rollback()if __name__ == '__main__':    try:        insertmany()    finally:        conn.close()

"""!./env python-*- Coding: utf-8 -*-@Time:  2021/6/10 18:32@Author:    三玖天下第一@file: example_insert_csv.py@Software: PyCharmcsv 转 sql"""import pyMysqLimport csvf = open(r'./file/jd2.csv', 'r', newline='', enCoding='utf-8')reader = csv.reader(f)conn = pyMysqL.Connect(host='127.0.0.1', port=3306,                       user='guest', password='Guest123',                       database='crawp', charset='utf8mb4')def insertmany():    try:        with conn.cursor() as cursor:            def execute_sql(data):                cursor.executemany(                    '''insert into tb_nb_computer(Title, detail, img, price, comment_num, comment_url, business, Tags) values(%s,%s,%s,%s,%s,%s,%s,%s)''',                    data                )            next(reader)            temp_data = []            index = 0            for row_data in reader:                temp_data.append(row_data)                if index >= 1:                    index = 0                    execute_sql(temp_data)                    temp_data.clear()            if temp_data:                execute_sql(temp_data)        conn.commit()    except pyMysqL.MysqLError as err:        print(err)        conn.rollback()if __name__ == '__main__':    try:        insertmany()    finally:        f.close()        conn.close()

---

总结

以上是内存溢出为你收集整理的day32_创建Mysql账户，以及python *** 作Mysql全部内容，希望文章能够帮你解决day32_创建Mysql账户，以及python *** 作Mysql所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。