python连接数据库进行增删查改
添加表数据
import pymysql
no = input("部门编号:")
name = input("部门名称:")
location = input("部门所在地:")
# 1.创建连接--->connection
conn = pymysql.connect(host='ip或localhost', port=3306, user='yangjiao_copy'
, password='xxxxx', database='hrs', charset='utf8mb4')
# print(conn)
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3.向数据库发出sql语句
# %s安全占位符,无论什么类型
a_row = cursor.execute(
'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s)',
(no,name,location)
)
if a_row ==1:
print('添加成功')
# 4.提交事务
conn.commit()
except pymysql.MySQLError as err:
print(err)
#5.回滚
conn.rollback()
finally:
conn.close()
删除表数据
# 删除数据
conn = pymysql.connect(host='ip', port=3306, user='yangjiao_copy'
, password='xxxxxx', database='hrs', charset='utf8mb4')
# print(conn)
no_d = input('删除部门编号:')
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3.向数据库发出sql语句
# %s安全占位符,无论什么类型
a_row = cursor.execute(
'delete from tb_dept where `dno`=%s',
(no_d,)
)
if a_row == 1:
print('删除成功')
# 4.提交事务
conn.commit()
except pymysql.MySQLError as err:
print(err)
# 5.回滚
conn.rollback()
finally:
conn.close()
当删除的部门出现如下提示:(1217, ‘Cannot delete or update a parent row: a foreign key constraint fails’)
其原因是因为,在创建该表时references后默认是restrict,做了外键约束,不能删除,但如果是cascade的话就可以删除和更新,同时与之相关联的数据页删除和修改
更新数据:
conn = pymysql.connect(host='ip', port=3306, user='yangjiao_copy'
, password='xxxxx', database='hrs', charset='utf8mb4')
# print(conn)
no = input("部门编号:")
name = input("部门名称:")
location = input("部门所在地:")
try:
# 2. 获取游标对象(Cursor)
with conn.cursor() as cursor:
# 3.向数据库发出sql语句
# %s安全占位符,无论什么类型
a_row = cursor.execute(
# 'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s)',
# (no, name, location)
'update tb_dept set dloc = %s,dname = %s where dno = %s',
(location,name,no)
)
if a_row == 1:
print('更新成功')
# 4.提交事务
conn.commit()
except pymysql.MySQLError as err:
print(err)
# 5.回滚
conn.rollback()
finally:
conn.close()
查询数据:
from pymysql.cursors import Cursor
conn = pymysql.connect(host='ip',password='xxxxx6'
,port=3306,user='yangjiao_copy'
,database = 'hrs',charset='utf8mb4')
try:
# ymysql.cursors.DictCursor使得查询出来的数据是字典
with conn.cursor(ymysql.cursors.DictCursor) as cursor: #type:pymysql
cursor.execute(
'select dno,dname,dloc from tb_dept'
)
# 通过游标抓取数据
print(cursor.fetchall())
except pymysql.MySQLError as err:
print(err)
finally:
conn.close()
显示如下:
将mysql数据写入Excel
import pymysql
import openpyxl
from openpyxl.worksheet.worksheet import Worksheet #加上有代码提示
from pymysql.cursors import Cursor #加上有代码提示
'''
# 创建工作部
wb = openpyxl.Workbook()
# 获取默认的工作表
sheet = wb.active #type: Worksheet
# 给默认表改名字
sheet.title = '员工'
# 创建新工作表
# sheet = wb.create_sheet('员工')
# 添加表头
sheet.append(('编号','姓名','职位','月薪','补贴','所在部门'))
conn = pymysql.connect(host='ip',password='ccccc'
,port=3306,user='yangjiao_copy'
,database = 'hrs',charset='utf8mb4')
try:
with conn.cursor() as cursor: #type:pymysql
cursor.execute(
'select eno,ename,job,sal,comm,dname'
' from tb_emp natural join tb_dept'
)
while row := cursor.fetchone():
sheet.append(row)
except pymysql.MySQLError as err:
print(err)
finally:
wb.save('员工信息表.xlsx')
conn.close()
将Excel写入mysql
import openpyxl
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.workbook import Workbook
import pymysql
from pymysql.cursors import Cursor
conn = pymysql.connect(host='ip地址或者localhost', password='xxxx'
, port=3306, user='yangjiao_copy'
, database='hrs', charset='utf8mb4')
wb = openpyxl.load_workbook('人力资源信息表.xlsx') #type:Workbook
sheet1 = wb['部门'] #type:Worksheet# 标记不要写成workbook
sheet2 = wb['员工'] #type:Worksheet
rows_iter1 = sheet1.iter_rows()
rows_iter2 = sheet2.iter_rows()
next(rows_iter2)
next(rows_iter1)
try:
with conn.cursor() as cursor: # type:pymysql
# 对表单的头进行迭代
for row1 in rows_iter1:
params = []
for cell in row1: # type:Cell
params.append(cell.value)
cursor.execute(
'insert into tb_dept (dno,dname,dloc) values (%s,%s,%s) ',
params
)
conn.commit()
except pymysql.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
to tb_dept (dno,dname,dloc) values (%s,%s,%s) ',
params
)
conn.commit()
except pymysql.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()