本人此前爲瞭解中國大陸地區的行政區劃信息,通過自己寫的Shell Script從中華人民共和國國家統計局網站抓取了相關數據,數據按省、市、縣、鄉、村五級存儲。但當時的數據源是2014年的,最新的統計數據是2015年的,於2016.07.27上傳。現準備重新抓取中國大陸地區 行政區劃 數據,爲此,對Shell Script, SQL Script代碼進行重構。

最新代碼已經上傳至GitHub

:舊版文檔見鏈接,舊版代碼見鏈接

Geographical Division Introduction

  • ROC 中華民國 Republic of China
  • PRC 中華人民共和國 People’s Republic of China

數據來自 中華人民共和國國家統計局 官網,具體頁面

中國大陸官方資料:目前有34個省級行政區,包括23個省、5個自治區、4個直轄市(包含臺灣省、香港特別行政區、澳門特別行政區)。但區劃代碼和城鄉劃分代碼不包括(臺灣省、香港特別行政區、澳門特別行政區)。

  • 中國大陸六大地理分區
Geographical Division Provices
1 華北 北京市、天津市、河北省、山西省、內蒙古自治區
2 東北 遼寧省、吉林省、黑龍江省
3 華東 上海市、江蘇省、浙江省、安徽省、福建省、江西省、山東省
4 中南 河南省、河北省、湖北省、廣東省、廣西壯族自治區、海南省
5 西南 重慶市、四川省、貴州省、雲南省、西藏自治區
6 西北 陝西省、甘肅省、青海省、寧夏回族自治區、新疆維吾爾族自治區

Rules Explanation

規則依據來自 統計用區劃代碼和城鄉劃分代碼編制規則

为规范统计用区划代码和城乡划分代码,建立各项普查、全面统计、抽样调查、专项调查统一使用的《统计用区划代码和城乡划分代码库》,特制定本规则。

統計用區劃代碼和城鄉劃分代碼分爲2段17

Postion Length Explanation
1~12 12 統計用區劃代碼
13~17 5 城鄉劃分代碼

Statistical Division Code

統計用區劃代碼共12位,行政區劃按行政級別共分爲5級。

Division Level Code Position Length English Explanation
省級 1~2 2 Province 包含省、直轄市、自治區
地級 3~4 2 City 各省、自治區的地級市,各直轄市的市轄區
縣級 5~6 3 Country 各直轄市的市轄區,各地級市所轄的區、市、縣
鄉級 7~9 3 Town 各街道辦事處或鄉鎮
村級 10~12 3 Village 各社區居委會 、各村委會

統計用區劃代碼基本長度爲12位,省、地、縣、鄉四級代碼不足12位用0補足。

县以上行政区划代码由1~6位代码组成。在统计工作中,各级统计部门不编制县以上行政区划代码,统一采用《中华人民共和国行政区划代码》国家标准。

县以下区划代码由7~12位代码组成,包括乡级代码和村级代码两部分。

Level Code Range Position Length Explanation
鄉級 001~099 7~9 3 街道
鄉級 100~199 7~9 3
鄉級 200~399 7~9 3
鄉級 400~599 7~9 3 類似鄉級單位 (民政部門未確認的開發區、工礦區、農場等)
村級 001~199 10~12 3 居民委員會
村級 200~399 10~12 3 村民委員會
村級 400~499 10~12 3 類似居民委員會(不含498代碼)
村級 500~599 10~12 3 類似村民委員會(不含598代碼)
村級 498 10~12 3 虛擬社區 (街道、鎮以及類似鄉級單位的開發區、科技園區、工業園區、工礦區、高校園區、科研機構園區等)
村級 598 10~12 3 虛擬生活區 (鄉以及類似鄉級單位的農、林、牧、漁場和其它農業活動區域)

凡民政部门确认的街道、镇、乡,按照国家标准《县级以下行政区划代码编制规则》(GB/T 10114—2003)编制,其乡级代码为001~399;民政部门未确认的开发区、工矿区、农场等类似乡级单位,乡级代码为400~599。

Urban And Rural Classification Code

城鄉劃分代碼共5位,含有 城鄉屬性城鄉分類 2個維度。

Postion Length Explanation
13~14 2 城鄉屬性代碼
15~17 3 城鄉分類代碼

Attributes

