第5章〓数据存取 数据存储是数据分析中非常重要的一环。在第4章爬取的数据仅仅是显示在程序的输出结果中，这样很不利于后期的数据分析和可视化。为此在爬取数据后，还需要对数据进行存储。常用的数据存储有文件和数据库两种方式，文件存储的格式有TXT、CSV、XLSX等，数据库存储有关系型数据库和非关系型数据库两类。 5.1JSON 在当今Web 3.0时代，以JavaScript与XML为代表的结合JavaScript、CSS、HTML等网页开发技术，仍然占据主流地位。 5.1.1JSON概述 1. JSON的概念 JSON的全称为JavaScript Object Notation，即JavaScript对象标记。JSON通过对象和数组的组合表示数据，是一种轻量级的数据交换格式。 JSON示例代码如下。 { "sites": ［ { "name"："重庆美食" , "url":"www.link1.com" }, { "name"："重庆美景" , "url":"www. link2.com" }, { "name"："重庆好玩" , "url":"www. link3.com" } ］ } XML示例代码如下。 重庆美食 www.link1.com 重庆美景 www.link2.com 重庆好玩 www.link3.com 通过以上两个示例的比较可以发现，JSON是比XML更简单的一种数据交换格式，它采用独立于编程语言的文本格式来存储和表示数据。JSON的语法规则如下。 (1) 使用键值对(key:value)表示对象属性和值。JSON的值有多种类型，如数字(整数或浮点数)、字符串(在双引号中)、逻辑值(true或false)、数组(在方括号中)、对象(在花括号中)和空（null）。 (2) 使用逗号“(，)”分隔多条数据。 (3) 使用花括号“｛｝”包含对象。 (4) 使用方括号“［］”表示数组。 2. JSON与XML比较 JSON和XML都是文本格式语言，均普遍用于数据交换和网络传输，它们的区别有以下几个方面。 (1) 可扩展性。 二者都有很好的扩展性，但JSON与JavaScript语言的结合更紧密，在JavaScript语言编写的网页中使用JSON更为合适。 (2) 可读性。 二者的可读性都很好，JSON的特点是简洁的语法，XML则是规范的标签形式。 (3) 编码难度。 XML诞生时间相对JSON而言要早一些，因此处理XML语言的编码工具更丰富。但是在相同结果下，XML文档需要的字符量更多。 (4) 解码难度。 JSON和XML都是可扩展性的结构，如果不知道文档结构，则解析文档会非常不方便。因此，两者都需要知道文档结构后才能进行解析。对于一般网页结构，可以通过F12键在开发者模式中进行直接观察，并由此作出判断。 (5) 有效数据率。 由于省去了大量的标签，因此JSON的有效数据率比XML高很多。 5.1.2用JSON库存取JSON文件 JSON库是Python中用于编码、解码JSON格式的标准库模块，主要用于将Python对象编码为JSON格式输出或存储，以及将JSON格式对象解码为Python对象。 JSON库提供了4种函数： json.dump()、json.load()、json.dumps()、json.loads()。 json.loads()可以将字符串转换为Python的数据结构，变成列表或字典。json.dumps()则相反，将Python类型的列表或字典转换为JSON字符串。json.load()可以传入一个JSON格式的文件流，并将其解码为Python对象。json.dump()函数用于将Python类型的数据以JSON格式存储到文件中。 1. 存取JSON数据 在存取JSON数据时，用json.dumps()和json.loads()来实现JSON字符串和Python数据类型的互转。例如： import json data = ''' ［{ "美食"： "火锅", "价格"： "100", "城市"： "重庆" }, { "美食"： "小面", "价格"： "10", "城市"： "重庆" }］ ''' print(data)#输出data print(type(data))#输出data的类型 py_data = json.loads(data)#将JSON类型的数据转换为Python类型的数据 print(py_data)#输出py_data数据 print(type(py_data))#输出py_data的类型 js_str = json.dumps(py_data,ensure_ascii=False)#将Python类型的数据转换为JSON字符串 print(js_str) #输出转换后的JSON数据 print(type(js_str)) #输出转换后的JSON类型 输出结果如下。 ［{ "美食"： "火锅", "价格"： "100", "城市"： "重庆" }, { "美食"： "小面", "价格"： "10", "城市"： "重庆" }］ ［{'美食'： '火锅'， '价格'： '100', '城市'： '重庆'}, {'美食'： '小面'， '价格'： '10', '城市'： '重庆'}］ ［{"美食"： "火锅"， "价格"： "100", "城市"： "重庆"}, {"美食"： "小面"， "价格"： "10", "城市"： "重庆"}］ 在以上代码中，使用json.dumps将Python类型的数据转换为JSON字符串时，添加了参数ensure_ascii=False。如果不添加ensure_ascii参数，默认输出为ASCII编码，而本例中的中文字符是无法正常输出的，因此将参数设置为False才能正常输出中文字符。 2. 存取JSON文件 在存取JSON文件时，用json.dump()和json.load()来实现JSON文件和Python数据类型的互转。例如： import json data = ''' ［{ "美食"： "火锅", "价格"： "100", "城市"： "重庆" }, { "美食"： "小面", "价格"： "10", "城市"： "重庆" }］ ''' with open("jd_json.json",'w',encoding='utf-8') as f: json.dump(data,f,ensure_ascii=False) with open("jd_json.json",'r',encoding='utf-8') as f: data_p=json.load(f) print(data_p) 程序执行结束后，会在当前目录生成一个jd_json.json文件并输出文件内容，输出结果如下。 ［{ "美食"： "火锅", "价格"： "100", "城市"： "重庆" }, { "美食"： "小面", "价格"： "10", "城市"： "重庆" }］ 注意： 文件操作也存在编码问题，encoding='utf8'可以避免中文不被正常显示。 5.1.3用Pandas库存取JSON文件 Pandas是Python中常用的第三方库，它在数据分析领域中占据重要的地位。除此之外，Pandas也可以很方便地处理 JSON文件。 安装Pandas库的方法有命令行方法和菜单方法两种，在Windows命令行中输入pip install pandas即可成功安装Pandas库。要使用Pandas库，需要在程序前输入import pandas as pd，这里的pd是对Pandas库的简写，方便编写时调用。 接下来介绍Pandas库对JSON文件的常用存取方法。 1. 写入文件 写入文件使用to_json()语句，在转换格式时，如果出现中文乱码，则添加参数force_ascii=False即可解决编码问题。例如： #导入Pandas库 import pandas as pd #定义数据 data =［{"美食"： "火锅"，"价格"： "100"，"城市"： "重庆"}, {"美食"： "小面"，"价格"： "10"，"城市"： "重庆"}］ #转为Pandas数据框 import pandas as pd frame = pd.DataFrame(data) print(frame) frame.to_json('frame.json',force_ascii=False) 程序执行后，会在当前目录生成一个frame.json文件，用记事本打开，内容如下。 {"美食"：{"0"："火锅"，"1"："小面"}，"价格"：{"0":"100","1":"10"}，"城市"：{"0"："重庆"，"1"："重庆"}} 2. 读取文件 读取文件使用to_json()函数，将待读取文件名传入参数即可。例如： import pandas as pd frame = pd.read_json('frame.json') print(frame) 输出结果如下。 美食价格城市 0火锅100重庆 1小面10重庆 5.2CSV存取 CSV(CommaSeparated Values，逗号分隔值)，即字符分隔值。CSV文件以纯文本(字符序列)的形式存储数据。CSV文件由多条记录组成，记录之间以换行符分隔，每条记录由字段组成，字段之间的分隔符可以是逗号或制表符等其他字符。CSV文件的扩展名是.csv，可以使用记事本或Excel打开。 5.2.1用CSV库存取CSV文件 Python内置了CSV库，用于对CSV文件进行读/写，因此不需要另外安装便可使用。要使用CSV库，需要在程序前输入import csv。 CSV库包含了处理CSV文件的多种方法，这里主要介绍读文件和写文件的常用对象和方法，如表51所示。 表51CSV库的常用对象和方法 对象/方法名描述类型 csv.writer(csvfile, dialect='excel', **fmtparams)返回将数据写入 CSV 文件的写入器对象 csv.reader(csvfile, dialect='excel', **fmtparams)返回一个遍历 CSV 文件各行的读取器对象 对象 续表 对象/方法名描述类型 writerows(Iterable)一次性写入多行数据 writerow(Iterable)写入单行数据 csv.DictWriter(f,fieldnames,restval="", extrasaction="raise", dialect="excel", *args, **kwds)以字典的形式写入数据 csv.DictReader(f,fieldnames=None,restkey=None, restval=None, dialect="excel", *args, **kwds)以字典的形式返回读取的数据 writeheader()写入标题行方法 1. 写入数据 下面通过代码介绍用CSV库写入数据文件的基本操作。 (1) 列表写入。 #导入CSV库 import csv #定义写文件函数 def writer(): #创建列表，保存标题内容 biaoti = ［"景点编号"， "景点名称"， "门票"］ #创建列表，保存数据 data = ［ ［"001", "动物园"， 25］, ［"002", "濯水古镇"， 50］, ［"003", "科技馆"， 0］ ］ #以写方式打开文件。注意添加 newline=""，否则会在两行数据之间都插入一行空白 with open("jingdian.csv", mode="w", encoding="utf-8-sig", newline="") as f: #基于打开的文件，创建 csv.writer 实例 w = csv.writer(f) #写入标题,writerow() 一次只能写入一行 w.writerow(biaoti) #写入数据，writerows() 一次写入多行 w.writerows(data) writer() 以上代码首先定义了biaoti的列表，描述每列的标题； 同时创建了data列表，定义了3行3列的数据，并使用“with…open…”语句创建了jingdian.csv文件。需要注意的是，f=open('names.csv', 'w')也可以创建一个names.csv文件，但是它必须要结合f.close()一起使用，否则后面对文件的操作会出现问题。“with…open…”语句通常不会出错，因此选用这种方式创建文件比较可靠。参数mode="w"表示写入文件； encoding="utf8sig"表示一种编码方式，这种编码方式可以解读中文字符，未指定编码方式将会导致中文字符不显示； newline=""可以避免在两行数据之间插入一行空白。然后基于打开的文件，用csv.writer(f)将f文件定义为一个写文件实例。接着用writerows()写入一行的标题内容，用writerows写入了数据。最后调用writer()函数，执行函数的所有内容，在当前Python文件的所在目录生成了一个jingdian.csv文件。 通过CSV库写入CSV文件，用Excel打开该文件，如图51所示。 图51通过CSV库写入CSV文件 (2) 字典写入。 #导入 CSV 库 import csv def writer(): #创建列表，保存标题内容 biaoti = ［"景点编号"， "景点名称"， "门票"］ data = ［ {"景点编号"："001", "景点名称"："动物园"，"门票"：25}, {"景点编号"："002", "景点名称"："濯水古镇"， "门票"：50}, {"景点编号"："003", "景点名称"："科技馆"， "门票"：0} ］ #以写方式打开文件。注意添加 newline=""，否则会在两行数据之间都插入一行空白 with open("jingdian.csv", mode="w", encoding="utf-8-sig", newline="") as f: #基于打开的文件，创建 csv.DictWriter 实例 w = csv.DictWriter(f,biaoti) #写入列标题 w.writeheader() #写入数据，writerows() 一次写入多行 w.writerows(data) writer() 以上代码定义了biaoti作为列名称列表，将data定义为数据的字典列表，再用DictWriter()方法将biaoti指定为data中列标题，将二者进行关联后用writeheader()写入标题。此种编写方法相对列表而言稍显烦琐，但优点在于便于初学者理解。 2. 读取数据 #导入CSV import csv #以指定编码的只读方式打开jingdian.csv with open("jingdian.csv", "r", encoding="utf-8-sig") as f: jingdian = csv.reader(f) #逐行输出CSV文件内容 for line in jingdian: print(line) 读取CSV文件的方法比较简单，用with open 语句以只读的方式打开jingdian.csv文件，再用csv.reader(f)将CSV文件转为读取器对象，通过for循环逐行输出CSV文件的所有内容。 5.2.2用Pandas库存取CSV文件 Pandas库也可以很方便地处理CSV文件，它的实现代码要比CSV库少很多。接下来介绍Pandas库对CSV文件的存取功能。 1. 写入数据 #Pandas写入CSV文件 #导入Pandas库并简写为pd import pandas as pd #定义3个列表jdbh、jdmc、jdmp，用于存放数据 jdbh= ［"001", "002", "003"］ jdmc= ［"动物园"， "濯水古镇"， "科技馆"］ jdmp= ［25, 50, 0］ #定义字典，为每一列数据加上字段名 jingdian = {"景点编号"： jdbh, "景点名称"： jdmc, "门票"： jdmp} #将jingdian这个字典定义为一个数据框 jd = pd.DataFrame(jingdian) #保存数据到jingdian_pandas.csv文件中 jd.to_csv('jingdian_pandas.csv',encoding="utf-8-sig", index=False) 在以上代码中，特别要注意保存文件时需要指定编码为utf8sig，否则无法显示中文内容。最终生成一个jingdian_pandas.csv文件，打开该文件，如图52所示。 图52通过Pandas库写入CSV文件 在使用.to_csv()写入数据时，会自动生成索引和列名称，因此需要将参数设置为index=False或headers=False，以避免写入索引和列名称。 2. 读取数据 import pandas as pd #打开文件 with open('jingdian_pandas.csv', 'r',encoding="utf-8-sig") as f: #读取文件 data = pd.read_csv(f, encoding="utf-8-sig") print(data) 输出结果如下。 Unnamed: 0景点编号景点名称门票 001动物园25 112濯水古镇50 223科技馆0 用Pandas库写入文件，其特点在于增加了索引和列名称。通常可以使用names选项指定表头，并将存有列名的数组赋给它。以下代码为读取结果添加了列名称。 data_tou = pd.read_csv("jingdian_pandas.csv",names=［'1列'，'2列'，'3列'］) print(data_tou) 输出结果如下。 1列2列3列 NaN景点编号景点名称门票 0.0001动物园25 1.0002濯水古镇50 2.0003科技馆0 若要将上述输出结果的索引“0.0，1.0，2.0”修改为所指定的索引，则需要通过参数index_col实现。 3. 读取部分数据 读取部分数据的方法如下。 (1) head(n)方法： 用于读取前n行数据。如果未填写参数n，则默认为前5行。此外，.read_csv()中添加参数nrows=n也可以读取前n行数据。 (2) tail(n)方法： 用于读取尾部的n行。如果未填写参数n，则默认返回5行，空行各字段的值返回NaN。 (3) info()方法： 返回表格的一些基本信息。 5.2.3应用案例 实现效果： 爬取“重庆—北京”的12306旅行车次信息，将其存储为CSV格式。 1. 直接存储文件 直接存储文件的实现代码如下。 # ==============爬取部分============================ import time from selenium import webdriver from lxml import etree base_url = r'https://kyfw.12306.cn/otn/leftTicket/init?linktypeid=dc&fs=%E9%87%8D%E5%BA%86,CQW&ts=%E5%8C%97%E4%BA%AC,BJP&date=2023-03-24&flag=N,N,Y' print("正在爬取数据……请等待……") options=webdriver.FirefoxOptions() #设置浏览器为headless 无界面模式 options.add_argument("--headless") options.add_argument("--disable-gpu") #打开浏览器处理，注意浏览器无显示 browser=webdriver.Firefox(options=options) browser.get(base_url) time.sleep(4) res=browser.page_source html = etree.HTML(res) #车次 result1 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［1］/div/a/text()') #始发站 result2 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［1］/text()') #到达站 result3 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［2］/text()') #出发时间 result4 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［1］/text()') #到达时间 result5 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［2］/text()') #历时 result6 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［4］/strong/text()') #输出车次信息 print('----共计{0}个车次信息，分别是：----'.format(len(result1))) for x in range(0,len(result1)): print('车次：',result1［x］，'始发站：',result2［x］，'到达站：',result3［x］，'出发时间：',result4［x］，'到达时间：',result5［x］，'历时：',result6［x］) print('----爬取的车次信息，显示完成----') #等待3s，关闭浏览器 time.sleep(3) browser.close() # =========CSV存储部分=============== for x in range(len(result1)): with open('火车信息.csv','a') as f: f.write(result1［x］+',') f.write(result2［x］+',') f.write(result3［x］+',') f.write(result4［x］+',') f.write(result5［x］+',') f.write(result6［x］+'＼n') 运行代码，生成一个“火车信息.csv”文件，用记事本打开文件，如图53所示。 图53直接存储文件 2. 用CSV库存储文件 用CSV库存储文件的实现代码如下。 l=［result1,result2,result3,result4,result5,result6］ l = list(map(list, zip(*l))) print(l) biaoti=［'车次'，'始发站'，'终到站'，'始发时间'，'终到时间'，'用时'］ with open("火车信息_csv.csv", mode="w", encoding="utf-8-sig", newline="") as f: w = csv.writer(f) # 写入标题,writerow() 一次只能写入一行 w.writerow(biaoti) # 写入数据，writerows() 一次写入多行 w.writerows(l) 3. 用Pandas库存储文件 用Pandas库存储文件的实现代码如下。 # ==============爬取部分============================ import time from selenium import webdriver from lxml import etree import pandas as pd base_url = r'https://kyfw.12306.cn/otn/leftTicket/init?linktypeid=dc&fs=%E9%87%8D%E5%BA%86,CQW&ts=%E5%8C%97%E4%BA%AC,BJP&date=2023-03-24&flag=N,N,Y' print("正在爬取数据……请等待……") options=webdriver.FirefoxOptions() #设置浏览器为headless 无界面模式 options.add_argument("--headless") options.add_argument("--disable-gpu") #打开浏览器处理，注意浏览器无显示 browser=webdriver.Firefox(options=options) browser.get(base_url) time.sleep(4) res=browser.page_source html = etree.HTML(res) #车次 result1 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［1］/div/a/text()') #始发站 result2 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［1］/text()') #到达站 result3 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［2］/text()') #出发时间 result4 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［1］/text()') #到达时间 result5 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［2］/text()') #历时 result6 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［4］/strong/text()') #输出车次信息 print('----共计{0}个车次信息，分别是：----'.format(len(result1))) for x in range(0,len(result1)): print('车次：',result1［x］，'始发站：',result2［x］，'到达站：',result3［x］，'出发时间：',result4［x］，'到达时间：',result5［x］，'历时：',result6［x］) print('----爬取的车次信息，显示完成----') #等待3s，关闭浏览器 time.sleep(3) browser.close() # =========CSV存储部分================ df=pd.DataFrame({'车次'：result1，'始发站'：result2，'到达站'：result3，'出发时间'：result4，'到达时间'：result5，'历时'：result6}) df.to_csv('火车查询信息'+time.strftime("-%m%d-%H%M%S", time.localtime())+'.csv',index=False,encoding='utf-8-sig') print('----爬取的车次信息，存储到CSV中----') 运行代码，自动生成一个“火车查询信息0316231407.csv”文件，用Excel打开文件，如图54所示。 图54用Pandas库存储文件 5.3XLSX存取 目前，Python中的读/写XLSX文件的第三方库有很多，如xlrd、xlwt、xlutils、xlwings、Openpyxl、xlsxwriter、Pandas等。每个库都各有特点，本节选取常用的xlrd、xlsxwriter、Openpyxl和Pandas库对XLSX文件进行存取。 5.3.1用xlrd库存取XLSX文件 1. 安装与导入 安装xlrd库时，用命令行方式输入pip install xlrd==1.2.0，导入模块使用import xlrd。需要注意的是，只有1.2.0及以上版本才支持XLSX文件格式，否则文件读取失败。 2. 使用方法 (1) 定义一个XLSX文件对象。 data = xlrd.open_workbook("E:＼火车信息.xlsx") 注意： 如果路径或文件名有中文，则需要在前面加一个"r"。 (2) 获取工作表。  按索引顺序获取： table = data.sheets()［0］。  按工作表名称获取： table = data.sheet_by_name(sheet_name)。  查询工作表名称： names = data.sheet_names()。 (3) 行的操作。  获取有效行数： nrows = table.nrows。  返回由该行中所有的单元格对象组成的列表： table.row(rowx)。  返回由该列中所有的单元格对象组成的列表： table.row_slice(rowx)。  返回由该行中所有单元格的数据类型组成的列表： table.row_types(rowx, start_colx=0, end_colx=None)。  返回由该行中所有单元格的数据组成的列表： table.row_values(rowx, start_colx=0, end_colx=None)。  返回该列的有效单元格长度： table.row_len(rowx)。 (4) 列的操作。  获取列表的有效列数： ncols = table.ncols。  返回由该列中所有的单元格对象组成的列表： table.col(colx, start_rowx=0, end_rowx=None)。  返回由该列中所有的单元格对象组成的列表： table.col_slice(colx, start_rowx=0, end_rowx=None)。  返回由该列中所有单元格的数据类型组成的列表： table.col_types(colx, start_rowx=0, end_rowx=None)。  返回由该列中所有单元格的数据组成的列表： table.col_values(colx, start_rowx=0, end_rowx=None)。 (5) 单元格的操作。  返回单元格对象： table.cell(rowx,colx)。  返回单元格中的数据类型： table.cell_type(rowx,colx)。  返回单元格中的数据： table.cell_value(rowx,colx)。 3. 基础操作案例 对“E:＼火车信息.xlsx”文件进行操作如下。 #导入xlrd模块 import xlrd #定义一个XLSX文件对象 data = xlrd.open_workbook(r"E:＼火车信息.xlsx")#文件名和路径，如果路径或文件名有中 #文，则需要在前面加一个"r" #查询工作表名称 names = data.sheet_names() #获取第1个工作表 table = data.sheet_by_index(0) #获取表格行数 nrows = table.nrows print("表格一共有"， nrows, "行") #获取表格第2行内容 row=table.row(1) print("表格第2行内容有：", row) #获取表格列数 nclos = table.ncols print("表格一共有"， nclos, "列") #获取表格第一列内容 col=table.col(0) print("表格第1列内容有：", col) #获取单个表格值 value = table.cell_value(1, 2) print("第2行第3列值为"， value) 输出结果如下。 表格一共有 13 行 表格第2行内容有： ［text:'G52', text：'重庆北'， text：'北京西'， xldate:0.3138888888888889, xldate:0.6013888888888889, xldate:0.28750000000000003］ 表格一共有 6 列 表格第1列内容有： ［text：'车次'， text:'G52', text:'G352', text:'G388', text:'T10', text:'G332', text:'Z96', text:'G372', text:'G372', text:'G54', text:'Z50', text:'Z4', text:'K508'］ 第2行第3列值为 北京西 5.3.2用xlsxwriter库写入XLSX文件 xlsxwriter库支持XLSX文件的写入，支持VBA，可以实现写入图表、设置数据验证、下拉列表、条件格式等操作。在写入XLSX文件较大时使用内存优化模式，是常用的一种写入Excel文件方式。 1. 安装与导入 安装xlsxwriter库时，用命令行方式输入pip install xlsxwriter，导入模块需要输入import xlsxwriter。 2. 使用方法 (1) 创建工作簿文件。 workbook = xlsxwriter.Workbook('旅游数据.xlsx') 可以创建一个名为“旅游数据.xlsx”的文件，定义工作簿变量为workbook。 (2) 创建工作表。 worksheet1 = workbook.add_worksheet() 使用workbook.add_worksheet()可以为workbook工作簿对象创建工作表，定义工作表变量为worksheet1。 (3) 写入数据。 ① 第1种方法： 按单元格添加。 worksheet1.write(A1, '景点编号') 用.write()为A1单元格录入数据“景点编号”。 ② 第2种方法： 按行号、列表添加。 worksheet.write(0，0，'景点编号') 用write()为第1行第1列录入数据“景点编号”。 在Excel中，录入的数据类型有文本、数字、日期等，因此，在xlsxwriter库中定义了不同类型数据的写入方法。例如，write_string()写入字符串，write_number()写入数字，write_blank()写入空值，write_formula()写入公式与函数，write_datetime()写入日期，write_boolean()写入逻辑值，write_url()写入链接。 (4) 调整格式。 ① 调整行高： set_row(row, num)。 例如，worksheet1.set_row(1, 20)可以将第2行的行高设置为20。 ② 调整列宽： set_column(col1,col2,num)，其中col1和col2分别指起始列和结束列。 例如，worksheet.set_column(‘B:C’, 20) 可以将B列和C列列宽都设定为20。 ③ 合并单元格。 例如，worksheet1.merge_range(0, 0, 0, 4, '景点信息表')表示从第0行第0列开始，合并1行5列单元格，并在合并后的单元格中输入“景点信息表”的文字。 ④ 设置单元格格式。 先在workbook中定义样式，然后在写入数据中加上样式即可。例如： import xlsxwriter workbook = xlsxwriter.Workbook('景点信息.xlsx') worksheet1 = workbook.add_worksheet() format = workbook.add_format({ 'bold': True,#字体加粗 'align': 'center',#水平居中 'valign': 'vcenter',#垂直居中 'border': 1, #单元格边框宽度 'bg_color': '#ffFF00', #单元格背景颜色 'num_format': '0.00',#格式化数据格式为小数点后两位 'font_size': 18,#字体大小 'font_color': 'red' #字体颜色 }) worksheet1.merge_range(0, 0, 0, 4, '景点信息表'， format) workbook.close() 运行代码，生成一个“景点信息.xlsx”文件，打开文件内容，如图55所示。 图55用xlsxwriter库生成XLSX文件并设置格式效果图 3. 应用案例 用xlsxwriter库写入文件的实现代码如下。 #导入模块 import xlsxwriter #创建工作簿 workbook = xlsxwriter.Workbook('景点信息.xlsx') #创建工作表 worksheet1 = workbook.add_worksheet() #定义标题格式 format = workbook.add_format({ 'bold': True,#字体加粗 'align': 'center',#水平居中 'valign': 'vcenter',#垂直居中 'border': 1,#单元格边框宽度 'bg_color': '#ffFF00', #单元格背景颜色 'num_format': '0.00',#格式化数据格式为小数点后两位 'font_size': 18,#字体大小 'font_color': 'red' #字体颜色 }) #合并1行5列，录入标题内容 worksheet1.merge_range(0, 0, 0, 4, '景点信息表'， format) #录入标题 worksheet1.write(1, 0, '景点编号') worksheet1.write(1, 1, '景点名称') worksheet1.write(1, 2, '项目') worksheet1.write(1, 3, '票价') worksheet1.write(1, 4, '地址') #定义数据 data=( ［'1'，"动物园"，'门票'，'25'，'重庆'］, ［'2', "科技馆"， '门票'， '25', '重庆'］, ［'3'，"金佛山"，'套票'，'100'，'重庆'］, ) #写入数据 for i in range(2,len(data)+2): for j in range(5): worksheet1.write_string(i,j,data［i-2］［j］) #关闭保存 workbook.close() 运行代码，在当前目录生成一个“景点信息.xlsx”文件，打开文件，效果如图56所示。 图56用xlsxwriter库写入文件效果图 5.3.3用Openpyxl库读/写、修改XLSX文件 Openpyxl是一个用于读取和编写XLSX、XLSM、XLTX和XLTM文件的库。它支持XLSX文件的读/写和修改，几乎可以实现Excel的所有功能，而且接口清晰，文档丰富。 1. 安装和导入 安装Openpyxl库时，用命令行方式输入pip install openpyxl，导入模块需要输入import openpyxl。 2. 使用方法 (1) 操作工作簿。  打开并读取工作簿： wb=openpyxl.load_workbook(filepath)，新建文件时默认有一个名为Sheet的工作表。  创建工作簿： wb=openpyxl.Workbook()。  保存工作簿： wb.save(filename)。 (2) 操作工作表。  获取第一个工作表对象： ws=wb.active。  获取指定名称的工作表对象： wb［sheet_name］。  获取所有工作表名称： wb.sheetnames。  获取所有工作表对象： wb.worksheets,wb.worksheets［0］表示第一个工作表。  创建工作表并返回工作表对象： wb.create_sheet(sheet_name,index="end")，默认添加到末尾。  复制工作表并返回工作表对象： wb.copy_worksheet(sheet)。  删除工作表： wb.remove(sheet)。 (3) 操作单元格。  设置工作表名称： ws.title。  设置工作表最大行号： ws.max_row。  设置工作表最大列表数字： ws.max_column。  表格末尾追加数据： ws.append(list)。  合并单元格： ws.merge_cells('A2:F2')。  取消合并单元格： ws.unmerge_cells('A2:F2')。 (4) 单元格读取。  根据坐标读取单元格： ws［'A1'］。  根据行列读取单元格： ws.cell(row, column, value=None)。  获取第一行的所有单元格对象： ws［1］。  获取第B列的所有单元格ws［"B"］。  获取第A~B列的所有单元格： ws［"A:B"］。  获取第1~2行的所有单元格对象： ws［1:2］。  获取单元格区域： ws［"A1：D4"］。  以列表形式返回所有单元格数据： ws.values。  设置单元格的值： cell.value。  设置数字列标： cell.column。  设置字母列标： cell.column_letter。  设置行号： cell.row。  设置单元格的坐标： cell.coordinate。  设置单元格的数据类型： cell.data_type。  设置单元格格式： cell.number_format，默认为常规型。  设置单元格Font对象： cell.font。  设置单元格边框： cell.border。  设置单元格水平/垂直对齐方式： cell.alignment。  设置单元格填充颜色： cell.fill。 (5) 行/列操作。  获取行对象： ws.row_dimensions［行号］。  获取列对象： ws.column_dimensions［字母列表］。  返回列表： get_column_letter(index)。  返回数字列表： column_index_from_string(string)。  设置行高： row.height。  设置列宽： column.width。 3. 基础操作案例 输入以下代码，实现写入数据到XLSX文件，并读取文件信息，输出文件内容。 import openpyxl #============写入部分============= #创建工作簿文件 jd = openpyxl.Workbook() #激活当前工作表 js=jd.active #创建一个 sheet1 s1 = jd.create_sheet("景点表"，0) #创建一个 sheet2 s2 = jd.create_sheet("游客表"，1) #设置 sheet1 标签背景色 s1.sheet_properties.tabColor = "00ff00" #修改工作表名称 s1.title="景点信息表" #合并1行5列，录入标题内容 s1.merge_cells('A1:E1') s1［'A1'］="景点信息表" #写入小标题 title = ［'景点编号'，'景点名称'，'项目'，'票价'，'地址'］ for col in range(len(title)): c=col+1 s1.cell(row=2,column=c).value=title［col］ jd.save("景点信息_openpy.xlsx") #输入数据 data=［ ('1'，"动物园"，'门票'，'25'，'重庆'), ('2', "科技馆"， '门票'， '25', '重庆'), ('3'，"金佛山"，'套票'，'100'，'重庆'), ］ rows = len(data) l = len(data［0］) for i in range(rows): for j in range(l): s1.cell(row=i +2, column=j + 1).value = data［i］［j］ jd.save("景点信息_openpy.xlsx") #============读取部分============= import openpyxl #打开工作簿文件 jd=openpyxl.load_workbook("景点信息_openpy.xlsx") js = jd.active #获取所有表格(worksheet)的名字 sheets = jd.sheetnames print(sheets) #获取工作表 s1 = jd［"景点信息表"］ #获取表格所有行和列，两者都是可迭代的 rows = s1.rows columns = s1.columns #迭代所有的行 for i in rows: line = ［col.value for col in i］ print(line) #通过坐标读取值 print(s1［'A1'］.value)#A表示列，1表示行 #查看第3行第2列的数据 print(s1.cell(row=3, column=2).value) 运行程序，在当前目录生成一个“景点信息_openpy.xlsx”文件，输出结果如下。 ［'景点信息表'， '游客表'， 'Sheet'］ ［'景点信息表'， Null, Null, Null, Null］ ［'1', '动物园'， '门票'， '25', '重庆'］ ［'2', '科技馆'， '门票'， '25', '重庆'］ ［'3', '金佛山'， '套票'， '100', '重庆'］ 景点信息表 科技馆 5.3.4用Pandas库读/写XLSX文件 Pandas库的功能十分强大，在数据处理分析领域应用广泛，且同样具有对XLSX文件的读/写操作。 1. 安装与导入 安装Pandas库时，用命令行方式输入pip install pandas，导入模块需要输入以下代码。 import pandas as pd 2. 使用方法 (1) 写入数据到XLSX文件。 写入数据到XLSX文件的方法是to_excel('文件名')。如果将单个对象写入 Excel 文件，则必须指定目标文件名； 如果将数据写入多张工作表，则需要创建一个ExcelWriter对象，并通过sheet_name参数依次指定工作表的名称。to_ecxel包含的参数如下。 df.to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=Null, columns=Null, header=True, index=True, index_label=Null, startrow=0, startcol=0, engine=Null, merge_cells=True, encoding=Null, inf_rep='inf', verbose=True, freeze_panes=Null) 其中，excel_writer表示文件路径或ExcelWriter对象； sheet_name表示指定某个工作表； columns表示需要写入的列； header表示列名； index表示要写入的索引； index_label表示引用索引列的列标签； startrow表示初始写入的行号，默认值为0； startcol表示初始写入的列序号，默认值为0。engine是一个可选参数，用于指定要使用的引擎，可以是openpyxl或xlsxwriter。 ① 写入数据到单个工作表。 import pandas as pd data=［ ('1'，"动物园"，'门票'，'25'，'重庆'), ('2', "科技馆"， '门票'， '25', '重庆'), ('3'，"金佛山"，'套票'，'100'，'重庆'), ］ df=pd.DataFrame(data) df.to_excel('景点_单表.xlsx', sheet_name='Sheet1', index=False) 执行以上代码，可以生成一个“景点_单表.xlsx”文件，文件内容如图57所示。 图57用Pandas库写入单个工作表 如果要去掉列名，则需要为to_excel()添加参数header=False，即可去掉第1行上自动生成的列名。 ② 写入文件到多个工作表。 import pandas as pd data1=［ ('1'，"动物园"，'门票'，'25'，'重庆'), ('2', "科技馆"， '门票'， '25', '重庆'), ('3'，"金佛山"，'套票'，'100'，'重庆') ］ data2=［ ('1'，"张三"，'186****0011'), ('2', "李四"， '186****0012'), ('3'，"王五"，'186****0013') ］ df1 = pd.DataFrame(data1) df2 = pd.DataFrame(data2) with pd.ExcelWriter('旅游统计表.xlsx') as writer: df1.to_excel(writer, sheet_name='景点信息'， index=False,header=False) df2.to_excel(writer, sheet_name='游客信息'， index=False,header=False) 执行以上代码，将会在当前目录创建一个“旅游统计表.xlsx”文件，其中包含了两个工作表“景点信息”和“游客信息”，且包含相应的数据。 如果原有文件存在且文件中有数据，则使用上述方法写入工作表后，文件中原有的数据会被覆盖。为了避免发生这种情况，需要使用在原文件中新增工作表的方法。 ③ 在原有文件中新增工作表。 import pandas as pd data=［ ('1'，"动物园"，'门票'，'25'，'重庆'), ('2', "科技馆"， '门票'， '25', '重庆'), ('3'，"金佛山"，'套票'，'100'，'重庆'), ］ df = pd.DataFrame(data) with pd.ExcelWriter('汇总表.xlsx', mode='a',engine='openpyxl') as writer: df.to_excel(writer, sheet_name='sheet1', index=False) 执行以上代码，将会在“汇总表.xlsx”文件中新增一个工作表“sheet1”，内容为data中的数据。 (2) 读取XLSX文件。 读取XLSX文件最常用的方法是read_excel('文件名')。如果指定某个工作表，则可以在参数中添加数字，第1个工作表从0开始编号，如 read_excel('文件名'，0)表示获取第1个工作表对象； 也可以用工作表名称进行定位，如read_excel('文件名'，'sheet1')表示获取sheet1工作表对象。执行以下代码，将会输出“汇总表.xlsx”文件中的第1个工作表的信息。 import pandas as pd df=pd.read_excel('汇总表.xlsx',0) print(df) 除此之外，read_excel()还有其他参数。例如，io表示文件路径； header表示规定哪几列为列名； names表示重新定义列名的值； index_col表示索引列，可以用数字表示使用哪一列作为索引，也可以是列表； usecols表示读取的列的范围，默认为所有列； converters表示列的数据类型； nrows表示需要读取的行数； skiprows表示跳过的行数。这些参数可以根据需要进行选择。 (3) 读取工作表内的数据。  获取工作表的尺寸： df.shape。  获取工作表前n行数据： df.head(n)。  获取工作表第m~n行的数据： df［m:n］。  获取工作表第m行和第n行（不连续的两行）的数据： df［［m,n］］。  获取工作表第m列的数据： df.iloc［:,m］。  获取工作表第m行第n列的值： df.iloc［m,n］。  获取工作表第m列和第n列的数据： df.iloc［：,［m,n］］。  获取工作表第m~n行且在第a~b列的数据区域： df.iloc［［m:n］,［a:b］］。 以上数字的编号均从0开始。一般情况下，整数索引切片是前闭后开，标签索引切片是前闭后闭。df.loc［］只能使用标签索引，不能使用整数索引，通过标签索引切片进行筛选时，前闭后闭。df.iloc［］只能使用整数索引，不能使用标签索引，通过整数索引切片进行筛选时，前闭后开。df.ix［］既可以使用标签索引，也可以使用整数索引。 3. 基础操作案例 现有“火车信息.xlsx”和“景点信息.xlsx”两个文件，需要将两个文件合并到一个“汇总表.xlsx”文件中，并分别输出汇总表中的数据。 import pandas as pd #读取两个文件中的数据 jdb=pd.read_excel('景点表.xlsx') cpb=pd.read_excel('车票表.xlsx') #同时写入两个文件到汇总表中 with pd.ExcelWriter('汇总表.xlsx', mode='w',engine='openpyxl') as writer: jdb.to_excel(writer, sheet_name='景点表'， index=False) cpb.to_excel(writer, sheet_name='车票表'， index=False) #读取汇总表的两个工作表 jdb_out=pd.read_excel('汇总表.xlsx',sheet_name='景点表') cpb_out=pd.read_excel('汇总表.xlsx',sheet_name='车票表') #输出工作表的尺寸 print(jdb_out.shape) #输出工作表前10行数据 print(jdb_out.head(10)) #输出工作表第2~10行的数据 print(jdb_out［3:10］) #输出工作表第2列的数据 print(jdb_out.iloc［:,1］) #输出工作表第2行第2列的值 print(jdb_out.iloc［1，1］) #整张表输出 print(jdb_out) 5.3.5应用案例 1. 12306车次信息爬取与存储 本案例实现步骤如下。  第1步： 爬取数据。  第2步： 存储数据。  第3步： 读取数据。 其中，存储数据分别用Pandas库、Openpyxl库和xlsxwriter库来写入，读取数据分别用xlrd库、Pandas库和Openpyxl库来写入，以便于比较各个库之间的区别及特点。 (1) 爬取数据。 爬取数据的实现代码如下。 import time from selenium import webdriver from lxml import etree base_url = r'https://kyfw.12306.cn/otn/leftTicket/init?linktypeid=dc&fs=%E9%87%8D%E5%BA%86,CQW&ts=%E5%8C%97%E4%BA%AC,BJP&date=2023-03-24&flag=N,N,Y' print("正在爬取数据……请等待……") options=webdriver.FirefoxOptions() #设置浏览器为headless 无界面模式 options.add_argument("--headless") options.add_argument("--disable-gpu") #打开浏览器处理，注意浏览器无显示 browser=webdriver.Firefox(options=options) browser.get(base_url) time.sleep(4) res=browser.page_source html = etree.HTML(res) #车次 result1 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［1］/div/a/text()') #始发站 result2 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［1］/text()') #到达站 result3 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［2］/strong［2］/text()') #出发时间 result4 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［1］/text()') #到达时间 result5 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［3］/strong［2］/text()') #历时 result6 = html.xpath('/html/body/div［2］/div［8］/div［8］/table/tbody/tr/td［1］/div/div［4］/strong/text()') #输出车次信息 print('----共计{0}个车次信息，分别是：----'.format(len(result1))) for x in range(0,len(result1)): print('车次：',result1［x］，'始发站：',result2［x］，'到达站：',result3［x］，'出发时间：',result4［x］，'到达时间：',result5［x］，'历时：',result6［x］) print('----爬取的车次信息，显示完成----') #等待3s，关闭浏览器 time.sleep(3) browser.close() (2) 存储数据。 ① 用Pandas库实现存储。 import pandas as pd df=pd.DataFrame({'车次'：result1，'始发站'：result2，'终点站'：result3，'出发时间'：result4，'到达时间'：result5，'历时'：result6}) df.to_excel('火车查询信息'+time.strftime("-%m%d-%H%M%S", time.localtime())+'.xlsx',index=False) print('----爬取的车次信息，存储到XLSX中----') ② 用xlsxwriter库实现存储。 import xlsxwriter workbook = xlsxwriter.Workbook('车票writer.xlsx') worksheet1 = workbook.add_worksheet() #录入标题 worksheet1.write(0, 0, '车次') worksheet1.write(0, 1, '始发站') worksheet1.write(0, 2, '终点站') worksheet1.write(0, 3, '始发时间') worksheet1.write(0, 4, '到达时间') worksheet1.write(0, 5, '用时') #定义数据 l1,l2,l3,l4,l5,l6=［］,［］,［］,［］,［］,［］ for i in range(len(result1)): l1.append(result1［i］) l2.append(result2［i］) l3.append(result3［i］) l4.append(result4［i］) l5.append(result5［i］) l6.append(result6［i］) l=［l1,l2,l3,l4,l5,l6］ l= list(map(list, zip(*l))) #写入数据 for i in range(1,len(l)+1): for j in range(6): worksheet1.write_string(i,j,l［i-2］［j］) #关闭保存 workbook.close() ③ 用Openpyxl库实现存储。 import openpyxl #创建工作簿文件 cc = openpyxl.Workbook() #激活当前工作表 js=cc.active #创建一个 sheet1 s1 = cc.create_sheet("景点表"，0) #写入小标题 title = ［'车次'，'始发站'，'到达站'，'始发时间'，'到达时间'，'用时'］ for col in range(len(title)): c=col+1 s1.cell(row=1,column=c).value=title［col］ #输入数据 l1,l2,l3,l4,l5,l6=［］,［］,［］,［］,［］,［］ for i in range(len(result1)): l1.append(result1［i］) l2.append(result2［i］) l3.append(result3［i］) l4.append(result4［i］) l5.append(result5［i］) l6.append(result6［i］) data=［l1,l2,l3,l4,l5,l6］ data= list(map(list, zip(*data))) rows = len(data) l = len(data［0］) for i in range(rows): for j in range(l): s1.cell(row=i +2, column=j + 1).value = data［i］［j］ cc.save("火车信息_openpy.xlsx") (3) 读取数据。 ① 用xlrd库读取XLSX文件。 #导入xlrd模块 import xlrd #定义一个XLSX文件对象 data = xlrd.open_workbook(r"车票writer.xlsx ") #查询工作表名称 names = data.sheet_names() #获取第1个工作表 table = data.sheet_by_index(0) #获取表格行数 nrows = table.nrows print("表格一共有"， nrows, "行") #获取表格列数 nclos = table.ncols print("表格一共有"， nclos, "列") #遍历表数据 for i in range(nrows): print(table.row_values(i)) ② 用Openpyxl库读取XLSX文件。 import openpyxl #打开工作簿文件 jd=openpyxl.load_workbook("车票writer.xlsx") js = jd.active # 获取所有表格(worksheet)的名字 sheets = jd.sheetnames print(sheets) #获取工作表 s1 = jd［'Sheet1'］ #获取表格所有行和列，两者都是可迭代的 rows = s1.rows columns = s1.columns #迭代所有的行 for i in rows: line = ［col.value for col in i］ print(line) ③ 用Pandas库读取XLSX文件。 import pandas as pd hc=pd.read_excel('火车信息_openpy.xlsx',sheet_name='景点表') print(hc) 对12306车次信息进行爬取与存储，最终生成的XLSX文件如图58所示。 图58车次信息XLSX文件 2. 携程网景点信息爬取与存储 本案例实现步骤为3步： 爬取数据、存储数据和读取数据。 (1) 爬取数据。 爬取数据的实现代码如下。 import requests import time from bs4 import BeautifulSoup #====景点信息爬取部分============ if __name__=='__main__': #通过观察网页，生成景点信息前10页的网址 headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.54 Safari/537.36'} url_list=［］ for x in range(1，11): url='https://you.ctrip.com/sight/chongqing158/s0-p{0}.html#sightname'.format(x) url_list.append(url) #开始爬取景点信息 i=1 list1=［］ for base_url inurl_list: print('========开始爬取第{0}页信息，共计10个景点========'.format(i)) #添加2s延时 time.sleep(2) res = requests.get(base_url, headers=headers) res.encoding = res.apparent_encoding soup = BeautifulSoup(res.text, 'lxml') res = soup.find_all(class_='list_mod2') for x in res: #景点名称 res1 = x.find_all('a') #景点地址 res2= x.find_all(class_='ellipsis') #景点热度 res3= x.find_all(class_='hot_score_number') #景点评分 res4= x.find_all(class_='score') #景点点评数 res5= x.find_all(class_='recomment') #显示信息 print('-'*30) print('景点名称：',res1［1］.string.strip()) print('景点地址：',res2［0］.string.strip()) print('景点热度：',res3［0］.string.strip()) print('景点评分：',res4［0］.find_all('strong')［0］.string.strip()) print('景点点评数：',res5［0］.string.strip()) #将数据存入字典后，加入列表中 dist1 = {} dist1［'景点名称'］ = res1［1］.string.strip() dist1［'景点地址'］ = res2［0］.string.strip() dist1［'景点热度'］ = res3［0］.string.strip() dist1［'景点评分'］ = res4［0］.find_all('strong')［0］.string.strip() dist1［'景点点评数'］ = res5［0］.string.strip().strip('条点评()') time.sleep(0.4) list1.append(dist1) time.sleep(0.4) print('========第{0}页信息，爬取完成========'.format(i)) i=i+1 print('========景点信息前10页信息，爬取完成========'.format(i)) (2) 存储数据。 ① 用Pandas库实现存储。 import pandas as pd #将数据保存到Execl表格中 name='景点信息'+'.xlsx' df = pd.DataFrame(list1) df.to_excel(name) print('----爬取的景点信息，存储到Excel中----') 执行以上代码，会生成一个“景点信息.xlsx”文件。 ② 用xlsxwriter库实现存储。 用xlsxwriter库也可以实现同样的存储效果，代码如下。 import xlsxwriter workbook = xlsxwriter.Workbook('景点writer.xlsx') worksheet1 = workbook.add_worksheet() #录入标题 worksheet1.write(0, 0, '景点名称') worksheet1.write(0, 1, '景点地址') worksheet1.write(0, 2, '景点热度') worksheet1.write(0, 3, '景点评分') worksheet1.write(0, 4, '景点点评数') #定义数据 l1, l2, l3, l4, l5, l6 = ［］, ［］, ［］, ［］, ［］, ［］ for i in range(len(list1)): l1.append(list1［i］.get("景点名称")) l2.append(list1［i］.get("景点地址")) l3.append(list1［i］.get("景点热度")) l4.append(list1［i］.get("景点评分")) l5.append(list1［i］.get("景点点评数")) l=［l1,l2,l3,l4,l5］ l= list(map(list, zip(*l))) #写入数据 for i in range(1,len(l)+1): for j in range(5): worksheet1.write(i,j,l［i-1］［j］) #关闭保存 workbook.close() ③ 用Openpyxl库实现存储。 用Openpyxl库也可以实现同样的存储效果，代码如下。 import openpyxl #============写入部分============= #创建工作簿文件 cc = openpyxl.Workbook() #激活当前工作表 js=cc.active #创建一个 sheet1 s1 = cc.create_sheet("景点表"，0) #写入小标题 title = ［'景点名称'，'景点地址'，'景点热度'，'景点评分'，'景点点评数'］ for col in range(len(title)): c=col+1 s1.cell(row=1,column=c).value=title［col］ #输入数据 l1, l2, l3, l4, l5, l6 = ［］, ［］, ［］, ［］, ［］, ［］ for i in range(len(list1)): l1.append(list1［i］.get("景点名称")) l2.append(list1［i］.get("景点地址")) l3.append(list1［i］.get("景点热度")) l4.append(list1［i］.get("景点评分")) l5.append(list1［i］.get("景点点评数")) l=［l1,l2,l3,l4,l5］ l= list(map(list, zip(*l))) #写入数据 rows = len(l) le = len(l［0］) for i in range(rows): for j in range(le): s1.cell(row=i +2, column=j + 1).value = l［i］［j］ cc.save("景点_openpy.xlsx") (3) 读取数据。 ① 用xlrd库读取XLSX文件。 #导入xlrd模块 import xlrd #定义一个XLSX文件对象 data = xlrd.open_workbook(r"景点_openpy.xlsx ") #查询工作表名称 names = data.sheet_names() #获取第1个工作表 table = data.sheet_by_index(0) #获取表格行数 nrows = table.nrows print("表格一共有"， nrows, "行") #获取表格列数 nclos = table.ncols print("表格一共有"， nclos, "列") #遍历表数据 for i in range(nrows): print(table.row_values(i)) ② 用Openpyxl库读取XLSX文件。 import openpyxl #打开工作簿文件 jd=openpyxl.load_workbook("景点_openpy.xlsx") js = jd.active #获取所有表格(worksheet)的名字 sheets = jd.sheetnames print(sheets) #获取工作表 s1 = jd［'景点表'］ #获取表格所有行和列，两者都是可迭代的 rows = s1.rows columns = s1.columns #迭代所有的行 for i in rows: line = ［col.value for col in i］ print(line) ③ 用Pandas库读取XLSX文件。 import pandas as pd jd=pd.read_excel('景点信息.xlsx',index_col=False) print(jd) 对携程网景点信息进行爬取与存储，最终生成的XLSX文件如图59所示。 图59景点信息XLSX文件 5.4数据库存取 在进行旅游大数据分析时，根据数据的结构，可以选择关系数据库和非关系数据库来存储数据。关系数据库能够实现复杂的数据查询，且有事务支持，可以实现数据存储的高安全性，目前MySQL在数据分析领域应用较为广泛。非关系数据库则主要存储非结构化的数据，如文本、图片、音频和视频等数据，常用的有MongoDB、Redis等。在大数据时代数据类型多且数据增长快的情况下，非关系数据库发展迅速，但是关系数据库凭借其高可靠性、高效率的数据管理优势，依然是主流数据库。因此，下面主要介绍关系数据库的使用方法。 5.4.1数据模型 数据库使用数据模型对现实世界进行抽象化，数据模型是对数据和数据之间联系的描述。现有的数据库系统均是基于某种数据模型的。常见的数据模型有3种： 层次模型、网状模型和关系模型。 1. 层次模型 层次模型使用树状结构表示实体及实体间的联系，满足以下两个条件的数据模型称为层次模型。 (1) 有且仅有一个结点无父结点，该结点为根结点。 (2) 其他结点有且仅有一个父结点。 2. 网状模型 网状模型使用网状结构表示实体及实体间的联系，满足以下两个条件之一的数据模型称为网状模型。 (1) 允许一个以上的结点无父结点。 (2) 允许结点可以有多于一个的父结点。 3. 关系模型 关系模型使用一组二维表表示实体及实体间的关系，它将世界看作是由实体和联系构成的。联系就是实体之间的关系，可以分为3种： 一对一、一对多、多对多。 关系模型的特点如下。 (1) 表中的每列都是不可再细分的基本数据项。 (2) 每列的名称不同，数据类型相同或兼容。 (3) 行的顺序无关紧要。 (4) 列的顺序无关紧要。 (5) 关系中不能存在完全相同的两行。 5.4.2关系数据库的基本概念与运算 在使用关系数据库之前，需要先了解它的基本概念和关系之间的运算。 1. 基本概念 (1) 关系： 一个关系对应一张二维表，每个关系有一个关系名。 (2) 记录： 表中的一行为一条记录，记录也称为元组。 (3) 属性： 表中的一列为一个属性，属性也称为字段。每一个属性都有一个名称，即属性名。 (4) 关键字： 表中的某个属性集，它可以唯一确定一条记录。 (5) 主键： 一个表中可能有多个关键字，但在实际的应用中只能选择一个，被选用的关键字称为主键。 (6) 值域： 属性的取值范围。 2. 关系运算 对关系数据库进行查询时，若要找到用户关心的数据，就需要进行一定的关系运算。关系运算有两种： 一种是传统的集合运算(如并、差、交、广义笛卡儿积等)； 另一种是专门的关系运算(如选择、投影、连接等)。 专门的关系运算的概念如下。 (1) 选择： 在关系中选择满足指定条件的元组。 (2) 投影： 在关系中选择某些属性(列)。 (3) 连接： 从两个关系的笛卡儿积中选取属性间满足一定条件的元组。 5.4.3关系数据库设计 在创建关系数据库之前，不论基于什么平台，都需要先设计关系数据库，其设计步骤为以下4步。 1. 建立ER(实体关系)模型 将现实世界抽象化为信息世界是设计数据库的第一步。将现实世界中客观存在的事物及其所具有的特征抽象为信息世界的实体和属性，进而绘制出ER图。绘图时涉及的实体、属性、实体标识符、联系及联系类型的概念如下。 (1) 实体： 客观存在并可以相互区分的事物称为实体，用矩形表示。 (2) 属性： 实体所具有的某一特性，用椭圆表示，并用连线与实体相连接。 (3) 实体标识符： 能唯一标识实体的属性或属性组合。 (4) 联系： 实体与实体之间的联系。联系的类型有一对一、一对多、多对多。用菱形框表示，并用连线与有关实体相连接。 例如，在设计旅游数据库时，将现实世界的旅游景点和游客抽象为景点和游客两个实体。景点实体具有景点编号、景点名称、景点位置、项目编号、项目名称、项目标价、项目折扣、旅游荐点(周边游、亲子游、团建游)等属性，游客实体具有身份证号、姓名、年龄、籍贯、手机号、家庭人数等属性。每位游客可以选多个景点，每个景点可以有多名游客旅游，游客与景点之间的联系是多对多，因此用游客旅游表作为游客和景点之间的联系名，并设置游客编号、游客姓名、游客年龄、景点编号、项目名称、旅游时间、消费金额、同行人数、交通方式、旅游形式(报团、自驾、周边游)等属性。 根据以上分析，绘制出旅游数据库ER图，如图510所示。 图510旅游数据库ER图 2. 将ER模型转换为关系模型 将第1步绘制的ER图转换为二维表的形式，即可得到关系模型。将上述案例中的ER图转换为关系模型后，得到以下3个表。 表52游客表（原） 身份证号姓名年龄/岁籍贯手机号家庭人数/人 50011…3121张三20重庆186888888883 ……………… 表53景点表（原） 景点编号景点名称景点位置项目编号项目名称项目标价项目折扣旅游荐点 CQ001动物园重庆主城DWY001门票2520亲子游 CQ001黔江濯水古镇重庆黔江ZS001游船7550周边游 …………………… 表54游客旅游表（原） 游客 编号游客 姓名游客 年龄/岁景点 编号项目 名称旅游 时间消费金额 /元同行人数 /人交通 方式旅游 形式 yk0001张三30CQ001门票2023/5/11004汽车自驾 yk0001张三30CQ001游船2023/5/23004火车+大巴 跟团 ………………………… 3. 对关系模型进行规范化 对第2步所得的3个二维表进行规范。规范化的目的是消除存储异常，减少数据冗余，保证数据完整性和存储效率。对于不同的规范化程序，可用“范式”来衡量，记作NF。一般规范化可实现第三 范式。 (1) 第一范式。 定义： 一个关系的每个属性都是不可再分的基本数据项。 经分析，表51、表52和表53三个表都符合第一范式。 为了理解第二范式和第三范式，需要了解函数依赖、部分函数依赖和函数传递依赖的概念。 ① 函数依赖： 完全依赖或部分依赖于主关键字。 例如，景点表中的景点名称、景点地址、旅游项目等都函数依赖于主关键字景点编号。 ② 部分函数依赖： 表中某属性只函数依赖于主关键字中的部分属性。 例如，游客旅游表中的旅游时间依赖于主关键字的游客编号，也依赖于主关键字中的景点编号，它完全函数依赖主关键字(游客编号、景点编号)； 而景点名称只函数依赖于主关键字(游客编号、景点编号)中的景点编号，它与游客编号无关，是部分函数依赖。 ③ 函数传递依赖： 属性之间存在传递的函数依赖关系。 例如，景点表中的景点编号决定项目编号，项目编号决定项目名称。如果项目名称通过项目编号的传递而依赖于主关键字景点编号，则称项目名称和景点编号之间存在函数传递依赖关系。 (2) 第二范式。 定义： 首先是第一范式，并且关系中的每个非主属性完全函数依赖(而不是部分依赖)于主关键字。 将非第二范式转换为第二范式： 提取部分函数依赖关系中的主属性(决定方)和非主属性从关系中提取，使其单独构成一个关系； 将关系中余下的其他属性加主关键字，从而构成关系。例如，游客旅游表中的景点名称部分函数依赖于主关键字(游客编号、景点编号)中的景点编号，需要对景点名称和景点编号进行分离。 (3) 第三范式。 定义： 首先是第二范式，并且关系中的任何一个非主属性都不函数传递依赖于任何主关键字。 消除函数传递依赖关系的方法如下。 例如，对景点表中的项目编号、项目名称、项目标价和折扣价进行分离，使其单独组成一个关系，并将剩余的景点编号、景点名称、项目编号构成一个关系表。 经过以上3个范式的规范，将上文中的3个表最终规范为5个表，如表55~表59所示。 表55游客表 身份证号姓名年龄/岁籍贯手机号家庭人数 50011…3121张三20重庆186888888883 ……………… 表56景点表 景点编号景点名称景点位置旅游荐点 CQ001动物园重庆主城亲子游 CQ002黔江濯水古镇重庆黔江周边游 ………… 表57项目表 项目编号项目名称项目标价/元折扣价/元 DWY001门票2520 ZS001游船7550 ………… 表58景点项目表 景 点 编 号项 目 编 号 CQ001DWY001 CQ001DWY001 …… 表59游客旅游表 游客编号景点编号项目编号旅游时间消费金额/元同行人数/人交通方式旅游形式 yk0001CQ001DWY0012023/5/11004汽车自驾 yk0001CQ002ZS0012023/5/23004火车+大巴跟团 …………………… 4. 数据完整性 规范后的多个二维表组成了数据库的主要内容，在此基础上，还需要保证数据完整性，才能创建数据库。数据完整性的规则体现在以下几方面。 (1) 列(域)完整性： 表中的每一列数据都必须满足所定义的数据类型，并且其值在有效范围之内。 (2) 表完整性： 表中必须有一个主关键字(不能为NULL)。 (3) 参照完整性： 每两个关联的表中的数据必须是一致的、协调的，主关键字与外关键字也必须是一致的、协调的。 (4) 在从表中进行插入操作时，要保证外关键字的值一定在主表中存在。 (5) 在主表中修改主关键字值时，要在从表中同步修改，或者禁止修改主表。 (6) 在从表中修改外关键字值时，要保证修改的值在主表中存在。 (7) 在删除主表记录时，要注意从表中是否引用主关键字。若存在引用，则禁止删除或同步删除从表记录。 5.4.4SQL语句 结构化查询语言SQL是操作关系数据库的工业标准语言。在SQL中，有以下4类语言。 (1) 数据查询语言(Data Query Language,DQL)： SELECT。 (2) 数据操纵语言（Data Manipulation Language，DML）： INSERT、UPDATE、DELETE。 (3) 数据定义语言（Data Definition Language，DDL）： CREAT、ALTER、DROP。 (4) 数据控制语言(Data Control Language，DCL)： GRANT、REVOKE。 这些语句是非常重要的，特别是在用Visual Basic、Power Builder等工具开发数据库应用程序时，这些语句是操作数据库的重要途径。 1. 常用运算符及函数 (1) 常用运算符。 运算符是表示实现某种运算的符号，一般分为4类： 算术运算符、关系运算符、逻辑运算符和字符串运算符。表510列出了常用运算符，其中，Like通常与“?”“*”“#”等通配符结合使用，主要用于模糊查询。在这3种通配符中，“?”表示任何单一字符； “*”表示零个或多个字符； “#”表示任何一个数字(0~9)。 表510常用运算符 类型运算符 算术运算符+ */^(乘方)＼(整除)MOD(取余数) 关系运算符<<=< >>>=BetweenLike 逻辑运算符NOT AND OR 字符串运算符& (2) 常用函数。 在SQL语句中可以使用大量的函数。表511列出了SQL语句中的常用函数。 表511常用内部函数和聚合函数 函数类型函数名说明 内部函数 Date()返回系统日期 Year()返回年份 AVG()计算某一列的平均值 COUNT(*)统计记录的个数 COUNT(列名)统计某一列值的个数 SUM(列名)计算某一列的总和 续表 函数类型函数名说明 聚合函数 MAX(列名)计算某一列的最大值 MIN(列名)计算某一列的最小值 FIRST(列名)/LAST(列名)分组查询时，选择同一组中第一条(或最后一条)记录在指定列上的值，将其作为查询结果中现在应记录在该列上的值 2. 创建数据库 创建一个数据库的SQL语句为CREATE DATABASE db_name。 例如，要创建一个旅游系统(tourism system)的数据库，编写SQL语句如下。 CREATE DATABASE tousys 其他常用操作如下。 (1) 查看数据库： show db_name。 (2) 选择指定数据库： use db_name。 (3) 删除数据库： drop database db_name。 3. 创建数据表 创建数据表的SQL语句如下。 CREATE TABLE 表名 (列名称1 数据类型, 列名称2 数据类型， 列名称3 数据类型， …) 常见的数据类型如下。 (1) int(size)： 整型。在括号内规定数字的最大位数。 (2) decimal(size,d)： 容纳带有小数的数字。“size”规定数字的最大位数，“d” 规定小数点右侧的最大位数。 (3) char(size)： 容纳固定长度的字符串(可容纳字母、数字和特殊字符)。在括号中规定字符串的长度。 (4) varchar(size)： 容纳可变长度的字符串(可容纳字母、数字和特殊字符)。在括号中规定字符串的最大长度。 (5) date(yyyymmdd)： 容纳日期。 例如，创建一个用户信息表，编写SQL语句如下。 CREATE TABLE Persons (Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)) 又如，创建一个景点信息表，编写SQL语句如下。 CREATE TABLE jdxxb (jdbh int,jdmc varchar(255),jdrd varchar(255),Address varchar(255),jddps varchar(255)，PRIMARY KEY (jdbh )) 该景点信息表的表结构如表512所示。 表512jdxxb表结构 列名称（jdbh）数据类型（int） jdmcvarchar(255) jdrdvarchar(255) addressvarchar(255) jddpsvarchar(255) 在创建jdxxb表时，最后一行的PRIMARY KEY (jdbh )表示设置jdbh为表的主键。此外，设置主键也可以直接在定义列名时写入，如jdbh int PRIMARY KEY。 删除数据表的SQL语句为DROP TABLE表名，清空数据表的SQL语句为DELETE FROM表名。 4. 插入语句（INSERT） 插入数据的SQL语句如下。 INSERT INTO表名［(字段1，字段2，…，字段n)］ VALUES(常量1，常量2，…，常量n) 例如，在jdxxb表中插入数据。 INSERT INTO jdxxb VALUES ( (1001，"动物园"，8.9，"重庆"，8900), (1002，"科技馆"，9.3，"重庆"，6000) ) 5. 查询语句(SELECT) 数据查询是数据库的核心操作，SELECT语句如下。 SELECT ［ALL|DISTINCT］目标列FROM表(或查询) ［WHERE条件表达式］ ［GROUP BY列名1［ HAVING过滤表达式］］ ［ORDER BY列名2［ ASC|DESC］］ 在SELECT语句中，选择目标列部分是最基本的、不可缺少的，属于基本语句； 其余部分是可以省略的，称为子句。 整个语句的功能是，根据 WHERE子句中的表达式，从FROM子句指定的表或查询中找出满足条件的记录，再按SELECT子句中的目标列显示数据。SELECT语句是数据查询语句，不会更改数据库中的数据。 对SELECT语句的进一步分析如下。 (1) 查询多行或多列。 在水平方向上查询满足条件的行的记录，称为选择； 在垂直方向上查询满足条件的列的记录，称为投影。 基本语句： SELECT［ALL|DISTINCT］目标列FROM表 WHERE 条件。 功能： 从FROM子句指定的表或查询中找出满足条件的记录，再按照目标列或行显示数据。 SELECT语句的一个简单用法如下。 SELECT列名1，…，列名 n FROM表 WHERE 条件 如果要修改目标里的显示名称，则可以在列名后添加“AS别名”； 如果要查询所有列的内容，则用“*”表示。DISTINCT表示去除查询结果中的重复值。除此之外，目标列中的列名也可以是一个使用聚合函数的表达式。如果没有 GROUP BY子句，则对整个表进行统计，整个表只产生一条记录； 否则，进行分组统计，一组产生一条记录。 例如，从景点信息表(jdxxb)中查询所有重庆的景点信息。 SELECT * FROM jdxxb whereaddress="重庆" 若要查询所有的景点名称列(jdmc)，则SELECT语句为 SELECTjdmc FROM jdxxb (2) 排序查询结果。 ORDER BY子句用于指定查询结果的排列顺序。ASC表示升序，DESC表示降序，默认是升序。ORDER BY可以指定多个列作为排序关键字。 例如，从景点信息表(jdxxb)中查询所有重庆的景点信息，并按景点编号从大到小降序排序。 SELECT * FROM jdxxb WHERE address="重庆" ORDER BY jdbh DESC (3) 分组查询。 GROUP BY子句用于对查询结果进行分组，即将在某一列上值相同的记录分在一组，一组产生一条记录。 例如，在景点信息表(jdxxb)中查询所有地区的景点信息。 SELECT * FROM jdxxb GROUP BY address GROUP BY后可以有多个列名，分组时把在这些列上值相同的记录分在一组。 例如，在景点信息表(jdxxb)中查询所有地区的景点信息，并计算每个地区的平均点评数。 SELECT address, AVG(jddps) FROM jdxxb GROUP BY address 若要对分组后的结果进行筛选，则可以使用HAVING子句。 例如，在景点信息表(jdxxb)中查询所有地区的景点信息，计算每个地区的平均点评数并筛选出平均点评数大于6000的结果。 SELECT address, AVG(jddps) FROM jdxxb GROUP BY address HAVING AVG(jddps)>6000 HAVING子句与WHERE子句都是用来设置筛选条件的，二者的区别在于，HAVING子句是在分组统计之后进行过滤，而WHERE子句是在分组统计之前进行选择记录。 (4) 多表查询。 在查询关系数据库时，有时需要的数据分布在几个表或视图中，此时需要按照某个条件将这些表或视图连接起来，形成一个临时的表，然后再对该临时表进行简单查询。 例如，有两个表jdxxb(jdbh,jdmc,jdrd,address,jddps)和jddpb(jdbh,user,jdpf)，分别存储了景点信息表(景点编号、景点名称、景点热度、景点地址、景点点评数)和景点点评表(景点编号、用户、景点评分)，查询景点编号为10的景点评分。 SELECT jdbh, jdmc,address,AVE(jdpf) FROM jdxxb,jddpb WHERE jdxxb.jdbh=jddpb.jdbh GROUP BY jdbh, jdmc,address, jdpf 6. 删除语句（DELETE） 在SQL中，DELETE语句用于数据删除，其语法格式如下。 DELETE FROM表［ WHERE条件］ DELETE语句用于从表中删除满足条件的记录。如果 WHERE子句为默认，则删除表中所有的记录，此时表没有被删除，仅仅删除了表中的数据。 例如，删除表jdxxb中所有地址为“重庆”的记录。 DELETE * from jdxb where address="重庆" 7. 修改语句（UPDATE） 在SQL中，UPDATE语句用于数据修改，其语法格式如下。 UPDATE表SET字段1=表达式1，…，字段n=表达式n［ WHERE条件］ UPDATE语句用于修改指定表中满足条件的记录，并按表达式的值修改相应的值。如果WHERE子句为默认，则修改表中的所有记录。 例如，将表 jdxxb中地址为“重庆”的数据改为“西南”。 UPDATE jdxxb SET address ="西南" WHERE address ="重庆" 需要注意的是，UPDATE语句一次只能对一个表进行修改，这就有可能破坏数据库中数据的一致性。因此，更新数据时需要考虑相关联的表格因素。 8. 其他语句 在某些特殊情况里需要使用其他SQL语句。例如，修改表的结构，使用ALTER TABLE语句； 授权用户，使用GRANT语句； 回收授权，使用REVOKE语句。 5.4.5在Python中操作MySQL 1. 安装MySQL数据库 在Python中调用MySQL数据库，首先需要安装MySQL数据库。 登录https://www.mysql.com/cn/downloads/，下载MySQL Community，下载后的文件名为mysqlinstallercommunity8.0.32.0.msi。对数据库进行运行安装，具体步骤如下。 (1) 选择Custom安装方式，单击Next。 (2) 在Select Products选项中，选择MySQL Server8.0.3264、Connector/ODBC 8.0.32 X64和Connector/NET 8.0.32 X86，分别添加到右侧安装列，如图512所示。修改Advanced Option，将安装目录和数据目录分别改为D盘和E盘，如图513所示。 图512选择安装文件 图513修改安装目录和数据目录 (3) 在Installation中，执行上述选择的3个产品安装即可。 (4) 在Products Configuration中对软件进行配置。在Type and Networking界面，对配置类型和网络连接进行设置，如图514所示。在Named Pipe界面，选择Full access to all users(NOT RECOMMENDED)。在Authentication Methord界面，选择第1个选项。在Accounts and Roles界面，可以设置默认用户名是root的管理员账户，也可以添加自定义账户，具体操作如图515和图516所示。 图514设置配置类型和网络连接 图515创建管理员账户 图516添加自定义账户 接下来跟随软件提示进行操作，直到出现Installation Complete界面，此时安装完成。 安装后可以进入开始菜单，查找MySQL 8.0 Command Line Client并运行，在命令框中输入设置好的管理员账户密码，如果看到如图517所示的效果，则提示安装成功。 图517测试连接 如果数据库未安装成功，则需要找到MySQL的安装路径，并在计算机的系统高级设置中配置环境变量，如图518所示。 图518配置环境变量 2. 用pymysql库操作MySQL数据库 pymysql库是在Python中操作数据库的一个第三方库。因此，除了掌握SQL语句外，还要掌握pymysql库的操作语法，才能操作MySQL数据库。安装pymysql库后，输入以下代码导入库。 import pymysql 在Python中执行SQL语句的步骤如下。 (1) 创建数据库的连接对象。 (2) 创建游标对象获取当前游标。 (3) 使用游标对象中的execute()或fetchall()等方法，执行某个SQL语句或获取数据。 (4) 关闭连接，释放资源。 对上述步骤的进一步分析如下。 (1) 创建连接对象。 在创建数据库的连接对象时，可以输入以下代码，以此获取本机数据库的连接对象。如果服务器不在本机，则需要将host设置为服务器的IP地址，代码如下。 mydb=pymysql.connect(host="127.0.0.1",user="wxn",password="123456") 执行以下代码，测试与MySQL数据库是否连接成功。 import pymysql try: mydb=pymysql.connect( host="127.0.0.1",user="wxn",password="123456") print("wxndb连接成功") except Exception as err: print("wxndb连接失败") (2) 获取游标。 从字面上理解，游标是指流动的标志。使用游标功能，可以存储SQL语句的执行结果，并提供一个游标接口给用户。在需要获取数据时，可以直接从游标中获取。 创建游标对象mycursor，并获取当前数据库对象的游标，代码如下。 cursor=mydb.cursor() (3) 使用游标方法执行SQL语句或获取数据。 游标的常用方法有execute(SQL语句)、fetchone()、fetchall()等。 ① 用execute()可以执行SQL语句，执行后要用commit()进行提交。 ② 用fetchone()可以获取一条数据，用fetchall()可以获取所有数据。这两个函数主要是用于将获取到的数据赋值给变量，便于之后的调用。 (4) 关闭连接。 在数据库操作结束后，断开数据库并释放资源，代码如下。 cursor.close() 3. 基础操作案例 创建旅游系统数据库(data base)，其中包含5个表，分别是游客表(身份证号、姓名、年龄、籍贯、手机号、家庭人数)、景点表(景点编号、景点名称、景点位置、旅游荐点)、项目表(项目编号、项目名称、项目标价、折扣价)、景点项目表(景点编号、项目编号)、游客旅游表(游客编号、景点编号、项目编号、旅游时间、消费金额、同行人数、交通方式、旅游形式)。创建成功后录入数据，查看数据库中的内容，并删除数据库。 该案例的实现步骤如下。 (1) 导入pymysql库并获取账户连接。 import pymysql #准备工作：获取账户连接 myconn=pymysql.connect(host="127.0.0.1",user="wxn",password="123456") (2) 创建数据库。 #定义SQL语句 sql1=''' create database if not exists toursys''' #获取游标 mycursor=myconn.cursor() #执行SQL语句 mycursor.execute(sql1) (3) 获取数据库的连接对象。 myconn=pymysql.connect(host="127.0.0.1",user="wxn",password="123456",database="toursys") (4) 创建5个数据表。 #第1个：游客表 sql1=''' create table if not exists ykb(ykbh varchar(18) primary key,sfzh varchar(18),xm varchar(10),nl varchar(3),jg varchar(10),sj char(11),jtrs varchar(3))''' mycursor=myconn.cursor() mycursor.execute(sql1) #第2个：景点表 sql2=''' create table if not exists jdb(jdbh varchar(18) primary key,jdmc varchar(18),jdwz varchar(18),lyjd varchar(10))''' mycursor=myconn.cursor() mycursor.execute(sql2) #第3个：项目表 sql3=''' create table if not exists xmb(xmbh varchar(18) primary key,xmmc varchar(18),xmbj varchar(10),zkj varchar(10))''' mycursor=myconn.cursor() mycursor.execute(sql3) #第4个：景点项目表 sql4=''' create table if not exists jdxmb(jdbh varchar(18),xmbh varchar(18),primary key(jdbh,xmbh))''' mycursor=myconn.cursor() mycursor.execute(sql4) #第5个：游客旅游表 sql5=''' create table if not exists yklyb(ykbh varchar(18),jdbh varchar(18),xmbh varchar(18),lysj date,xfje varchar(10),yxrs varchar(3),jtfs varchar(10),lyxs char(11))''' mycursor=myconn.cursor() mycursor.execute(sql5) (5) 插入数据。 #第1个：游客表 sqlin1='''insert into ykb values(%s,%s,%s,%s,%s,%s,%s)''' # try: mycursor.execute(sqlin1,［"1001","500114233533332312"，"张三"，"20"，"重庆"，"18688888888","5"］) mycursor.execute(sqlin1,［"1002","500114233533332322","李四"，"25"，"成都"，"18688888888","10"］) mycursor.execute(sqlin1,［"1003","500114233533332332"，"王五"，"40"，"西藏"，"18688888888","3"］) myconn.commit() # except: print('插入数据失败') #第2个：景点表 sqlin2=''' insert into jdb values(%s,%s,%s,%s)''' try: mycursor.execute(sqlin2,［"CQ001"，"动物园"，"重庆主城"，"亲子游"］) mycursor.execute(sqlin2,［"CQ002"，"黔江濯水古镇"，"重庆黔江"，"周边游"］) mycursor.execute(sqlin2,［"CQ003"，"大足石刻"，"重庆大足"，"周边游"］) myconn.commit() except: print('插入数据失败') #第3个：项目表 sqlin3=''' insert into xmb values(%s,%s,%s,%s)''' try: mycursor.execute(sqlin3,［"DWY001"，"门票"，25，25］) mycursor.execute(sqlin3,［"ZS001"，"游船"，75，50］) myconn.commit() except: print('插入数据失败') #第4个：景点项目表 sqlin4=''' insert into jdxmb values(%s,%s)''' try: mycursor.execute(sqlin4,［"CQ001","DWY001"］) mycursor.execute(sqlin4,［"CQ001","DWY002"］) myconn.commit() except: print('插入数据失败') #第5个：游客旅游表 sqlin5=''' insert into yklyb values(%s,%s,%s,%s,%s,%s,%s,%s)''' mycursor.execute(sqlin5,［"yk0001","CQ001","DWY001","2023.5.1","100",4，"汽车"，"自驾"］) mycursor.execute(sqlin5,［"yk0001","CQ002","DWY001","2023.5.2","300",3，"火车+大巴"，"跟团"］) myconn.commit() (6) 查询数据。 myconn=pymysql.connect(host="127.0.0.1",user="wxn",password="123456",database='toursys') mycursor=myconn.cursor() #全表查询 sqlsel1=''' select * from ykb;''' mycursor.execute(sqlsel1) myconn.commit() #获取一条数据 result1=mycursor.fetchone() print(result1) #获取所有数据 data= mycursor.fetchall() print(data) #将数据转换为DataFrame frompandas importDataFrame df=DataFrame(data) print(df) (7) 删除数据库。 sql4="drop database toursys;" mycursor.execute(sql4) myconn.commit() (8) 关闭连接。 myconn.close() 在PyCharm中的输出结果如下，查询的是游客表的所有信息。 0123456 01001500114233533332312张三20重庆186888888885 11002500114233533332322李四25成都1868888888810 21003500114233533332332王五40西藏186888888883 在MySQL程序的执行过程中，输入“show databases”可以查看所有的数据库，输入“use toursys”可以选择toursys数据库，输入“select * from ykb”可以查看游客表的信息。数据库写入结果如图519所示。 图519数据库写入结果 5.4.6应用案例 案例实现效果： 爬取携程网上前5页的重庆景点信息(景点名称、景点地址、景点热度、点评分数、景点点评数)，以及每个景点的点评信息(用户名、点评内容、点评分数、点评时间、IP属地)，将这些信息插入MySQL数据库并对数据库进行查询。 由于爬取数据较多，因此需要先将爬取到的数据存储为CSV文件。为了便于初学者理解，又将CSV文件转换为XLSX文件，用Excel软件进行查看。然后创建数据库和数据表，读取XLSX文件中的数据并将其存入数据库，以此实现对数据库中的表信息进行查看。 1. 爬取数据 (1) 爬取景点信息。 爬取景点信息的代码如下。 import requests import time from selenium import webdriver from selenium.webdriver import ActionChains from selenium.webdriver.common.by import By from bs4 import BeautifulSoup if __name__=='__main__': #通过观察网页，生成景点信息前5页的网址 headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.54 Safari/537.36'} url_list=［］ for x in range(1，6): url='https://you.ctrip.com/sight/chongqing158/s0-p{0}.html#sightname'.format(x) url_list.append(url) #开始爬取景点信息 i=1 #将信息组合成字典，再存入列表，为存储做准备 list1=［］ for base_url inurl_list: print('========开始爬取第{0}页信息，共计10个景点========'.format(i)) #添加2s延时 time.sleep(2) res = requests.get(base_url, headers=headers) res.encoding = res.apparent_encoding soup = BeautifulSoup(res.text, 'lxml') res = soup.find_all(class_='list_mod2') for x in res: #景点名称 res1 = x.find_all('a') #景点地址 res2= x.find_all(class_='ellipsis') #景点热度 res3= x.find_all(class_='hot_score_number') #点评分数 res4= x.find_all(class_='score') #景点点评数 res5= x.find_all(class_='recomment') #景点链接 res6= x.find_all(class_='recomment') #显示信息 print('-'*30) print('景点链接：', res1［0］［'href'］) print('景点名称：',res1［1］.string.strip()) print('景点地址：',res2［0］.string.strip()) print('景点热度：',res3［0］.string.strip()) print('点评分数：',res4［0］.find_all('strong')［0］.string.strip().strip('()条点评')) print('景点点评数：',res5［0］.string.strip()) #将数据存入字典后，加入列表中 dist1 = {} dist1［'景点链接'］ = res1［0］［'href'］ dist1［'景点名称'］ = res1［1］.string.strip() dist1［'景点地址'］ = res2［0］.string.strip() dist1［'景点热度'］ = res3［0］.string.strip() dist1［'点评分数'］ = res4［0］.find_all('strong')［0］.string.strip() dist1［'景点点评数'］ = res5［0］.string.strip().strip('()条点评') time.sleep(0.4) list1.append(dist1) time.sleep(0.4) print('========第{0}页信息，爬取完成========'.format(i)) i=i+1 print('========景点信息前5页信息，爬取完成========'.format(i)) (2) 爬取景点评论。 爬取景点评论的代码如下。 list2=［］ for x in list1: base_url=x［'景点链接'］ jd_name=x［'景点名称'］ print("========开始爬取({0})景点的评论=====".format(jd_name)) options = webdriver.FirefoxOptions() #设置浏览器为headless 无界面模式 options.add_argument("--headless") options.add_argument("--disable-gpu") #打开浏览器处理，注意浏览器无显示 browser = webdriver.Firefox(options=options) browser.get(base_url) print("正在获取数据……请稍等……。") time.sleep(2) #循环获取5页评价 for x in range(1): print("第{0}页数据加载中，请稍等……".format(x+1)) time.sleep(5) print('--------正在获取第{0}页数据--------'.format(x+1)) url1=browser.current_url res=browser.page_source soup = BeautifulSoup(res, 'lxml') res = soup.find_all(class_='commentItem') print(len(res)) for x in res: #用户名 result1 = x.find_all(class_='userName') #评分 result2 = x.find_all(class_='averageScore') #评语 result3 = x.find_all(class_='commentDetail') #点评时间 l2 = x.find_all(class_='commentTime') # IP属地 l3 = x.find_all(class_='ipContent') print('用户名：', result1［0］.string, '点评时间：',l2［0］.text［0:10］, l3［0］.text, '评分：',result2［0］.text［0:3］ , '评语：',result3［0］.string) dist2={} dist2［'景点名称'］=jd_name dist2［'用户名'］=result1［0］.string dist2［'点评时间'］ = l2［0］.text［0:10］ dist2［'IP属地'］ = l3［0］.text［5:］ dist2［'评分'］ = result2［0］.text［0:3］ dist2［'评语'］ = result3［0］.string list2.append(dist2) #换页操作 #获取底部下一页 canzhao = browser.find_elements(By.CLASS_NAME, 'seotitle1') nextpage=browser.find_elements(By.CLASS_NAME,'ant-pagination-item-comment') time.sleep(2) #移动到元素element对象的"顶端"与当前窗口的"底部"对齐 browser.execute_script("arguments［0］.scrollIntoView(false);", canzhao［0］) time.sleep(2) #鼠标移至下一页 ActionChains(browser).move_to_element(nextpage［1］).perform() time.sleep(2) #鼠标单击下一页 nextpage［1］.click() time.sleep(4) #数据爬取完成，关闭浏览器 print('--------获取数据完成--------') time.sleep(4) browser.close() 2. 存储数据 (1) 将数据存储为CSV文件。 将数据存储为CSV文件的代码如下。 import pandas as pd #将数据保存到CSV文件中 df1 = pd.DataFrame(list1) df1.to_csv('景点数据表.csv) df2 = pd.DataFrame(list2) df2.to_csv('景点点评表.csv) print('----爬取的景点信息，存储到文件中----') (2) 将CSV文件转换为Excel文件。 将CSV文件转换为Excel文件的代码如下。 import pandas as pd jd=pd.read_csv('景点数据表.csv',index_col=False, encoding='utf-8') dp=pd.read_csv('景点点评表.csv',index_col=False, encoding='utf-8') #将 CSV 文件转换为 Excel 文件，并将数据保存在创建的表格 data 中 jd.index=jd.index+1 dp.index=dp.index+1 jd.to_excel('景点数据表.xlsx',index_label='景点编号'，columns=［'景点链接'，'景点名称'，'景点地址'，'景点热度'，'点评分数'，'景点点评数'］) dp.to_excel('景点点评表.xlsx',index_label='评价编号'，columns=［"景点名称"，"用户名"，"评价时间"，"IP属地"，"评分"，"评语"］) 3. 存入数据库 (1) 从XLSX文件中读取数据。 从XLSX文件中读取数据的代码如下。 import pandas as pd list1=pd.read_excel('景点数据表.xlsx') list2=pd.read_excel("景点点评表.xlsx") (2) 获取连接。 获取连接的代码如下。 #获取到database的连接对象 print('='*40) print('将爬取到的数据插入MySQL数据库') print('='*40) import pymysql myconn=pymysql.connect(host="127.0.0.1",user="wxn",password="123456") (3) 创建数据库。 创建数据库的代码如下。 #创建数据库 sql_crate='''create database if not exists toursys''' mycursor=myconn.cursor() mycursor.execute(sql_crate) #获取到database的连接对象 myconn=pymysql.connect(host="127.0.0.1",user="wxn",password="123456",database='toursys') (4) 创建数据表。 创建数据表的代码如下。 #创建数据表(景点信息表，景点点评表) sql1=''' create tableif not exists jdxxb(id int primary key auto_increment,jdname varchar(100),jdlj varchar(100),jdaddress varchar(100),jdhot varchar(20),jdscore varchar(20),jdnumber varchar(20))''' mycursor=myconn.cursor() mycursor.execute(sql1) sql2=''' create tableif not exists jddpb(id int primary key auto_increment,jdname varchar(100),pname varchar(100),content varchar(160),score varchar(20),dptime varchar(20),ip varchar(100))''' mycursor=myconn.cursor() mycursor.execute(sql2) (5) 插入数据。 插入数据的代码如下。 #将数据存入MySQL数据库 print('='*40) print('查看存入MySQL数据库的数据') print('='*40) #添加数据 sql3=''' insert into jdxxb(id,jdname,jdlj,jdaddress,jdhot,jdscore,jdnumber) values(Null,%s,%s,%s,%s,%s,%s)''' for x in range(len(list1［'景点名称'］)): try: mycursor.execute(sql3,［list1［'景点名称'］［x］,list1［'景点链接'］［x］,list1［'景点地址'］［x］,str(list1［'景点热度'］［x］),str(list1［'点评分数'］［x］),str(list1［'景点点评数'］［x］)］) print(list1［'景点名称'］［x］，'数据插入成功') myconn.commit() except: print(list1［'景点名称'］［x］,'==景点信息==插入数据失败') sql4=''' insert into jddpb(id,jdname,pname,content,score,dptime,ip) values(Null,%s,%s,%s,%s,%s,%s)''' for y in range(len(list2［'景点名称'］)): try: mycursor.execute(sql4,［list2［'景点名称'］［y］,list2［'用户名'］［y］,list2［'评语'］［y］,list2［'评分'］［y］,list2［'评价时间'］［y］,list2［'IP属地'］［y］］) print(list2［'景点名称'］［y］，'数据插入成功') myconn.commit() except: print(list2［'景点名称'］［y］,'--景点评价信息--插入数据失败') 4. 查询数据库 (1) 查询数据库中的景点信息。 查询数据库中的景点信息的代码如下。 sql5=''' select * from jdxxb;''' mycursor.execute(sql5) myconn.commit() #获取所有数据 data= mycursor.fetchall() print(data) 景点信息查询结果如图520所示，所有的景点信息输出成功。 图520景点信息查询结果 (2) 查询数据库中的评价信息。 查询数据库中的评价信息的代码如下。 sql6=''' select * from jddpb;''' mycursor.execute(sql6) myconn.commit() #获取所有数据 data1= mycursor.fetchall() print(data1) #关闭连接 myconn.close() 点评信息查询结果如图521所示。 图521点评信息查询结果