Viii. python operations Excel and network programming and exception handling

One, Python operations Excel1, read Excel, xlrd module for reading Excel

# book = Xlrd.open_workbook (R ' students.xlsx ')
#打开excel
# Print (Book.sheet_names ())
#获取所有sheet的名字
# sheet = book.sheet_by_index (0)
#根据sheet页的位置去取sheet
# Sheet2 = book.sheet_by_name (' Sheet2 ')
#根据sheet页的名字获取sheet页
# print (sheet.nrows) #获取sheet页里面的所有行数
# print (sheet.ncols) #获取sheet页里面的所有列数
# print (sheet.row_values (0))
#根据行号获取整行的数据
# print (sheet.col_values (0))
# #根据列获取整列的数据
# Print (Sheet.cell (). Value)
#cell方法是获取指定单元格的数据, preceded by rows, followed by columns
# lis = []
# for I in Range (1,sheet.nrows):
#     #i代表的是每一行 Because the first row is the table header, so start the loop directly from the second row
#     d = {}
#     id = Sheet.cell (i,0). value# line is not fixed, the column is fixed
#     name = Sheet.cell (i,1). Value
#     sex = Sheet.cell (i,2). Value
#     d[' id ']=int (ID)
#     d[' name ']=name
#     d[' sex ']=sex
#     Lis.append (d)
# Print (LIS)
#读excel的时候, XLS xlsx can be read

2, write Excel, XLWT module for reading Excel

Lis = [{' id ': 1, ' name ': ' xiaoming ', ' sex ': ' Male '},
{' id ': 2, ' name ': ' Little black ', ' sex ': ' Man '},
{' id ': 3, ' name ': ' Little Monster ', ' Sex ': ' Man '},
{' id ': 4, ' name ': ' Small white ', ' sex ': ' Female '}]
New_lis = [
[1, ' xiaoming ', ' Man '],
[2, ' xiaoming ', ' Man '],
[3, ' xiaoming ', ' Man '],
[4, ' xiaoming ', ' Man '],
[5, ' xiaoming ', ' Man '],
[6, ' xiaoming ', ' Man ']
]
title = [' Number ', ' name ', ' gender ']
# import XLWT
# book = XLWT. Workbook ()
# #新建一个excel对象
# sheet = book.add_sheet (' stu ')
# #添加一个sheet页
# # Sheet.write (0,0, ' number ')
# # Book.save (' Stu.xls ')
# for I in range (len (title)):
# #title多长, loop a few times
# Sheet.write (0,i,title[i])
# #i既是lis的下标, also on behalf of each column
# #处理表头
# #写excel的时候, the file name you saved must be XLS
# for row in range (len (LIS)):
# #取lis的长度, control the number of cycles
# id = lis[row][' id ')
# #因为lis里面存的是一个字典, Lis[row] represents every element in the dictionary, and then the dictionary takes
# #固定的key就可以了
# name = lis[row][' name ']
# sex = lis[row][' sex '
# New_row = row+1# because the loop starts at 0, the No. 0 row is the table header and cannot be written
# #要从第二行开始写, so here's the number of rows to add
# Sheet.write (New_row,0,id)
# Sheet.write (new_row,1,name)
# Sheet.write (new_row,2,sex)
#
# book.save (' New_stu.xls ')

3.xlutils Module

xlutils module is used to modify the contents of Excel, you can not directly modify the original Excel content, you must first copy a new Excel, and then modify the new Excel, use the following:

From xlutils.copy Import copy
Book = Xlrd.open_workbook (' New_stu.xls ')
#打开原来的excel
New_book = Copy (book)
#通过xlutils里面copy复制一个excel对象
Sheet = new_book.get_sheet (0)
#获取sheet页
Sheet.write (0,0, ' id ')
New_book.save (' New_stu_1.xls ')

Two, Python network programming

From urllib.request import Urlopen
From Urllib.parse import Urlencode,quote,quote_plus,unquote,unquote_plus
Import JSON
url = ' Http://python.nnzhp.cn/get_sites '
#quote把特殊字符变成url编码
Url2 = ' https://www.baidu.com/s?wd=sdfsdfsdf%3A%2F%3A%22&rsv_spt=1&rsv_iqid=0xaa8e074900029bd6&issp= 1&f=8&rsv_bp=1&rsv_idx=2&ie=utf-8&rqlang=cn&tn=baiduhome_pg&rsv_enter=0&inputt =5072&rsv_t=dcbeepyrzuynvhhzvklsb6mnuzdc0rsbeilp4crixem8r98nibni82yw4nl2mnkw%2fszm&rsv_sug3=28&oq= sdfsdfsdf%2520%2526lt%253b%25202%25202%2526lt%253b%25204%25202&rsv_pq=b4570bd80002e2e5&rsv_sug1=11 &rsv_sug7=100&rsv_sug2=0&rsv_sug4=5634 '
#unquote就是把url编码变成原来字符串
#url编码
# res = urlopen (URL). read (). Decode ()
#发送get请求
# new_res = json.loads (res)
#把返回的json转成python的数据类型
#urlopen (URL) This is to send a GET request

