"""
查询操作
"""
import MySQLdb
from MySQLdb.cursors import DictCursor
conn = MySQLdb.connect(host='', port=,
user='root', password='',
database='', charset='utf8mb4')
try:
with conn.cursor(cursorclass=DictCursor) as cursor:
cursor.execute('select dno as no, dname as name, dloc as location from tb_dept')
for dept in cursor.fetchall():
"""
fetchall 是抓取全部
fetchone 是抓取一个
fetchmany(N) 是抓取N个
"""
print(f'{dept["name"]}\t{dept["location"]}')
except MySQLdb.MySQLError as err:
print(err)
finally:
conn.close()
"""
更新数据
"""
import MySQLdb
no = int(input('编号:'))
location = input('所在地:')
"""
修改某个编号的部门的所在地
"""
conn = MySQLdb.connect(host='', port=,
user='', password='',
database='', charset='utf8')
try:
with conn.cursor() as cursor:
affected_rows = cursor.execute(
'update tb_dept set dloc=%s where dno=%s',
(location, no)
)
if affected_rows == 1:
print('更新部门成功')
conn.commit()
except MySQLdb.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
"""
pip config set global.index-url https://pypi.douban.com/simple
pip install mysqlclient
pip install pymysql
pip uninstall -y pymysql
pip list
pip freeze > requirements.txt
pip install -r requirements.txt
pip check
SQL注射攻击(SQL Injection)
"""
import MySQLdb
no = int(input('编号:'))
name = input('名称:')
location = input('所在地:')
conn = MySQLdb.connect(host='', port=,
user='', password='',
database='', charset='utf8mb4')
try:
with conn.cursor() as cursor:
affected_rows = cursor.execute(
'insert into tb_dept values (%s, %s, %s)',
(no, name, location)
)
if affected_rows == 1:
print('新增部门成功')
conn.commit()
except MySQLdb.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
"""
将查询到的数据保存为execl表(xls)
读取数据写入Excel文件
一个Excel文件 ---> 工作簿 ---> workbook
一个工作簿下面可以有一个或多个工作表 ---> sheet
"""
import xlwt
import MySQLdb
conn = MySQLdb.connect(host='', port=,
user='', password='',
database='', 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 = xlwt.Workbook()
sheet = wb.add_sheet('员工信息表')
titles = ('工号', '姓名', '职位', '月薪', '部门')
title_style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '行楷-简'
font.height = 400
font.colour_index = 4
font.bold = True
title_style.font = font
aligment = xlwt.Alignment()
aligment.horz = xlwt.Alignment.HORZ_CENTER
aligment.vert = xlwt.Alignment.VERT_CENTER
title_style.alignment = aligment
for col_index, title in enumerate(titles):
sheet.write(0, col_index, title)
for row_index, row in enumerate(cursor.fetchall()):
for col_index, value in enumerate(row):
sheet.write(row_index + 1, col_index, value)
wb.save('hrs.xls')
"""
保存到指定地址在文件名前面加绝对路径!
"""
except MySQLdb.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()
import xlrd
wb = xlrd.open_workbook('阿里巴巴2017年股票数据.xlsx')
sheetname = wb.sheet_names()[0]
sheet = wb.sheet_by_name(sheetname)
print(sheet.nrows, sheet.ncols)
for row in range(sheet.nrows):
for col in range(sheet.ncols):
value = sheet.cell(row, col).value
if row > 0:
if col == 0:
value = xlrd.xldate_as_tuple(value, 0)
value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'
else:
value = f'{value:.2f}'
print(value, end='\t')
print()