import pymysql
no = int(input('部门编号:'))
name = input('部门名称:')
location = input('部门所在地:')
conn = pymysql.connect(
host='10.7.174.56', port=3306, user='guest', password='Guest.618', database='hrs', charset='utf8mb4'
)
try:
with conn.cursor() as cursor:
affected_rows = cursor.execute(
'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',
(no, name, location)
)
if affected_rows == 1:
print('添加部门成功')
conn.commit()
except pymysql.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
import pymysql
no = int(input('部门编号: '))
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:
affected_rows = cursor.execute(
'delete from tb_dept where dno=%s',
(no, )
)
if affected_rows == 1:
print('删除部门成功!')
conn.commit()
except pymysql.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
import pymysql
no = int(input('部门编号: '))
name = input('部门名称: ')
location = input('部门所在地: ')
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:
affected_rows = cursor.execute(
'update tb_dept set dname=%s, dloc=%s where dno=%s',
(name, location, no)
)
if affected_rows == 1:
print('更新部门信息成功!')
conn.commit()
except pymysql.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
import pymysql
"""
修改数据
"""
conn = pymysql.connect(host='10.7.174.56', port=3306, user='guest', password='Guest.618', database='hrs',
charset='utf8mb4')
try:
with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
cursor.execute(
'select dno as no, dname as name, dloc as location from tb_dept'
)
for row in iter(lambda: cursor.fetchone(), None):
print(row)
except pymysql.MySQLError as err:
print(err)
finally:
conn.close()
- 从数据库中读取员工的编号、姓名、职位、月薪和部门名称,写入excel文件
import pymysql
import openpyxl
conn = pymysql.connect(host='10.7.174.56', 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.active
titles = ('工号', '姓名', '职位', '月薪', '部门')
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()
import pymysql
import openpyxl
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.56', 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()