城乡属性代码由第13、14位代码组成。其中:第13位表示乡级属性,第14位表示村级属性。

乡级属性代码表示街道、镇、乡以及类似乡级单位的乡级属性。乡级属性代码用1~3数字表示。

村级属性代码表示居民委员会(社区)、村民委员会以及类似村级单位的村级属性。村级属性代码用1~9数字表示。

Level Code Num Position Length Explanation
鄉級 1 13 1 县级政府驻地
鄉級 2 13 1 连接的乡级区域
鄉級 3 13 1 其他乡级区域
村級 1 14 1 乡级政府驻地
村級 2 14 1 完全连接的村级地域
村級 3 14 1 部分连接的村级地域
村級 4 14 1 与其他区、市完全连接的村级地域
村級 5 14 1 与其他区、市部分连接的村级地域
村級 6 14 1 与其他镇完全连接的村级地域
村級 7 14 1 与其他镇部分连接的村级地域
村級 8 14 1 特殊地域
村級 9 14 1 其他村级地域

在城乡属性代码中,特殊地域仅指以下两种情况:

  • 在类似居委会中,常住人口达到或超过3000人的开发区、工矿区、大专院校、科研单位等居民生活区域。
  • 在类似村委会中,常住人口达到或超过3000人,且非农产业从业人员达到70%的农、林、牧、渔场及其他以农业活动为主的区域。
    当类似居委会、类似村委会不满足上述要求,也不满足代码1~7的编制要求时,村级属性一律编9

农、林、牧、渔场
农、林、牧、渔场场部的村级属性代码一律编1,与场部连接的下属生产单位,村级属性代码编23,与场部不连接的下属生产单位,村级属性代码编9。当常住人口达到3000人,非农产业从业人员达到70%时,不连接的下属生产单位的村级属性代码编8

只有一个村级单位的村级属性
凡乡级单位下只有一个村级单位,无论是实际存在还是虚拟的,其对应的村级属性代码一律编1

不连接的居民委员会
当居民委员会与政府驻地不连接时,如果有农业用地,村级属性代码编9;如果没有农业用地(或没有明确的地域),村级属性代码编8

Classification

城鄉分類代碼

通常由3位數字構成:

  • 首位是1表示 城鎮
  • 首位是2表示 鄉村

城乡分类代码由第15~17位代码组成。第15位为1,表示城镇;第15位为2,表示乡村。

Code Num Position Length Explanation
111 15~17 3 主城區
112 15~17 3 城鄉結合區
121 15~17 3 鎮中心區域
122 15~17 3 鎮鄉結合區
123 15~17 3 特殊區域
210 15~17 3 鄉中心區
220 15~17 3 村莊

Web Crawler Methods

通過命令curl抓取html頁面,通過命令sed去除HTML頁面中標籤。

直接使用curl抓取頁面,返回的數據出現中文亂碼,考慮是編碼問題。使用curl -s URL | grep charset返回的數據中有charset=gb2312,即頁面採用GB2312編碼。

解決方案是使用iconv命令進行編碼轉換,格式

1
iconv -f fromcode -t tocode [file ...]

使用命令

1
iconv -l | grep -Ei '^(gb|utf)'

返回的編碼集中有GB2312GBKUTF8UTF-8。經過測試源編碼可使用GB2312GBK,目標編碼可使用UTF8UTF-8

即使用curl -s URL | iconv -f GBK -t UTF-8

-s quiet靜默模式 –retry 重試次數 –retry-delay 間隔時間 -x 代理 -o保存路徑 ipaddr 代理IP port 代理端口

使用代理進行抓取操作,如 curl -s --retry 5 --retry-delay 5 -x ipaddr:port | iconv -f GBK -t UTF-8

使用sed -r '[email protected]<[^>]*>@@g;'去除HTML頁面中標籤。

Official Page Analysis

2015年统计用区划代码和城乡划分代码 相關頁面進行分析。

Specific Datas

  • 省級: <tr class='provincetr'></tr>
  • 地級: <tr class='citytr'></tr>
  • 縣級: <tr class='countytr'></tr>
  • 鄉級: <tr class='towntr'></tr>
  • 村級: <tr class='villagetr'></tr>

URL Analysis

homepage

首頁地址

1
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/index.html

province

省級列表地址

1
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13.html

