最近需要一些城市的環(huán)境污染指數(shù)做分析，在網(wǎng)上搜了一下牙捉，沒有完整的類型教程竹揍，在用百度和Google搜了幾天解決方案之后，終于把代碼寫完了邪铲，Python新手芬位，所以有些代碼會寫的比較啰嗦。
1. 導(dǎo)入所需庫文件
<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("以下數(shù)據(jù)為測試是否可以連接api")
print(pollution)
print("-"*150)
</code>
3.連接數(shù)據(jù)庫昧碉，填入數(shù)據(jù)庫的信息
<code>
db = pymysql.connect("hostname","database","password","username")
cursor = db.cursor()
r_id = None //主鍵賦值為空，在mysql中設(shè)置id自增
time_now = datetime.datetime.now()
//需要分析的城市存在列表中
cites = ['北京','天津','太原','石家莊','濟南',
'鄭州','南京','蘇州','上海','杭州',
'寧波','溫州','莆田','泉州','廈門',
'廣州','深圳','重慶','成都','紹興',
'呼和浩特','蘭州','貴陽','合肥','徐州',
'南通','常州','長沙','昆明','西安',
'汕頭','東莞','南寧','無錫','揚州',
'哈爾濱','長春','珠海','唐山','衡水',
]
</code>
4.以下是取出數(shù)據(jù)揽惹，并encode為utf8格式被饿，然后逐個存入數(shù)據(jù)庫中
<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') + "污染數(shù)據(jù)，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() //記住關(guān)閉連接
db.close()
</code>
