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()
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