13是省級區劃代碼,如河北省13,對應替換爲其它省份代碼

city

地級列表地址

1
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/1306.html

13是河北省,1306是保定市,格式是2位省份代碼/地級市代碼

country

縣級列表地址

1
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/130637.html

13是河北省,06在河北省下代表保定,130637是河北省保定市博野縣,格式是2位省份代碼/2位所屬地級市代碼/縣級代碼

town

鄉級列表地址

1
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/37/130637100.html

13是河北省,06在河北省下代表保定,s在保定下代表博野縣,130637100河北省保定市博野縣博野鎮,顯示具體村莊列表信息,格式是2位省份代碼/2位所屬地級市代碼/2位縣級代碼/鄉級代碼

Crawler Codes

index page

首頁地址: index.html

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/index.html | iconv -f GBK -t UTF-8 | grep "class='provincetr'" | sed -r "[email protected]'@@g;[email protected]</td>@\[email protected];[email protected](<a |href=|.html)@@g;[email protected]<[^>]*>@@g;[email protected]>@ @g;"

old code

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/index.html | iconv -f GBK -t UTF-8 | tr -d "'" | grep "class=provincetr" | sed -n '/<tr class=provincetr>/,/<\/tr>/ p' | sed -r '[email protected]<br/>@\[email protected];[email protected]</?(a|tr|td)>@@g;[email protected]<tr class=provincetr>@@g;[email protected](<a |href=|.html)@@g;[email protected]>@ @g;'

province page

省級列表地址: 13.html

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13.html  | iconv -f GBK -t UTF-8 | grep "class='citytr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;"

old code

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13.html | iconv -f GBK -t UTF-8 | tr -d "'" | grep "class=citytr" | sed -r '[email protected] [email protected]@g' |sed -n '/<tr>/,/<\/tr>/ p' | sed -r '[email protected]<tr>@\[email protected];[email protected]>@> @g;[email protected]<[^>]*>@@g;'

city page

地級列表地址: 13/1306.html

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/1306.html  | iconv -f GBK -t UTF-8 | grep "class='countytr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;"

old code

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/1306.html | iconv -f GBK -t UTF-8 | tr -d "'" | grep "class=countytr" | sed -r '[email protected] [email protected]@g' |sed -n '/<tr>/,/<\/tr>/ p' | sed -r '[email protected]<tr>@\[email protected];[email protected]>@> @g;[email protected]<[^>]*>@@g;'

country page

縣級列表地址: 13/06/130637.html

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/130637.html  | iconv -f GBK -t UTF-8 | grep "class='towntr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;"

old code

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/130637.html | iconv -f GBK -t UTF-8 | tr -d "'" | grep "class=towntr" | sed -r '[email protected] [email protected]@g' |sed -n '/<tr>/,/<\/tr>/ p' | sed -r '[email protected]<tr>@\[email protected];[email protected]>@> @g;[email protected]<[^>]*>@@g;'

town page

鄉級列表地址: 13/06/37/130637100.html

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/37/130637100.html  | iconv -f GBK -t UTF-8 | grep "class='villagetr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;"

old code

1
curl -s http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/13/06/37/130637100.html | iconv -f GBK -t UTF-8 | tr -d "'" | grep "class=villagetr" | sed -r '[email protected] [email protected]@g' |sed -n '/<tr>/,/<\/tr>/ p' | sed -r '[email protected]<tr>@\[email protected];[email protected]>@> @g;[email protected]<[^>]*>@@g;'

Code Design Architecture

代碼架構設計

按照 省級 –> 地級 –> 縣級 –> 鄉級 –> 村級 層級,逐級操作。

數據抓取通過代理IP進行,數據提取使用grep, sed
數據插入使用多條數據同時插入形式,以提高入庫效率。

共四個文件

1
2
3
4
5
6
7
8
9
[[email protected] chinaReginCode]$ tree
.
├── crawler.sh
├── impotProxyIP.sh
├── proxyList.txt
└── SQLDesign.sql

0 directories, 4 files
[[email protected] chinaReginCode]$

  • crawler.sh: 數據抓取腳本
  • impotProxyIP.sh: 導入代理IP腳本
  • proxyList.txt: 代理IP列表
  • SQLDesign.sql: 數據表設計

SQL Design

