python爬取综合信息需要的数据库表以及天气配置表

python爬取综合信息需要的数据库表以及天气配置表

天气表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for weather

---

DROP TABLE IF EXISTS weather;
CREATE TABLE weather (
weather_id int(0) NOT NULL AUTO_INCREMENT COMMENT ‘id’,
province varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘城市名称’,
province_alias varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘省份拼音’,
type int(0) NULL DEFAULT NULL COMMENT ‘0:省份级 1：地市级’,
state1 int(0) NULL DEFAULT NULL COMMENT ‘天气状态’,
state2 int(0) NULL DEFAULT NULL COMMENT ‘天气状态’,
state_detailed varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘天气中文描述’,
wind_state varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘风力，风向’,
create_time timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ‘创建时间’,
PRIMARY KEY (weather_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 38 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘天气情况（每两小时更新一次）’ ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

2综合信息表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for comprehensive

---

DROP TABLE IF EXISTS comprehensive;
CREATE TABLE comprehensive (
id int(0) NOT NULL AUTO_INCREMENT COMMENT ‘id(主键）’,
address varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘地址’,
zq_tm datetime(0) NULL DEFAULT NULL COMMENT ‘抓取时间’,
zq_title varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘抓取标题’,
zq_content varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘抓取内容’,
zq_type int(0) NULL DEFAULT NULL COMMENT ‘抓取类型（1：天气（weather表）；2：气象新闻；3：地震信息,5:气象预警，4:热点新闻）’,
zq_temp varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘温度（天气的温度区间）’,
zq_weather_type varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘天气类型（多云、小雨、晴等等）’,
magnitude varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘震级’,
lgtd varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘经度’,
lttd varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘纬度’,
eq_time datetime(0) NULL DEFAULT NULL COMMENT ‘事件发生/播报时间’,
eq_length varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘震源深度’,
warning_level varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘预警等级’,
warning_type varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘预警类型’,
city varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘城市’,
broad_state int(0) NULL DEFAULT 0 COMMENT ‘播报状态 0：未播报 1：已播报’,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1377 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘地震,气象,新闻,气象新闻’ ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

3.降雨量表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for rain

---

DROP TABLE IF EXISTS rain;
CREATE TABLE rain (
rain_id int(0) NOT NULL AUTO_INCREMENT,
time varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘时间’,
create_time datetime(0) NULL DEFAULT NULL COMMENT ‘创建时间’,
pname varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘省份名称’,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘城市名称’,
rain_num double NULL DEFAULT NULL COMMENT ‘降雨量(单位:mm)’,
broad_state int(0) NULL DEFAULT 0 COMMENT ‘播报状态 0：未播报 1：已播报’,
PRIMARY KEY (rain_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 270 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘降雨量top30（每小时更新一次）’ ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

4.天气配置

[
{
“url”:“http://flash.weather.com.cn/wmaps/xml/china.xml”,
“type”:“0”,
“provinceName”:“全国”,
“value”:37,
“pname”:“china”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/beijing.xml”,
“type”:“1”,
“provinceName”:“北京”,
“value”:15,
“pname”:“beijing”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/shanghai.xml”,
“type”:“1”,
“provinceName”:“上海”,
“value”:10,
“pname”:“shanghai”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/tianjin.xml”,
“type”:“1”,
“provinceName”:“天津”,
“value”:13,
“pname”:“tianjin”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/chongqing.xml”,
“type”:“1”,
“provinceName”:“重庆”,
“value”:33,
“pname”:“chongqing”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/xianggang.xml”,
“type”:“1”,
“provinceName”:“香港”,
“value”:3,
“pname”:“xianggang”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/aomen.xml”,
“type”:“1”,
“provinceName”:“澳门”,
“value”:3,
“pname”:“aomen”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/anhui.xml”,
“type”:“1”,
“provinceName”:“安徽”,
“value”:16,
“pname”:“anhui”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/fujian.xml”,
“type”:“1”,
“provinceName”:“福建”,
“value”:9,
“pname”:“fujian”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/guangdong.xml”,
“type”:“1”,
“provinceName”:“广东”,
“value”:21,
“pname”:“guangdong”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/guangxi.xml”,
“type”:“1”,
“provinceName”:“广西”,
“value”:14,
“pname”:“guangxi”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/guizhou.xml”,
“type”:“1”,
“provinceName”:“贵州”,
“value”:9,
“pname”:“guizhou”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/gansu.xml”,
“type”:“1”,
“provinceName”:“甘肃”,
“value”:14,
“pname”:“gansu”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/hainan.xml”,
“type”:“1”,
“provinceName”:“海南”,
“value”:18,
“pname”:“hainan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/hebei.xml”,
“type”:“1”,
“provinceName”:“河北”,
“value”:11,
“pname”:“hebei”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/henan.xml”,
“type”:“1”,
“provinceName”:“河南”,
“value”:18,
“pname”:“henan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/jilin.xml”,
“type”:“1”,
“provinceName”:“吉林”,
“value”:9,
“pname”:“jilin”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/heilongjiang.xml”,
“type”:“1”,
“provinceName”:“黑龙江”,
“value”:13,
“pname”:“heilongjiang”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/hubei.xml”,
“type”:“1”,
“provinceName”:“湖北”,
“value”:17,
“pname”:“hubei”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/hunan.xml”,
“type”:“1”,
“provinceName”:“湖南”,
“value”:14,
“pname”:“hunan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/jiangsu.xml”,
“type”:“1”,
“provinceName”:“江苏”,
“value”:13,
“pname”:“jiangsu”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/jiangxi.xml”,
“type”:“1”,
“provinceName”:“江西”,
“value”:11,
“pname”:“jiangxi”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/liaoning.xml”,
“type”:“1”,
“provinceName”:“辽宁”,
“value”:14,
“pname”:“liaoning”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/neimenggu.xml”,
“type”:“1”,
“provinceName”:“内蒙古”,
“value”:12,
“pname”:“neimenggu”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/ningxia.xml”,
“type”:“1”,
“provinceName”:“宁夏”,
“value”:5,
“pname”:“ningxia”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/qinghai.xml”,
“type”:“1”,
“provinceName”:“青海”,
“value”:8,
“pname”:“qinghai”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/sanxi.xml”,
“type”:“1”,
“provinceName”:“陕西”,
“value”:10,
“pname”:“sanxi”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/shanxi.xml”,
“type”:“1”,
“provinceName”:“山西”,
“value”:11,
“pname”:“shanxi”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/shandong.xml”,
“type”:“1”,
“provinceName”:“山东”,
“value”:16,
“pname”:“shandong”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/sichuan.xml”,
“type”:“1”,
“provinceName”:“四川”,
“value”:21,
“pname”:“sichuan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/taiwan.xml”,
“type”:“1”,
“provinceName”:“台湾”,
“value”:15,
“pname”:“taiwan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/xizang.xml”,
“type”:“1”,
“provinceName”:“西藏”,
“value”:7,
“pname”:“xizang”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/xinjiang.xml”,
“type”:“1”,
“provinceName”:“新疆”,
“value”:15,
“pname”:“xinjiang”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/yunnan.xml”,
“type”:“1”,
“provinceName”:“云南”,
“value”:16,
“pname”:“yunnan”
},{
“url”:“http://flash.weather.com.cn/wmaps/xml/zhejiang.xml”,
“type”:“1”,
“provinceName”:“浙江”,
“value”:11,
“pname”:“zhejiang”
}

]

** 文件名和路径自行在代码里修改 **