基于中国天气网的数据库设计与开发(python+MySQL)

应用背景:

前不久，2019年开放数据中心峰会在北京国际会议中心成功召开，ODCC指出：“对数据进行汇聚，在体系化融合中产生新的价值已成为未来发展的关键”

在DT时代，谁拥有数据，谁就有筹码。话不多说，我们先来了解一下，什么是大数据？简单来说，假如你是一名气象员，负责统计某个城市的天气情况（最高温度、最低温度、风速等等），如果你每小时统计一次，那么，一天下来，你可以获得24条数据，如果你雇了很多人一起统计你所在城市的每个城区的天气，假设该城市有5个城区，那么，你在一天之内就能收集245=120条数据，再把范围扩大，我们把全国各个城市的天气数据汇聚在一起，（我查了一下，截止2018年，全国共有661个市），那么你在一天之内，就收集到了120661=79320条数据！这仅仅只是一天的数据，如果你坚持一周、一个月甚至更久…

调查研究并分析:

以上是中国天气网展示的天气数据.可以看出,天气预报分成了中国八大地区进行显示,在每个地区下,还有一周天气预报,里面包含了天气现象,风向风力,最高气温,最低气温.

可以说,里面可以利用的数据还是很多的,为了便于获取数据,我这里使用爬虫,一次性把数据抓取下来,具体方法请见我写的博客:

抓取中国天气网当前时段所有城市的天气数据(python+xpath)

https://blog.csdn.net/zbp_12138/article/details/101617083

我本来的想法是按照八个地区,建八张数据表,但是,这样一来,会显得很杂乱,因为还有时间,要通过时间进行分类.因此,我打算一天建一张表,把当天内所有城市的天气数据放在一起.在我的数据库里,就会呈现以下结构:

我的命名规则是:日期+小写字母+数字

中国天气网的天气预报每年小时更新一次,因此,我可以在一天内建多张数据表,方便命名,也便于查找.每张数据表里的结构是这样的:

• Id是每个城市的编号,建立主键,防止城市名出现相同的情况(中国有两个朝阳,一个是北京市朝阳区,另一个是辽宁朝阳市)
• City是每个城市的名称
• Weather是每个对应城市的天气情况,有晴,多云,小雨,阴等
• Wind表示风向以及风力大小
• Max表示白天最高温度
• Min表示夜间最低温度

为了便于区分每张表里的城市是属于哪一个省或直辖市的,因此,我建了一个area表:

我给每个省或直辖市建了一个新的proid作为这张表的主键,除此之外,因为34个省有一个总体天气状况,因此我新增了一列weather用来存储该省的总体天气状况.

刚刚建立的20191102a1和area表都是在city表下的,以下是city表:

里面存放了所有城市的id,以及每个城市属于的省或直辖市,以及对应的proid,我把这张表作为主表.

构建ER图:

将ER图转为关系表:

数据库开发:
建表:

CREATE TABLE `20191102a1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` char(10) DEFAULT NULL,
  `weather` char(10) DEFAULT NULL,
  `wind` char(20) DEFAULT NULL,
  `max` char(10) DEFAULT NULL,
  `min` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `20191102a1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=463 DEFAULT CHARSET=utf8

CREATE TABLE `area` (
  `proid` int(11) NOT NULL,
  `province` char(10) DEFAULT NULL,
  `weather` char(10) DEFAULT NULL,
  PRIMARY KEY (`proid`),
  CONSTRAINT `area_ibfk_1` FOREIGN KEY (`proid`) REFERENCES `city` (`proid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` char(10) DEFAULT NULL,
  `province` char(10) DEFAULT NULL,
  `proid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `proid` (`proid`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`proid`) REFERENCES `area` (`proid`)
) ENGINE=InnoDB AUTO_INCREMENT=463 DEFAULT CHARSET=utf8

在插入数据之前,要连接数据库:

conn = connect(host = '127.0.0.1',
               port = 3306,
               user = 'root',
               password = 'root',
               charset = 'utf8',
               db = 'asset')
cursor = self.conn.cursor()

我的数据表名称是日期和简单的字母加数字,因此,我可以通过系统时间获取:

ticks = time.strftime("%Y%m%da1", time.localtime())

ticks里存放我将要建表的表名称,开始建表:

self.cursor.execute("CREATE TABLE `%s` (id INT AUTO_INCREMENT PRIMARY KEY,city  CHAR(10),weather CHAR(10),wind CHAR(20),max CHAR(10),min CHAR(10))"%ticks)
print("数据表创建成功!")

抓取数据的部分代码:

这张上对应着下面将要存放数据的代码.抓取到数据后,把数据存到刚刚新建的数据表中:

sql = 'insert into %s(city,weather,wind,max,min)'%ticks + 'value(%s,%s,%s,%s,%s)'
cursor.execute(sql,[city_name,weather,wind,max,min])
conn.commit()

以下是运行结果:
在数据表中显示的结果:

不是当天的数据,所以会对不上

对于查询语句,我结合python写了一个可视化的程序:
1:查询所有城市的天气信息

def get_all_city(self):
        sql = 'SELECT * FROM 20191102a1'
        try:
            self.cursor.execute(sql)
            for item in self.cursor.fetchall():
                print(item)
        except Exception as e:
            print(e)

2:查询每个省/直辖市下有几个城市

def get_all_province(self):
        sql = 'SELECT city.province,COUNT(city.`city`) AS sums FROM city LEFT JOIN `area` ON city.`proid`=area.`proid` GROUP BY area.province;'
        try:
            self.cursor.execute(sql)
            for item in self.cursor.fetchall():
                print(item)
        except Exception as e:
            print(e)

3:根据温度区间查找城市

def find_tem_city(self):
        min = int(input("请输入最小值:"))
        max = int(input("请输入最大值:"))
        sql_1 = 'select * FROM 20191102a1 WHERE 20191102a1.min >= %s AND 20191102a1.max <= %s'%(min,max)
        sql_2 = 'SELECT COUNT(*) AS city_count  FROM 20191102a1 WHERE 20191102a1.min >= %s AND 20191102a1.max <= %s'%(min,max)
        try:
            self.cursor.execute(sql_1)
            for item in self.cursor.fetchall():
                print(item[0:])
            self.cursor.execute(sql_2)
            for item in self.cursor.fetchall():
                print("共有",item,"个城市在该温度范围")
        except Exception as e:
            print(e)

4:查询指定城市的天气信息

def find_city(self):
        name = input("请输入城市名:")
        sql = 'select * FROM 20191102a1 WHERE city = "%s" '%name
        try:
            self.cursor.execute(sql)
            for item in self.cursor.fetchall():
                print('%s'%name,'的天气为:',item[2])
        except Exception as e:
            print(e)

5:统计每种天气情况的数量

def find_weather(self):
        sql = 'SELECT weather,COUNT(20191102a1.`city`) AS sums FROM 20191102a1 GROUP BY 20191102a1.weather'
        try:
            self.cursor.execute(sql)
            for item in self.cursor.fetchall():
                print(item)
        except Exception as e:
            print(e)

6:退出程序

此次着重讲解数据库设计与开发的过程,因此省略了不少python程序代码,如有需要,可以跟我私聊(扫描下方二维码加我微信)