數據表設計,總共7張表,關聯表之間建有外鍵約束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
-- lempstacker
-- https://lempstacker.com
-- 2016.11.10 14:50 Thu Asia/Shanghai

-- Database chinaCode
-- table
-- proxy
-- province
-- city
-- country
-- town
-- urbanruralCode
-- village

-- 數據庫名 chinaCode
drop database if exists chinaCode;

create database if not exists chinaCode character set=utf8 collate=utf8_general_ci;

use chinaCode

-- Proxy代理
-- drop table if exists proxy;
create table if not exists proxy (
id mediumint unsigned not null auto_increment primary key comment '代理IP列表自增id',
ipaddr char(15) not null comment '代理IP地址',
port smallint unsigned not null comment '代理IP端口',
protocol char(10) comment 'HTTP類型',
anonymity varchar(20) comment '匿名等級',
country varchar(20) comment '代理IP所屬國家',
province varchar(20) comment '代理IP所屬省份地區',
city varchar(20) comment '代理IP所屬城市',
uptime varchar(10) comment '代理IP正常運行時間',
status enum('well','bad') default 'well' comment '代理IP狀態',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
unique key index_proxy_ipaddr_port (ipaddr,port)
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='代理IP列表';

-- alter table proxy add index index_proxy_id_ipaddr (id,ipaddr,port);
-- alter table proxy add unique index index_proxy_ipaddr_port (ipaddr,port);
-- drop index index_proxy_id_ipaddr on proxy;
-- drop index index_proxy_ipaddr_port on proxy;


-- province省、直轄市、自治區
-- drop table if exists province;
create table if not exists province (
id tinyint unsigned not null auto_increment primary key comment '省級列表自增id',
name char(30) not null comment '省份名稱',
nameRoc char(30) comment '省份名稱(正體)',
code bigint(12) unsigned not null comment '行政區劃代碼,共12位,由前2位指定',
region enum('華北','東北','華東','中南','西南','西北') not null comment '省所屬區域:1華北、2東北、3華東、4中南(華中,華南)、5西南、6西北',
abbr varchar(30) comment '省份簡稱或別稱',
abbrRoc varchar(30) comment '省份簡稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
key index_province_name_abbr (name,abbr),
key index_provinceRoc_name_abbr (nameRoc,abbrRoc),
unique key ind_code (code)
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸省份列表';

-- alter table province add index index_province_name_abbr (name,abbr);
-- alter table province add index index_provinceRoc_name_abbr (nameRoc,abbrRoc);
-- alter table province add index index_province_id_code (id,code);


-- city地市級
-- drop table if exists city;
create table if not exists city (
id mediumint unsigned not null auto_increment primary key comment '地級市列表自增id',
provinceId tinyint unsigned not null comment '省級id,對應表province,外鍵約束',
name char(60) not null comment '地級市名稱',
nameRoc char(60) comment '地級市名稱(正體)',
code bigint(12) unsigned not null comment '行政區劃代碼,共12位,由前4位指定',
abbr varchar(30) comment '地級市簡稱或別稱',
abbrRoc varchar(30) comment '地級市簡稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
key indCity_name_abbr (name(12),abbr),
key indCityRoc_name_abbr (nameRoc(12),abbrRoc),
key incCity_fkey_province (provinceId),
unique key indCity_code (code),
constraint fkey_province_city foreign key (provinceId) references province(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地級市列表';

-- alter table city add constraint fkey_province_city foreign key(provinceId) references province(id) on update cascade;


-- country縣級
-- drop table if exists country;
create table if not exists country (
id mediumint unsigned not null auto_increment primary key comment '縣級列表自增id',
cityId mediumint unsigned not null comment '地級市id,對應表city,外鍵約束',
name char(60) not null comment '縣級市名稱',
nameRoc char(60) comment '縣級市名稱(正體)',
code bigint(12) unsigned not null comment '行政區劃代碼,共12位,由前6位指定',
abbr varchar(30) comment '縣級市簡稱或別稱',
abbrRoc varchar(30) comment '縣級市簡稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
key in_country_name_abbr (name(12),abbr),
key in_country_roc_name_abbr (nameRoc(12),abbrRoc),
key in_country_fkey_city (cityId),
unique key in_country_code (code),
constraint fkey_city_coun foreign key (cityId) references city(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸縣級列表';


-- town鄉級
-- drop table if exists town;
create table if not exists town (
id int unsigned not null auto_increment primary key comment '鄉鎮列表自增id',
countryId mediumint unsigned not null comment '縣級市id,對應表country,外鍵約束',
name char(60) not null comment '鄉鎮名稱',
nameRoc char(60) comment '鄉鎮名稱(正體)',
code bigint(12) unsigned not null comment '行政區劃代碼,共12位,由前9位指定',
abbr varchar(30) comment '鄉鎮簡稱或別稱',
abbrRoc varchar(30) comment '鄉鎮簡稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
key in_town_name_abbr (name(12),abbr),
key in_town_roc_name_abbr (nameRoc(12),abbrRoc),
key in_town_fkey_coun (countryId),
unique key in_town_code (code),
constraint fkey_coun_town foreign key (countryId) references country(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸鄉鎮列表';

-- village村級
-- drop table if exists village;
create table if not exists village (
id int unsigned not null auto_increment primary key comment '村級列表自增id',
townId int unsigned not null comment '鄉鎮id,對應表town,外鍵約束',
name char(60) not null comment '村莊名稱',
nameRoc char(60) comment '村莊名稱(正體)',
urbanruralCode tinyint(3) unsigned not null comment '城鄉分類代碼,對應表urbanruralCode中code,不設外鍵約束',
code bigint(12) unsigned not null comment '行政區劃代碼,共12位,後3位是村莊代碼',
abbr varchar(30) comment '村莊簡稱或別稱',
abbrRoc varchar(30) comment '村莊簡稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
key in_village_name_abbr (name(12),abbr),
key in_village_roc_name_abbr (nameRoc(12),abbrRoc),
key in_village_urcode (urbanruralCode),
key in_village_fkey_town (townId),
unique key in_village_code (code),
constraint fkey_town_village foreign key (townId) references town(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸村级列表';

-- Urban and rural classification code 城鄉分類代碼
-- drop table if exists urbanruralCode;
create table if not exists urbanruralCode (
id tinyint unsigned not null auto_increment primary key comment '城鄉分類代碼自增id',
code tinyint unsigned not null comment '城鄉分類代碼,共3位',
name char(60) not null comment '城鄉分類代碼名稱',
nameRoc char(60) not null comment '城鄉分類代碼名稱(正體)',
createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
unique key indurc_code (code)
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸城鄉分類代碼表';

insert into urbanruralCode (code,name,nameRoc) values (111,'主城区','主城區'),(112,'城乡结合区','城鄉結合區'),(121,'镇中心区','鎮中心區域'),(122,'镇乡结合区','鎮鄉結合區'),(123,'特殊区域','特殊區域'),(210,'乡中心区','鄉中心區'),(220,'村庄','村莊');

impotProxyIP.sh

將同目錄下proxyList.txt文件中的數據導入數據庫

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#!/bin/bash
#lempstacker
#https://lempstacker.com
#2016.11.10 15:15 Thu Asia/Shanghai
#獲取代理IP列表並寫入數據庫
#代理IP來源 http://www.freeproxylists.net/

#Variables Setting
dbname='chinaCode'
file='./proxyList.txt';

sed -i '/^$/d;[email protected] [email protected]@g;[email protected] [email protected]@g;' $file
#createTime設置屬性timestamp default current_timestamp,無需手動指定入庫時間

mysql -e "truncate table $dbname.proxy;"

awk '{print $1,$2,$3,$4,$5,$8,$9,$10}' $file | while read line;do
# now=`date +'%Y-%m-%d %H:%M:%S'`
# now=`date +'%F %T'`
#存入數組
arr=(${line})
ipaddr=${arr[0]}
port=${arr[1]}
protocol=${arr[2]}
anonymity=${arr[3]}
country=${arr[4]}
region=${arr[5]}
city=${arr[6]}
uptime=${arr[7]}
mysql -e "insert into $dbname.proxy set ipaddr='$ipaddr',port='$port',protocol='$protocol',anonymity='$anonymity',country='$country',province='$region',city='$city',uptime='$uptime';"

done

mysql -e "select count(*) from $dbname.proxy;"

crawler.sh

數據抓取腳本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
#!/bin/bash
#lempstacker
#https://lempstacker.com
#2016.11.10 15:38 Thu Asia/Shanghai
#抓取數據入庫

dbname='chinaCode'
originUrl='http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2015/'

#curl參數 retry重試次數 retryDelay時間間隔
declare i retry=5
declare i retryDelay=5
# curl -s --retry $retry --retry-delay $retryDelay -x ipaddr:port

#iconv charset
charsetFrom='GBK'
charsetTo='UTF-8'

#獲取代理IP
function getProxyIP () {
temp=`mysql -Bse "select ipaddr,port from $dbname.proxy order by rand() limit 1;"`
arr=($temp)
ipaddr=${arr[0]}
port=${arr[1]}
echo $ipaddr:$port
}
#調用自定義函數getProxyIP
curltool="curl -s --retry $retry --retry-delay $retryDelay -x "`getProxyIP`

# set foreign_key_checks=0

# tr -s "\r\n" "\n" 用於去除符號 ^M

##抓取首頁省份列表
##查詢數據庫,表province中是否有數據,如果沒有則抓取網頁提取數據入庫

provinceCount=`mysql -Bse "select count(*) from $dbname.province;"`
if [[ $provinceCount -eq 0 ]]; then
mainPage=$originUrl'index.html' #拼接網頁
tempfile=`mktemp -t tempXXXXX.txt`
$curltool $mainPage | iconv -f $charsetFrom -t $charsetTo | grep "class='provincetr'" | sed -r "[email protected]'@@g;[email protected]</td>@\[email protected];[email protected](<a |href=|.html)@@g;[email protected]<[^>]*>@@g;[email protected]>@ @g" | tr -s "\r\n" "\n" > $tempfile

str='' #定義變量str,用於拼接字符串入庫
while read line; do
arr=($line)
pcode=${arr[0]}
pregion=${pcode:0:1}
pname=${arr[1]}
# mysql -Bse "insert into $dbname.province set name='"$pname"',code=$pcode,region=$pregion;" 2> /dev/null
#將插入數據拼接成字符串,提高插入效率
str=$str"('"$pname"',$pcode,$pregion),"
unset arr
unset pcode
unset pregion
unset pname
done < $tempfile
rm -f $tempfile
str=${str%,*} #刪除字符串尾部逗號,
mysql -Bse "insert into $dbname.province (name,code,region) values $str;" 2> /dev/null
unset str
unset mainPage
fi
unset provinceCount



##抓取各省地級市列表
##地級表city中查看字段provinceId是否含有表province中的數據,如果爲空開值抓取對應地級市數據

cityCount=`mysql -Bse "select count(a.id) from $dbname.city a join $dbname.province b on a.provinceId=b.id;"`
if [[ $cityCount -eq 0 ]]; then
tempfile=`mktemp -t tempXXXXX.txt`
mysql -Bse "select id,code,name from $dbname.province;" > $tempfile #id用於外鍵查選和入庫,code用於拼接網頁URL
while read line; do
arr=($line)
pid=${arr[0]}
pcode=${arr[1]}
pname=${arr[2]}
num=`mysql -Bse "select count(id) from $dbname.city where provinceId=$pid"`
if [[ $num -eq 0 ]]; then
tempfilecity=`mktemp -t tempXXXXX.txt`
provincePage=$originUrl$pcode'.html'
$curltool $provincePage | iconv -f $charsetFrom -t $charsetTo | grep "class='citytr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;" | tr -s "\r\n" "\n" > $tempfilecity
sed -i '/^$/d' $tempfilecity
unset provincePage

cstr=''
while read cityline; do
cityarr=($cityline)
ccode=${cityarr[0]}
cname=${cityarr[1]}
if [[ $cname == '县' ]]; then
cname=$pname' 县'
fi
cstr=$cstr"('"$cname"',$ccode,$pid),"
unset cityarr
unset ccode
unset cname
done < $tempfilecity
rm -f $tempfilecity

cstr=${cstr%,*} #刪除字符串尾部逗號,
mysql -Bse "insert into $dbname.city (name,code,provinceId) values $cstr;" 2> /dev/null
unset cstr
fi
unset arr
unset pid
unset pcode
unset pname

done < $tempfile
rm -f $tempfile
fi
unset cityCount


##抓取各地級市縣列表
##地級表city中查看字段cityId是否含有表city中的數據,如果爲空開值抓取對應縣級數據

countyCount=`mysql -Bse "select count(a.id) from $dbname.country a join $dbname.city b on a.cityId=b.id;"`
if [[ $countyCount -eq 0 ]]; then
tempfile=`mktemp -t tempXXXXX.txt`
mysql -Bse "select id,left(code,2),left(code,4),name from $dbname.city;" > $tempfile #id用於外鍵查選和入庫,code用於拼接網頁URL 截取前4爲 省2位、地級2位
while read line; do
arr=($line)
cityid=${arr[0]}
provinceCode=${arr[1]}
cityCode=${arr[2]}
cityName=${arr[3]}
num=`mysql -Bse "select count(id) from $dbname.country where cityId=$cityid"`
if [[ $num -eq 0 ]]; then
tempfilecountry=`mktemp -t tempXXXXX.txt`
cityPage=$originUrl$provinceCode'/'$cityCode'.html'
$curltool $cityPage | iconv -f $charsetFrom -t $charsetTo | grep "class='countytr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;" | tr -s "\r\n" "\n" > $tempfilecountry

sed -i '/^$/d' $tempfilecountry
unset cityPage
countryStr=''
while read countryline; do
countryarr=($countryline)
countryCode=${countryarr[0]}
countryName=${countryarr[1]}
if [[ $countryName == '市辖区' ]]; then
countryName=$cityName' 市辖区'
fi
countryStr=$countryStr"('"$countryName"',$countryCode,$cityid),"
unset countryarr
unset countryCode
unset countryName
done < $tempfilecountry
rm -f $tempfilecountry

countryStr=${countryStr%,*} #刪除字符串尾部逗號,
mysql -Bse "insert into $dbname.country (name,code,cityId) values $countryStr;" 2> /dev/null
unset countryStr
fi
unset arr
unset cityid
unset provinceCode
unset cityCode
unset cityName
done < $tempfile
rm -f $tempfile

fi
unset countyCount


##抓取各地縣的鄉鎮列表
##鄉級表country中查看字段countryId是否含有表Country中的數據, 如果爲空開值抓取對應縣級數據

townCount=`mysql -Bse "select count(a.id) from $dbname.town a join $dbname.country b on a.countryId=b.id;"`
if [[ $townCount -eq 0 ]]; then
tempfile=`mktemp -t tempXXXXX.txt`
mysql -Bse "select id,left(code,2),substring(code,3,2),left(code,6),name from $dbname.country;" > $tempfile #id用於外鍵查選和入庫,code用於拼接網頁URL 截取前4爲 省2位、地級2位
while read line; do
arr=($line)
countryid=${arr[0]}
provinceCode=${arr[1]}
cityCode=${arr[2]}
countryCode=${arr[3]}
countryName=${arr[4]}
num=`mysql -Bse "select count(id) from $dbname.town where countryId=$countryid"`
if [[ $num -eq 0 ]]; then
tempfileTown=`mktemp -t tempXXXXX.txt`
countryPage=$originUrl$provinceCode'/'$cityCode'/'$countryCode'.html'
$curltool $countryPage | iconv -f $charsetFrom -t $charsetTo | grep "class='towntr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;" | tr -s "\r\n" "\n" > $tempfileTown

sed -i '/^$/d' $tempfileTown
unset countryPage
townStr=''
while read townline; do
townarr=($townline)
townCode=${townarr[0]}
townName=${townarr[1]}
townStr=$townStr"('"$townName"',$townCode,$countryid),"
unset townarr
unset townCode
unset townName
done < $tempfileTown
rm -f $tempfileTown

townStr=${townStr%,*} #刪除字符串尾部逗號,
# echo $townStr
mysql -Bse "insert into $dbname.town (name,code,countryId) values $townStr;" 2> /dev/null
unset townStr
fi
unset arr
unset countryid
unset provinceCode
unset cityCode
unset countryCode
unset countryName
done < $tempfile
rm -f $tempfile

fi
unset townCount


##抓取各鄉鎮下的村列表
##村級表village中查看字段townId是否含有表town中的數據, 如果爲空開值抓取對應鄉級數據

villageCount=`mysql -Bse "select count(a.id) from $dbname.village a join $dbname.town b on a.townId=b.id;"`
if [[ $villageCount -eq 0 ]]; then
tempfile=`mktemp -t tempXXXXX.txt`
mysql -Bse "select id,left(code,2),substring(code,3,2),substring(code,5,2),left(code,9),name from $dbname.town;" > $tempfile #id用於外鍵查選和入庫,code用於拼接網頁URL 截取前6位 省2位、地級2位 縣級2位
while read line; do
arr=($line)
townid=${arr[0]}
provinceCode=${arr[1]}
cityCode=${arr[2]}
countryCode=${arr[3]}
townCode=${arr[4]}
townName=${arr[5]}
num=`mysql -Bse "select count(id) from $dbname.village where townId=$townid"`
if [[ $num -eq 0 ]]; then
tempfileVillage=`mktemp -t tempXXXXXX.txt`
townPage=$originUrl$provinceCode'/'$cityCode'/'$countryCode'/'$townCode'.html'
$curltool $townPage | iconv -f $charsetFrom -t $charsetTo | grep "class='villagetr'" | sed -r "[email protected]</tr>@\[email protected];[email protected]<[^>]*>@ @g;" | tr -s "\r\n" "\n" > $tempfileVillage

sed -i '/^$/d' $tempfileVillage
unset townPage
villageStr=''
while read villageline; do
villagearr=($villageline)
villageCode=${villagearr[0]}
urbanruralCode=${villagearr[1]}
villageName=${villagearr[2]}
villageStr=$villageStr"('"$villageName"',$villageCode,$urbanruralCode,$townid),"
unset villagearr
unset villageCode
unset villageName
done < $tempfileVillage
rm -f $tempfileVillage

villageStr=${villageStr%,*} #刪除字符串尾部逗號,

mysql -Bse "insert into $dbname.village (name,code,urbanruralCode,townId) values $villageStr;" 2> /dev/null
unset villageStr
fi
unset arr
unset townid
unset provinceCode
unset cityCode
unset countryCode
unset townCode
unset townName
done < $tempfile
rm -f $tempfile

fi
unset villageCount

Deploying Procedure

腳本部署步驟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 進入代碼目錄
cd chinaAdministrativeDivisionCode/sourceCode

# 將數據庫表結構導入數據庫 數據庫名(chinaCode)
mysql < SQLDesign.sql

# 將代理IP地址寫入文件 proxyList.txt

# 將代理IP導入導入數據庫
bash impotProxyIP.sh

# 執行腳本抓取網頁數據
setsid bash crawler.sh
# 或
nohup bash crawler.sh &

Data Presentation

數據展示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
MariaDB [chinaCode]> show tables;
+---------------------+
| Tables_in_chinaCode |
+---------------------+
| city |
| country |
| province |
| proxy |
| town |
| urbanruralCode |
| village |
+---------------------+
7 rows in set (0.00 sec)

MariaDB [chinaCode]> select count(*) from province;
+----------+
| count(*) |
+----------+
| 31 |
+----------+
1 row in set (0.02 sec)

MariaDB [chinaCode]> select count(*) from city;
+----------+
| count(*) |
+----------+
| 346 |
+----------+
1 row in set (0.01 sec)

MariaDB [chinaCode]> select count(*) from country;
+----------+
| count(*) |
+----------+
| 3129 |
+----------+
1 row in set (0.00 sec)

MariaDB [chinaCode]> select count(*) from town;
+----------+
| count(*) |
+----------+
| 39844 |
+----------+
1 row in set (0.01 sec)

MariaDB [chinaCode]> select count(*) from village;
+----------+
| count(*) |
+----------+
| 666356 |
+----------+
1 row in set (0.11 sec)

MariaDB [chinaCode]>

Problems

  • 文末^M出現,使用tr -s "\r\n" "\n"將其去除
  • Shell腳本中自定義函數必須先定義,而後才能調用,位置有先後。將函數返回值賦值給變量,可使用’function_name‘實現,反引號包裹函數名
  • MySQL中字符串截取,下標從1開始 substring(string,pos,len)

Change Log

  • 2016.03.14 00:01 Mon Asia/Beijing
    • 初稿完成
  • 2016.03.17 20:34 Thu Asia/Beijing
    • 數據表設計優化
  • 2016.11.11 09:10 Fri Asia/Shanghai
    • 數據源更新、數據表結構優化、代碼優化

  • Note Time: 2016.03.14 00:01 Mon
  • Note Location: Asia/Beijing
  • Writer: lempstacker