data = {
"username": "Hahahahahahah",
"Password": "123456",
"C_PASSWD": "123456"
}
param = urlencode (data). Encode ()
# Print (Urlopen (url2,param). Read (). Decode ())
#发post请求
#requests模块就是基于urllib模块开发的

Import requests
# requests.get (URL). text
#text方式返回的是字符串
# res = requests.get (URL). JSON ()
#返回的json


#post请求
Url_reg = ' Http://python.nnzhp.cn/reg?username=lhl&password ' \
' =123456&c_passwd=123456 '
# res = Requests.post (url_reg). JSON ()
# Print (Type (res), RES)
Url_set = ' http://python.nnzhp.cn/set_sties '
D = {
"Stie": "Hahfsdfsdf",
"url": "Http://www.nnzhp.cn"
}
# res = Requests.post (url_set,json=d). JSON ()
# Print (RES)
Cookie_url = ' http://python.nnzhp.cn/set_cookies '

data = {' username ': ' Little Monster ', ' Money ': 8888}
Cookie = {' token1111 ': ' Ajajja '}
res = Requests.post (Cookie_url,data=data,cookies=cookie). JSON () #使用cookies参数指定cookie
Print (RES)
# head_url = ' Http://api.nnzhp.cn/getuser2 '
# data = {' UserID ': 1}
# header = {' Content-type ': ' Application/json '}
# res = requests.post (URL, headers=header). JSON ()

Up_url = ' Http://python.nnzhp.cn/upload '
File = {' file_name ': Open (' aaa.py ')}
res = Requests.post (up_url,files=file). Text
Print (RES)

third, exception handling

info = {
"id": 1,
"Name": "Xiaobai",
"Sex": "Nan"
}
# # Chioce = input (' Please enter the attribute you want to see: ')
# try:
# 5/0
# except Exception as E:
# #这个exception can catch all the anomalies
# #python3
# #这个是出了异常的话, how to handle, E for exception information
# print (' The key you typed does not exist ', E)
# Else:
# #没有出异常的话, here we go
# print (' no exception, go here ')
# finally:
# #不管有没有出异常都会走
# print (' Here is finally ')
Import Pymysql
def opertionmysql (sql,port=3306,charset= ' UTF8 '):
Host = ' 211.149.218.16 '
user = ' Byz '
passwd = ' 123456 '
db = ' Bt_st '
Try
conn = Pymysql.connect (
Host=host,user=user,passwd=passwd,port=port,
Db=db,charset=charset
) #建立连接
Except Exception as E:
return {"Code": 308, "MSG": "Database Connection exception%s"%e}
cur = conn.cursor (cursor=pymysql.cursors.dictcursor)
#建立游标
Try
Is_correct_sql (SQL)
Cur.execute (SQL)
Except Exception as E:
return {"Code": 309, "MSG": "SQL Error! %s "%e}
Else
If Sql.startswith (' select '): # Determine what the statement is
res = Cur.fetchone ()
Else
Conn.commit ()
res = 88
return res
Finally
Cur.close ()
Conn.close ()
#执行sql

def is_float (s):
Try
Float (s)
Except Exception as E:
Return False
Return True

Class Sqlerr (Exception):
def __str__ (self):
Return ' SQL Exception hahahhahahfdsfsdflkj4k324324 '

def is_correct_sql (SQL):
#select
# Update Insert Delete truncate DROP create alter
Sql_start = [' select ', ' Update ', ' Insert ', ' delete ']
If Sql.startswith (sql_start[0]) or Sql.startswith (sql_start[1]) \
or Sql.startswith (sql_start[2]) or Sql.startswith (Sql_start[3]):
Return True
Else
Raise Sqlerr
#主动抛出异常


# is_correct_sql (' Sdfsdfsdfsdf ')
res = opertionmysql (' xxxxxx ')

Print (RES)
#raise是主动抛出一个异常, if the function is called
#没有捕捉异常的话, a function called at the previous level can also be captured

Eight, Python operation Excel and network programming and exception handling