day32
总结
-
Python程序接入MySQL数据库 ~ 三方库: - mysqlclient ---> C ---> 有可能会因为底层C语言的缺失而失败 ---> import MySQLdb - pymysql ---> Python ---> 安装一定会成功 ---> import pymysql except 指定的一场类型是父类型,那么可以捕获到子类型的异常 因为异常捕获需要遵循面向对象编程的里氏替换原则 (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: PyCharm pip config set global.Index-url https://pypi.doubanio.com/simple pip 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 pymysql import openpyxl wb = openpyxl.Workbook() sheet = wb.active sheet.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 openpyxl import pymysql conn = 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 openpyxl import pymysql wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx') ws = wb.active params = [] 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: PyCharm excel 转sql """ import pymysql import openpyxl wb = openpyxl.load_workbook(r'./file/阿里巴巴2020年股票数据.xlsx') ws = wb.active row_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: PyCharm csv 转 sql """ import pymysql import csv f = 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()