该项目从联合国贸易网站爬取2020-2021年中国农产品交易记录，涉及Python编程、数据爬取、CSV存储、数据库整合、数据分析、图表绘制和邮件发送。使用requests库爬取数据，pandas进行数据处理，MariaDB存储，matplotlib绘图，通过邮件发送结果。遇到的问题包括请求限制和异常处理，通过设置延迟和异常重试解决。
一、项目描述
中国自古以来就是一个农业大国，广阔的土地上栽培的无数的农产品，这个项目是用来从联合国的数据里，爬取到各个国家农产品的交易记录，并且对这个数据进行分析，方便他人可以通过图表对我国农产品的进出口有个很清晰的了解。
二、项目的步骤
1、浏览分析联合国的贸易网站，查看网站接口api的文档
2、编写数据爬取的代码，使用requests库发起请求，将csv数据保存在本地
3、将csv文件使用pandas进行合并入库
4、对入库的数据进行分析，并绘制图表，然后将结果通过邮件发送给老师
三、涉及到的技术
python3.6，mariadb，centos
四、需求分析
1、爬取联合国各个农产品的交易数据
2、需要年份 2020-2021年 这个年份最好不要写死，以后可以更改，最好用配置文件去管理
3、保存成csv 放到data目录 这个data目录路径你要自己设计好
4、入库到数据库
5、绘制图表，并将结果发送给老师
五、代码实现
1、新建一个config配置文件，可以用来随时更改年份、农产品的种类、出口国、进口国、以及后面要请求的URL，和要连接的数据库等
# 保存项目的配置
#如果需要获取别的年份，直接修改如下配置
#start_year 规定开始年份
#end_year 规定结束年份
start_year = 2020
end_year = 2021
#要获取的产品
#其他农产品的id可以去网址查询，此项目以大米来举例说明
Products = "rice"
Products_id = 1006
URL = f"https://comtrade.un.org/api/get/plus?max=10000&type=C&freq=A&px=HS&p=all&rg=all&fmt=csv"
#sqlalchmey 连接数据库url
#什么数据库+什么方式连接://用户名:密码@主机ip地址/连接的数据库？字符编码
DBURL = "mysql+pymysql://sc:123456@192.168.243.128/sc?charset=utf8"
2、新建一个country文件，里面存放了所有国家和国家对应的id
country = [
{
"id": "all",
"text": "All"
},
{
"id": "4",
"text": "Afghanistan"
},
{
"id": "8",
"text": "Albania"
},
{
"id": "12",
"text": "Algeria"
},
{
"id": "20",
"text": "Andorra"
},
{
"id": "24",
"text": "Angola"
},
{
"id": "660",
"text": "Anguilla"
},
{
"id": "28",
"text": "Antigua and Barbuda"
},
{
"id": "32",
"text": "Argentina"
},
{
"id": "51",
"text": "Armenia"
},
{
"id": "533",
"text": "Aruba"
},
{
"id": "36",
"text": "Australia"
},
{
"id": "40",
"text": "Austria"
},
{
"id": "31",
"text": "Azerbaijan"
},
{
"id": "44",
"text": "Bahamas"
},
{
"id": "48",
"text": "Bahrain"
},
{
"id": "50",
"text": "Bangladesh"
},
{
"id": "52",
"text": "Barbados"
},
{
"id": "112",
"text": "Belarus"
},
{
"id": "56",
"text": "Belgium"
},
{
"id": "58",
"text": "Belgium-Luxembourg"
},
{
"id": "84",
"text": "Belize"
},
{
"id": "204",
"text": "Benin"
},
{
"id": "60",
"text": "Bermuda"
},
{
"id": "64",
"text": "Bhutan"
},
{
"id": "68",
"text": "Bolivia (Plurinational State of)"
},
{
"id": "535",
"text": "Bonaire"
},
{
"id": "70",
"text": "Bosnia Herzegovina"
},
{
"id": "72",
"text": "Botswana"
},
{
"id": "92",
"text": "Br. Virgin Isds"
},
{
"id": "76",
"text": "Brazil"
},
{
"id": "96",
"text": "Brunei Darussalam"
},
{
"id": "100",
"text": "Bulgaria"
},
{
"id": "854",
"text": "Burkina Faso"
},
{
"id": "108",
"text": "Burundi"
},
{
"id": "132",
"text": "Cabo Verde"
},
{
"id": "116",
"text": "Cambodia"
},
{
"id": "120",
"text": "Cameroon"
},
{
"id": "124",
"text": "Canada"
},
{
"id": "136",
"text": "Cayman Isds"
},
{
"id": "140",
"text": "Central African Rep."
},
{
"id": "148",
"text": "Chad"
},
{
"id": "152",
"text": "Chile"
},
{
"id": "156",
"text": "China"
},
{
"id": "344",
"text": "China, Hong Kong SAR"
},
{
"id": "446",
"text": "China, Macao SAR"
},
{
"id": "170",
"text": "Colombia"
},
{
"id": "174",
"text": "Comoros"
},
{
"id": "178",
"text": "Congo"
},
{
"id": "184",
"text": "Cook Isds"
},
{
"id": "188",
"text": "Costa Rica"
},
{
"id": "384",
"text": "Côte d'Ivoire"
},
{
"id": "191",
"text": "Croatia"
},
{
"id": "192",
"text": "Cuba"
},
{
"id": "531",
"text": "Curaçao"
},
{
"id": "196",
"text": "Cyprus"
},
{
"id": "203",
"text": "Czechia"
},
{
"id": "200",
"text": "Czechoslovakia"
},
{
"id": "408",
"text": "Dem. People's Rep. of Korea"
},
{
"id": "180",
"text": "Dem. Rep. of the Congo"
},
{
"id": "208",
"text": "Denmark"
},
{
"id": "262",
"text": "Djibouti"
},
{
"id": "212",
"text": "Dominica"
},
{
"id": "214",
"text": "Dominican Rep."
},
{
"id": "588",
"text": "East and West Pakistan"
},
{
"id": "218",
"text": "Ecuador"
},
{
"id": "818",
"text": "Egypt"
},
{
"id": "222",
"text": "El Salvador"
},
{
"id": "226",
"text": "Equatorial Guinea"
},
{
"id": "232",
"text": "Eritrea"
},
{
"id": "233",
"text": "Estonia"
},
{
"id": "231",
"text": "Ethiopia"
},
{
"id": "97",
"text": "EU-28"
},
{
"id": "234",
"text": "Faeroe Isds"
},
{
"id": "238",
"text": "Falkland Isds (Malvinas)"
},
{
"id": "242",
"text": "Fiji"
},
{
"id": "246",
"text": "Finland"
},
{
"id": "886",
"text": "Fmr Arab Rep. of Yemen"
},
{
"id": "278",
"text": "Fmr Dem. Rep. of Germany"
},
{
"id": "866",
"text": "Fmr Dem. Rep. of Vietnam"
},
{
"id": "720",
"text": "Fmr Dem. Yemen"
},
{
"id": "230",
"text": "Fmr Ethiopia"
},
{
"id": "280",
"text": "Fmr Fed. Rep. of Germany"
},
{
"id": "582",
"text": "Fmr Pacific Isds"
},
{
"id": "590",
"text": "Fmr Panama, excl.Canal Zone"
},
{
"id": "592",
"text": "Fmr Panama-Canal-Zone"
},
{
"id": "868",
"text": "Fmr Rep. of Vietnam"
},
{
"id": "717",
"text": "Fmr Rhodesia Nyas"
},
{
"id": "736",
"text": "Fmr Sudan"
},
{
"id": "835",
"text": "Fmr Tanganyika"
},
{
"id": "810",
"text": "Fmr USSR"
},
{
"id": "890",
"text": "Fmr Yugoslavia"
},
{
"id": "836",
"text": "Fmr Zanzibar and Pemba Isd"
},
{
"id": "251",
"text": "France"
},
{
"id": "254",
"text": "French Guiana"
},
{
"id": "258",
"text": "French Polynesia"
},
{
"id": "583",
"text": "FS Micronesia"
},
{
"id": "266",
"text": "Gabon"
},
{
"id": "270",
"text": "Gambia"
},
{
"id": "268",
"text": "Georgia"
},
{
"id": "276",
"text": "Germany"
},
{
"id": "288",
"text": "Ghana"
},
{
"id": "292",
"text": "Gibraltar"
},
{
"id": "300",
"text": "Greece"
},
{
"id": "304",
"text": "Greenland"
},
{
"id": "308",
"text": "Grenada"
},
{
"id": "312",
"text": "Guadeloupe"
},
{
"id": "320",
"text": "Guatemala"
},
{
"id": "324",
"text": "Guinea"
},
{
"id": "624",
"text": "Guinea-Bissau"
},
{
"id": "328",
"text": "Guyana"
},
{
"id": "332",
"text": "Haiti"
},
{
"id": "336",
"text": "Holy See (Vatican City State)"
},
{
"id": "340",
"text": "Honduras"
},
{
"id": "348",
"text": "Hungary"
},
{
"id": "352",
"text": "Iceland"
},
{
"id": "699",
"text": "India"
},
{
"id": "356",
"text": "India, excl. Sikkim"
},
{
"id": "360",
"text": "Indonesia"
},
{
"id": "364",
"text": "Iran"
},
{
"id": "368",
"text": "Iraq"
},
{
"id": "372",
"text": "Ireland"
},
{
"id": "376",
"text": "Israel"
},
{
"id": "381",
"text": "Italy"
},
{
"id": "388",
"text": "Jamaica"
},
{
"id": "392",
"text": "Japan"
},
{
"id": "400",
"text": "Jordan"
},
{
"id": "398",
"text": "Kazakhstan"
},
{
"id": "404",
"text": "Kenya"
},
{
"id": "296",
"text": "Kiribati"
},
{
"id": "414",
"text": "Kuwait"
},
{
"id": "417",
"text": "Kyrgyzstan"
},
{
"id": "418",
"text": "Lao People's Dem. Rep."
},
{
"id": "428",
"text": "Latvia"
},
{
"id": "422",
"text": "Lebanon"
},
{
"id": "426",
"text": "Lesotho"
},
{
"id": "430",
"text": "Liberia"
},
{
"id": "434",
"text": "Libya"
},
{
"id": "440",
"text": "Lithuania"
},
{
"id": "442",
"text": "Luxembourg"
},
{
"id": "450",
"text": "Madagascar"
},
{
"id": "454",
"text": "Malawi"
},
{
"id": "458",
"text": "Malaysia"
},
{
"id": "462",
"text": "Maldives"
},
{
"id": "466",
"text": "Mali"
},
{
"id": "470",
"text": "Malta"
},
{
"id": "584",
"text": "Marshall Isds"
},
{
"id": "474",
"text": "Martinique"
},
{
"id": "478",
"text": "Mauritania"
},
{
"id": "480",
"text": "Mauritius"
},
{
"id": "175",
"text": "Mayotte"
},
{
"id": "484",
"text": "Mexico"
},
{
"id": "496",
"text": "Mongolia"
},
{
"id": "499",
"text": "Montenegro"
},
{
"id": "500",
"text": "Montserrat"
},
{
"id": "504",
"text": "Morocco"
},
{
"id": "508",
"text": "Mozambique"
},
{
"id": "104",
"text": "Myanmar"
},
{
"id": "580",
"text": "N. Mariana Isds"
},
{
"id": "516",
"text": "Namibia"
},
{
"id": "524",
"text": "Nepal"
},
{
"id": "530",
"text": "Neth. Antilles"
},
{
"id": "532",
"text": "Neth. Antilles and Aruba"
},
{
"id": "528",
"text": "Netherlands"
},
{
"id": "540",
"text": "New Caledonia"
},
{
"id": "554",
"text": "New Zealand"
},
{
"id": "558",
"text": "Nicaragua"
},
{
"id": "562",
"text": "Niger"
},
{
"id": "566",
"text": "Nigeria"
},
{
"id": "579",
"text": "Norway"
},
{
"id": "512",
"text": "Oman"
},
{
"id": "490",
"text": "Other Asia, nes"
},
{
"id": "586",
"text": "Pakistan"
},
{
"id": "585",
"text": "Palau"
},
{
"id": "591",
"text": "Panama"
},
{
"id": "598",
"text": "Papua New Guinea"
},
{
"id": "600",
"text": "Paraguay"
},
{
"id": "459",
"text": "Peninsula Malaysia"
},
{
"id": "604",
"text": "Peru"
},
{
"id": "608",
"text": "Philippines"
},
{
"id": "616",
"text": "Poland"
},
{
"id": "620",
"text": "Portugal"
},
{
"id": "634",
"text": "Qatar"
},
{
"id": "410",
"text": "Rep. of Korea"
},
{
"id": "498",
"text": "Rep. of Moldova"
},
{
"id": "638",
"text": "Réunion"
},
{
"id": "642",
"text": "Romania"
},
{
"id": "643",
"text": "Russian Federation"
},
{
"id": "646",
"text": "Rwanda"
},
{
"id": "647",
"text": "Ryukyu Isd"
},
{
"id": "461",
"text": "Sabah"
},
{
"id": "652",
"text": "Saint Barthelemy"
},
{
"id": "654",
"text": "Saint Helena"
},
{
"id": "659",
"text": "Saint Kitts and Nevis"
},
{
"id": "658",
"text": "Saint Kitts, Nevis and Anguilla"
},
{
"id": "662",
"text": "Saint Lucia"
},
{
"id": "534",
"text": "Saint Maarten"
},
{
"id": "666",
"text": "Saint Pierre and Miquelon"
},
{
"id": "670",
"text": "Saint Vincent and the Grenadines"
},
{
"id": "882",
"text": "Samoa"
},
{
"id": "674",
"text": "San Marino"
},
{
"id": "678",
"text": "Sao Tome and Principe"
},
{
"id": "457",
"text": "Sarawak"
},
{
"id": "682",
"text": "Saudi Arabia"
},
{
"id": "686",
"text": "Senegal"
},
{
"id": "688",
"text": "Serbia"
},
{
"id": "891",
"text": "Serbia and Montenegro"
},
{
"id": "690",
"text": "Seychelles"
},
{
"id": "694",
"text": "Sierra Leone"
},
{
"id": "702",
"text": "Singapore"
},
{
"id": "703",
"text": "Slovakia"
},
{
"id": "705",
"text": "Slovenia"
},
{
"id": "711",
"text": "So. African Customs Union"
},
{
"id": "90",
"text": "Solomon Isds"
},
{
"id": "706",
"text": "Somalia"
},
{
"id": "710",
"text": "South Africa"
},
{
"id": "728",
"text": "South Sudan"
},
{
"id": "724",
"text": "Spain"
},
{
"id": "144",
"text": "Sri Lanka"
},
{
"id": "275",
"text": "State of Palestine"
},
{
"id": "729",
"text": "Sudan"
},
{
"id": "740",
"text": "Suriname"
},
{
"id": "748",
"text": "Eswatini"
},
{
"id": "752",
"text": "Sweden"
},
{
"id": "757",
"text": "Switzerland"
},
{
"id": "760",
"text": "Syria"
},
{
"id": "762",
"text": "Tajikistan"
},
{
"id": "807",
"text": "North Macedonia"
},
{
"id": "764",
"text": "Thailand"
},
{
"id": "626",
"text": "Timor-Leste"
},
{
"id": "768",
"text": "Togo"
},
{
"id": "772",
"text": "Tokelau"
},
{
"id": "795",
"text": "Turkmenistan"
},
{
"id": "796",
"text": "Turks and Caicos Isds"
},
{
"id": "798",
"text": "Tuvalu"
},
{
"id": "800",
"text": "Uganda"
},
{
"id": "804",
"text": "Ukraine"
},
{
"id": "858",
"text": "Uruguay"
},
{
"id": "850",
"text": "US Virgin Isds"
},
{
"id": "842",
"text": "USA"
},
{
"id": "841",
"text": "USA (before 1981)"
},
{
"id": "548",
"text": "Vanuatu"
},
{
"id": "862",
"text": "Venezuela"
},
{
"id": "704",
"text": "Viet Nam"
},
{
"id": "975",
"text": "ASEAN"
}]
3、新建一个request_data文件，可以用来存放获取数据的代码，同时还要在同目录下新建一个data目录用来存放请求到的数据；
#导入第三方请求库
import requests
#导入刚刚自己定义的国家模块，因为后面要用到国家模块里面的id
import country
#导入自己定义的配置文件库
import config
#导入文件处理os库
import os
#导入time时间处理的库
import time
#定义一个函数，检查文件夹是否存在，不存在就创建
def dir_check(dir_path):
if not os.path.exists(dir_path):
os.makedirs(dir_path)
# 通过接口请求获取相应的信息，然后保存在当前data目录下，要在当前目录下新建一个data目录
#定义一个函数，用来去请求数据，并将数据保存在文件夹里
def download(full_url,file_path):
# 请求接口 -- 异常处理，重试3次，睡个2秒再去重试
for i in range(3):
try:
result = requests.get(full_url)
if result.status_code == 200:
with open(file_path,"w+",encoding="utf-8")as fp:
fp.write(result.text)
break;
except:
print("请求失败，重新发起请求")
time.sleep(2)
if __name__=="__main__":
#判断数据目录存不存在，不存在就调用dir_check函数去创建，创建的目录以产品的名称来命名
dir_path = "data/"+config.Products
dir_check(dir_path)
#在配置文件里规定好起始年份和结束年份，循环得到所有年份
for year in range(config.start_year,config.end_year+1):
#创建年份文件夹
year_path = dir_path + "/" + str(year)
dir_check(year_path)
#循环所有国家，每次循环得到一个字典，用state接收
for state in country.country[1:]:
print(f"正在下载{year}年，{state['text']}国家的数据")
#将字典里id的值赋给state_id
state_id = state["id"]
#使用字符串拼接得到最终请求的url，\是续航符
full_url=config.URL+"&ps="+str(year)+"&r="+state_id + \
"&cc=" + str(config.Products_id)
#使用字符串拼接，得到年份下面以国家命名的.csv结尾的文件
file_path = year_path + "/" + state["text"] + ".csv"
# 如果请求过就不需要重新请求了
if os.path.exists(file_path):
print("此文件已经存在，不需要重新请求")
else:
download(full_url,file_path)
# 一个小时只有100次请求，控制请求频率，请求一下 sleep一下
time.sleep(40)
运行request文件里的代码，就会得到数据，等待所有数据爬取完成，就是这个界面（我这里2020年的数据没有爬取完）
4、新建一个insert_mysql的文件，将爬取到的数据存到自己的数据库里面去；
#导入 pymysql模块
import pymysql
#导入pandas数据库叫做pd
import pandas as pd
#从sqlalchemy模块导入create_engine函数
from sqlalchemy import create_engine
#导入文件处理os模块
import os
#从config模块里导入DBURL,start_year, end_year, Products
from config import DBURL, start_year, end_year, Products
#用pymysql模块的方法连接到数据库
conn = pymysql.connect(
host='192.168.243.128',#主机的ip地址
user='sc',#用户名
password='123456',#用户密码
database='sc',#连接的数据库的名称
charset='utf8'#字符编码格式
)
#创建连接数据库引擎
engine = create_engine(DBURL)
#按年份存入数据库
dir_path = "date/"+ Products
for year in range(start_year,end_year+1):
#按年存入数据库
#将每一年的csv都读取到一个大的dataframe里面，然后使用to_sql入库
df = pd.DataFrame()
#得到年份目录的路径
year_path = os.path.join(dir_path,str(year))
#循环得到年份目录下每一个以.csv结尾的国家的文件
for filename in os.listdir(year_path):
#使用os的拼接方法，得到文件的路径
file_path = os.path.join(year_path,filename)
#使用pandas将每个文件都读取出来
tmp_df = pd.read_csv(file_path)
#将每个小的dataframe合并成一个大的dataframe
df = df.append(tmp_df,ignore_index=False)
#用pandas处理表格数据，筛选出年份不为空的数据
df = df[df["Period"].notnull()]
#将最终筛选出来的df表格插入到数据库里
df.to_sql("union_table",engine,if_exists="append",index=False)
查看一下插入前的数据库
查看执行了代码后，插入后的数据库
5、新建一个analysis文件，用来分析数据，并绘图
#分析2021年中国与其他国家大米交易量前三的国家
#使用pandas matplotlib作图
import pymysql
import pandas as pd
#连接数据库
db = pymysql.connect(
host = "192.168.243.128", #mysql主机ip
user = "sc", #用户名
passwd = "123456", #密码
database = "sc" #数据库
)
#获取china 2021年对所有国家的交易数据
df = pd.read_sql("select * from union_table where Reporter='China' and Year='2021';", con=db)
#与中国进行大米交易前三的国家数据 Partner得到的国家就成为这个dataframe 的index了
result = df.groupby('Partner').sum().sort_values(by="Netweight (kg)", ascending=False).iloc[1:4, :]
#sql语句 直接查询
#select Partner, sum(Qty) as nw from union_table where Reporter='China' and Year='2021' group by Partner order by nw desc limit 5;
#把前三的国家 交易数据放入result_df
result_df = df[df['Partner'].isin(result.index) ]
#把Partner作为result_df索引
result_df.set_index(['Partner'], inplace=True)
#筛选交易类型和交易量
result_df = result_df[['Trade Flow','Netweight (kg)']]
#s1是所有出口国家
#s2是所有进口国家
s1 = result_df[result_df["Trade Flow"] == "X"]['Netweight (kg)']
s2 = result_df[result_df["Trade Flow"] == "M"]['Netweight (kg)']
#将s1，s2，俩个serise合并成一个dataframe，并且他们的列名称就是"export","import"
rdf = pd.concat([s1,s2], axis=1)
rdf.columns = ["export","import"]
# #画图
import matplotlib.pyplot as plt
#对于dataframe，matplotlib作图工具，会自动的将列名称作为柱状图的x轴下标名称
#会自动的将每一行的数据生成一个柱状图
rdf.plot.bar()
#让柱状图的下标可以自由的旋转，达到合适的效果
plt.xticks(rotation=360)
# fontproperties='simhei'，让python画图支持中文格式
plt.title(f"2021-大米进出口总量前三排名",fontproperties='simhei')
#将图片保存在当前目录1.png里面
plt.savefig('1.png')
plt.show()
双击显示柱状图
6、新建一个sendmail的文件，用来将最后的图片发送老师的邮箱
#邮件协议
#smtp协议 发邮件的协议 默认端口25号
#pop3 收邮件的协议 默认端口110
#imap 收邮件的协议 默认端口143
#pop3和imap都是收邮件的协议，区别在于
#pop3在客户端操作不会反馈到服务器
#imap在客户端的操作会反馈到服务器
163.com邮箱的授权码
#safdsdfsad（这里保存好自己的授权码）
#python里两个模块发邮件
#1、smtplib 发邮件
#2、email 构建邮件内容
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
#设置第三方发邮件 SMTP服务
mail_host = "smtp.163.com" #设置服务器
mail_user = "123456@163.com" #用户名
mail_pass = "saasfd" #授权码，填自己正确的授权码
receivers = "123456@163.com" #接收邮箱，可以自己给自己发邮件
#创建一个实例
msg = MIMEMultipart()
#设置标题的一些主题，邮件来自和邮件发送
msg['Subject'] = 'lhj'
msg['From'] = mail_user
msg['To'] = receivers
#给邮件加入内容
message = MIMEText('lhj')
msg.attach(message)
#发送附件1.png图片
message = MIMEApplication(open('1.png','rb').read())
message.add_header('Content-Disposition','attachment',filename='1.png')
msg.attach(message)
try:
#创建一个实例
s = smtplib.SMTP()
#创建连接
s.connect(mail_host)
#登录，mail_user是用户名，mail_pass是授权码
s.login(mail_user,mail_pass)
#发送邮件，第一个参数是谁发送的，第二个参数是发送给谁的，后面的是将内容变成string
s.sendmail(mail_user,receivers,msg.as_string())
except Exception as e:
print(e)
s.close()
六、遇到的问题
1、一个小时只有100次请求
解决：每次请求采用time库的sleep方法，睡个40秒
2、程序错误结束时，开始下一次请求时，请求过就不需要重新请求
解决：采用一个循环，结合os库的方法，判断文件路径是否存在，如果存在就不去url请求数据了
3、请求接口时，接口发生异常，
解决：采用异常处理，捕获异常，用循环重试3次，睡个2秒再去重试
扫一扫
2376
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
