最近需要一些城市的环境污染指数做分析，在网上搜了一下，没有完整的类型教程，在用百度和Google搜了几天解决方案之后，终于把代码写完了，Python新手，所以有些代码会写的比较啰嗦。
1. 导入所需库文件
<code>
import urllib.parse
import urllib.request
from urllib.parseimportquote
import json
import pymysql
import datetime
import urllib
</code>
2. 连接API并测试该API是否可以连接，此处用的是阿里云的API
<code>
def getAndSaveJSON(sent):
host = 'http://jisuaqi.market.alicloudapi.com'
path = '/aqi/query'
method = 'GET'
text = quote(sent)
appcode = '你的APIKEY'
url = host + path + '?city=' + text + '&cityid=0'
request = urllib.request.Request(url)
request.add_header('Authorization', 'APPCODE ' + appcode)
rawData = urllib.request.urlopen(request)
jsonStr = rawData.read()
data = json.loads(jsonStr.decode('utf8'))
return data
def json_path(data,path,sep="."):
pp = path.split(sep)
tem = data
for p in pp:
if type(tem) is dict:
tem = tem[p]
elif type(tem) is list:
tem = tem[int(p)]
else:
tem = None
return tem
def getDetail(pollution,path,sep="."):
info = path.split(sep)
temp = pollution
for i in info:
if type(temp) is dict:
temp = temp[i]
elif type(temp) is list:
temp = temp[int(i)]
else:
temp = None
return temp
pollution = json_path(getAndSaveJSON("杭州"),"result")
print("以下数据为测试是否可以连接api")
print(pollution)
print("-"*150)
</code>
3.连接数据库，填入数据库的信息
<code>
db = pymysql.connect("hostname","database","password","username")
cursor = db.cursor()
r_id = None //主键赋值为空，在mysql中设置id自增
time_now = datetime.datetime.now()
//需要分析的城市存在列表中
cites = ['北京','天津','太原','石家庄','济南',
'郑州','南京','苏州','上海','杭州',
'宁波','温州','莆田','泉州','厦门',
'广州','深圳','重庆','成都','绍兴',
'呼和浩特','兰州','贵阳','合肥','徐州',
'南通','常州','长沙','昆明','西安',
'汕头','东莞','南宁','无锡','扬州',
'哈尔滨','长春','珠海','唐山','衡水',
]
</code>
4.以下是取出数据，并encode为utf8格式，然后逐个存入数据库中
<code>
for city in cites:
city_info = json_path(getAndSaveJSON(str(city)), "result")
cityId = getDetail(city_info, "cityid").encode('utf-8')
cityName = getDetail(city_info, "city").encode('utf-8')
so2 = getDetail(city_info, "so2").encode('utf-8')
so224 = getDetail(city_info, "so224").encode('utf-8')
no2 = getDetail(city_info, "no2").encode('utf-8')
no224 = getDetail(city_info, "no224").encode('utf-8')
co = getDetail(city_info, "co").encode('utf-8')
co24 = getDetail(city_info, "co24").encode('utf-8')
o3 = getDetail(city_info, "o3").encode('utf-8')
o324 = getDetail(city_info, "o324").encode('utf-8')
pm10 = getDetail(city_info, "pm10").encode('utf-8')
pm1024 = getDetail(city_info, "pm1024").encode('utf-8')
pm2_5 = getDetail(city_info, "pm2_5").encode('utf-8')
pm2_524 = getDetail(city_info, "pm2_524").encode('utf-8')
iso2 = getDetail(city_info, "iso2").encode('utf-8')
ino2 = getDetail(city_info, "ino2").encode('utf-8')
ipm10 = getDetail(city_info, "ipm10").encode('utf-8')
ipm2_5 = getDetail(city_info, "ipm2_5").encode('utf-8')
aqi = getDetail(city_info, "aqi").encode('utf-8')
primaryPollutant = getDetail(city_info, "primarypollutant").encode('utf-8')
quality = getDetail(city_info, "quality").encode('utf-8')
timePoint = getDetail(city_info, "timepoint").encode('utf-8')
level = getDetail(city_info, "aqiinfo.level").encode('utf-8')
color = getDetail(city_info, "aqiinfo.color").encode('utf-8')
affect = getDetail(city_info, "aqiinfo.affect").encode('utf-8')
measure = getDetail(city_info, "aqiinfo.measure").encode('utf-8')
print(cityName.decode('utf-8') + "污染数据，Get！")
city_table_name = "city_aqi_" + cityId.decode('utf-8')
tem_table = "template_city"
try:
if cursor.execute("SHOW TABLES LIKE " + repr(city_table_name)):
cursor.execute("INSERT INTO "+ city_table_name + " VALUES(" "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", (r_id,cityId,cityName,so2,so224,no2,no224,co,co24,o3,o324,pm10,pm1024,pm2_5,pm2_524,iso2,ino2, ipm10,ipm2_5,aqi,primaryPollutant,quality,timePoint,level,color,affect,measure,time_now))
else:
cursor.execute("CREATE TABLE IF NOT EXISTS " + city_table_name + " LIKE " + tem_table)
print(city_table_name + " has been created")
//先将该城市的id存入city_info这个表中，方便查询
cursor.execute("INSERT INTO info_city VALUES("
"%s,%s,%s,%s)",
(r_id,cityName,cityId,time_now))
cursor.execute("INSERT INTO "+ city_table_name + " VALUES(" "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
(r_id,cityId,cityName, so2, so224, no2,
no224, co, co24, o3, o324,
pm10, pm1024, pm2_5, pm2_524,iso2,
ino2,ipm10, ipm2_5, aqi, primaryPollutant,
quality, timePoint, level, color, affect,
measure,time_now))
rows = cursor.fetchall()
db.commit()
except Exception as e:
print(e)
db.rollback()
cursor.close() //记住关闭连接
db.close()
</code>
