欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-08 11:23:39
...

应用背景:

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

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

调查研究并分析:
基于中国天气网的数据库设计与开发(python+MySQL)
以上是中国天气网展示的天气数据.可以看出,天气预报分成了中国八大地区进行显示,在每个地区下,还有一周天气预报,里面包含了天气现象,风向风力,最高气温,最低气温.

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

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

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

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

基于中国天气网的数据库设计与开发(python+MySQL)
我的命名规则是:日期+小写字母+数字

中国天气网的天气预报每年小时更新一次,因此,我可以在一天内建多张数据表,方便命名,也便于查找.每张数据表里的结构是这样的:
基于中国天气网的数据库设计与开发(python+MySQL)

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

为了便于区分每张表里的城市是属于哪一个省或直辖市的,因此,我建了一个area表:
基于中国天气网的数据库设计与开发(python+MySQL)
我给每个省或直辖市建了一个新的proid作为这张表的主键,除此之外,因为34个省有一个总体天气状况,因此我新增了一列weather用来存储该省的总体天气状况.

刚刚建立的20191102a1和area表都是在city表下的,以下是city表:
基于中国天气网的数据库设计与开发(python+MySQL)
里面存放了所有城市的id,以及每个城市属于的省或直辖市,以及对应的proid,我把这张表作为主表.

构建ER图:
基于中国天气网的数据库设计与开发(python+MySQL)
将ER图转为关系表:
基于中国天气网的数据库设计与开发(python+MySQL)
数据库开发:
建表:

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("数据表创建成功!")

抓取数据的部分代码:
基于中国天气网的数据库设计与开发(python+MySQL)
这张上对应着下面将要存放数据的代码.抓取到数据后,把数据存到刚刚新建的数据表中:

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+MySQL)
在数据表中显示的结果:
基于中国天气网的数据库设计与开发(python+MySQL)
不是当天的数据,所以会对不上

对于查询语句,我结合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)

基于中国天气网的数据库设计与开发(python+MySQL)
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)

基于中国天气网的数据库设计与开发(python+MySQL)
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)

基于中国天气网的数据库设计与开发(python+MySQL)
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)

基于中国天气网的数据库设计与开发(python+MySQL)
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)

基于中国天气网的数据库设计与开发(python+MySQL)
6:退出程序
基于中国天气网的数据库设计与开发(python+MySQL)
此次着重讲解数据库设计与开发的过程,因此省略了不少python程序代码,如有需要,可以跟我私聊(扫描下方二维码加我微信)
基于中国天气网的数据库设计与开发(python+MySQL)