day6

#操作数据库，redis，excel，发邮件，异常处理


# #sys模块
# import sys
# print(sys.argv)   #用来获取运行python文件的时候，传过来的参数
# #举例 my_pip.py
#
# #结束当前的python文件
# exit()
# exit('结束')
# quit()

# 异常处理
# try :
# except

# try:
# #    '1'+ 2
#     2/0
# except TypeError as e:
#     print(e)
#     print('int类型')
# except ZeroDivisionError as e:
#     print('除数不能为零')
# except IndentationError:
#     print('下标错误')
# except KeyError:
#     print('知不道字典的key')
# except FileNotFoundError:
#     print('文件打不开'
# else:
#     print('没有出现异常')
# 扑捉到所有的异常
# try:
#     '1'+'2'
# except Exception as e:
#     print('出异常了',e)
# else:
#     print('没有出现异常')
#
# #finally 的使用,
#
# f=open('xxx','w')
# try:
#     f.write()
# except:
#     print('出错了')
# else：
#     print('xxx')
# finally:
#     f.close()

#堆栈信息
# import traceback
#
# def is_price(s):
#     try:
#         f=float(s)
#     except Exception as e:
#         print('自己打印的报错信息。。。。。。',traceback.format_exc())
#         return False
#     else:
#         if f>0:
#             return True
#
# is_price('a')

#????????????????????????怎么查看日志文件？

#发送邮件
#安装模块pip install yamail
#邮箱开通授权码：设置- 账号 - 开启服务 POP/SMTP服务  获取授权码obhhmywmkdbcbhfd
#
# import yamail
# smtp=yamail.SMTP(
#             host='smtp.qq.com',#改成自己邮箱的邮箱服务器即可
#             user='251202767@qq.com',
#             password='obhhmywmkdbcbhfd') #password 是163\qq邮箱的授权码，如果是公司邮件，一般是输入密码
#
# smtp.send(to='xianfeng1224@126.com',#发送给谁
#           subject='你好，请查收邮件',#标题
#           cc='150407001@qq.com',#抄送，如果是抄送给多个人，写成list
#           contents='邮件正文',#邮件正文
#           attachments='a.txt' #附件，如果有多个附件，写成list
#           )
#
# smtp.close()


#操作excel
#安装下列模块
#pip install xlrd
#pip install xlwt
#pip install xlutils

#写excel
import xlwt

# book = xlwt.Workbook()
# sheet = book.add_sheet('students')
#
# sheet.write(0,0,'id')
# sheet.write(0,1,'name')
# sheet.write(0,2,'age')
#
# sheet.write(1,0,'1')
# sheet.write(1,1,'xiaohei')
# sheet.write(1,2,'38')

# book.save('students.xls')#如果后缀写成xlsx，使用微软的office打不开，使用wps才能打开

#编号、姓名、地址、年龄
# stus=[
#     [1,'ds','beijing',51],
#     [2,'fd','shanghai','28'],
#     [3,'ljj','shanghai','45'],
#     [4,'lhy','shanghai','21'],
#     [5,'ylm','beijing','25'],
#     [6,'wxl','beijing','34']
# ]

# stus=[
#     [1,'ds','beijing',51],
#     [2,'fd','shanghai','28'],
#     [3,'ljj','shanghai','45'],
#     [4,'lhy','shanghai','21'],
#     [5,'ylm','beijing','25'],
#     [6,'wxl','beijing','34']
# ]
# stus.insert(0,['编号','姓名','地址','年龄'])
# book = xlwt.Workbook()
# sheet = book.add_sheet('students1')

# row=0
# for stu in stus:#控制行
#     col=0
#     for s in stu:#控制列
#         sheet.write(row,col,s)
#         col+=1
#     row+=1

# for row,stu in enumerate(stus):#控制行
#     for col,s in enumerate(stu):#控制列
#         sheet.write(row,col,s)
#
# ages=[s[-1]for s in stus if type(s[-1])!=str]
# avg_age=round(sum(ages)/len(ages),2)
# content='平均年龄：%s'%avg_age
# sheet.write(row+1,0,content)
#
# book.save('students1.xls')

#读取excel
# import xlrd
# book = xlrd.open_workbook('students1.xls')
#
# #sheet=book.sheet_by_index(0)
# sheet=book.sheet_by_name('students1')
# print(book.sheets())#所有的sheet页，返回的是一个list，list里面就是每个sheet对象
#
# for s in book.sheets():
#     print(s.row_values(2))
#
# print(sheet.cell(0,0).value)
#
# print(sheet.row_values(0))
#
# print(sheet.col_values(1))
#
# print(sheet.nrows)#多少行
# print(sheet.ncols)#多少列

#修改excel
# from xlutils import copy
# import xlrd
#
# book=xlrd.open_workbook('students1.xls')
# sheet=book.sheet_by_index(0)
#
# new_book=copy.copy(book)
# copy_sheet=new_book.get_sheet(0)
#
# for row in range(1,sheet.nrows-1):#1234567
#     addr=sheet.cell(row,2).value
#     addr=addr.replace('beijing','北京').replace('shanghai','上海')
#     copy_sheet.write(row,2,addr)
#
# new_book.save('students1.xls')


#mysql操作

#操作数据库需要：IP，username，password，db数据库名称


import pymysql

host='118.24.3.40'
user='jxz'
password='123456'#字符串
db='jxz'
port=3306#int类型

connect=pymysql.connect(host=host,user=user,
                        password=password,
                        port=port,db=db,
                        autocommit=True)#连接数据库,autocommit=True自动提交
#建立游标,指定游标类型：pymysql.cursors.DictCursor，取值元素以字典的方式显示，如果为空（），取值元素以list的方式显示
cur=connect.cursor(pymysql.cursors.DictCursor)

#查询
cur.execute('select * from students;')
print(cur.description)#表里字段名的信息
result=cur.fetchall()#返回二维数组，即返回多个记录(rows),如果没有结果 则返回 ()
print(result)
print(cur.fetchone())
print(cur.fetchmany(2))
for data in cur:
    print(data)
#插入
# cur.execute('insert into students values (39,"wl","nv",38,"天马座","广州");')
# cur.execute('insert into students (name,class) values ("wll","天马座");')
# 修改
# cur.execute('update students set name="王石" where id=39;')
# 删除
# cur.execute('delete from students where id=38;')
#
# connect.commit()#提交（连接数据库的时候可以设置自动提交）
# connect.rollback()#回滚,没提交之前才可以回滚
cur.close()
connect.close()