Contents

连接数据库SQLite

连接数据库SQLite

准备数据

之前的章节我们已经测试了Request API接口，并且把代码做了规范化处理。本节练习中，我们需要先把一些函数从notebook中导出到py文件中，这样就可以复用了。

需要注意的是: Pycharm中软件帮我们设置了项目的根目录，并且把根目录包含在系统path中。所以当我们用import导入时，python可以找到我们自己写的模块。 这里我们采用的是原生的jupyter notebook，需要手动将我们的模块添加到系统path中。一种方法是手动添加到环境变量中，这是永久方法。还有一种方法是添加临时path。本文就是采用后一种方式。

import os
import sys

module_path = os.path.abspath(os.path.join('..'))
print(module_path)
if module_path not in sys.path:
    sys.path.append(module_path)

C:\Users\renb\PycharmProjects\weather_dashapp\weather_book

项目结构

weather_book
├───.ipynb_checkpoints
├───data
├───steps  <-- notebook 主要在这里
├───weather_app
│   ├───models    <-- 函数写在这里

添加完path之后，就可以导入自己的模块了。 注意如果是上面的路径中采用的“..”,说明只返回上一层，如果是“../..”,说明返回了两层。 返回一层和两层对我的项目的区别是，以下代码是否需要包含weather_book。 如果只返回一层，系统可以直接找到weather_app 模块，但是无法找到weather_book（因为已经在weather_book模块内了，不识模块真面目，只缘身在此山中）。

from weather_app.models.query_api import get_geo_from_city,generate_url,request_weather_info,transform_weather_raw,add_city_info

一个好的习惯是，对你的函数进行快速测试。

city = 'shanghai'
lon, lat = get_geo_from_city(city)
url = generate_url(lon, lat)
text_j = request_weather_info(url)
weather_info_df = transform_weather_raw(text_j)
weather_info_df = add_city_info(weather_info_df, lon, lat, city)
weather_info_df
cloudcover lifted_index prec_type prec_amount temp2m rh2m weather timestamp wind_direction wind_speed longitude latitude city
0 9 15 none 0 5 54 cloudyday 2022-02-03 03:00:00 NE 3 121.469207 31.232276 shanghai
1 9 15 none 0 5 51 cloudyday 2022-02-03 06:00:00 NE 3 121.469207 31.232276 shanghai
2 9 15 none 1 4 75 cloudyday 2022-02-03 09:00:00 NE 3 121.469207 31.232276 shanghai
3 9 15 rain 1 4 82 lightrainnight 2022-02-03 12:00:00 NE 3 121.469207 31.232276 shanghai
4 9 15 rain 1 4 68 lightrainnight 2022-02-03 15:00:00 NE 2 121.469207 31.232276 shanghai
... ... ... ... ... ... ... ... ... ... ... ... ... ...
59 3 15 none 4 5 71 pcloudynight 2022-02-10 12:00:00 E 3 121.469207 31.232276 shanghai
60 9 15 none 4 5 71 cloudynight 2022-02-10 15:00:00 SE 2 121.469207 31.232276 shanghai
61 9 15 none 4 5 74 cloudynight 2022-02-10 18:00:00 SE 2 121.469207 31.232276 shanghai
62 9 15 none 4 5 75 cloudynight 2022-02-10 21:00:00 NE 3 121.469207 31.232276 shanghai
63 9 15 none 4 5 62 cloudyday 2022-02-11 00:00:00 NE 3 121.469207 31.232276 shanghai

64 rows × 13 columns

引入SQLite

我们可以使用任意的关系数据库保存这些数据，但是这里我强烈推荐SQLite，因为它轻量，无无服务器，免安装，开箱即用。对于新手来说，完全是无痛体验。 但是它支持标准的SQL查询语言的功能，我们一样可以用它练习数据库的增删查操作。

如果想快速入门，可以参考中文网站： https://www.runoob.com/sqlite/sqlite-intro.html#:~:text=SQLite是一个进程内,直接访问其存储文件。

使用 SQLAlchemy

我们已经选择了SQLite 作为我们的数据库，作为“客户端”python和数据库之间的衔接，我们可以选择很多库，这里推荐的是SQLAlchemy。 这里对 SQLAlchemy做一个简单介绍，SQLAlchemy 是用于处理数据库和Python的工具。他的主要组成其实是两部分：core和ORM。 core属于低阶API，ORM属于高阶API。也就是说，ORM建立在Core上。

sqla_arch_small.png 首先我们需要安装SQLAlchemy，采用pip安装。

pip install sqlalchemy

https://docs.sqlalchemy.org/en/14/tutorial/index.html

首先我们需要建立对话，调用create_engine 函数。对于SQLite 来说，URL格式就是本地数据库（文件）的路径，这里我需要在当前文件夹生成这个数据库，所以不会添加其他相对路径，只有一个文件名而已。

from sqlalchemy import create_engine

engine = create_engine('sqlite:///weather.db')

engine

Engine(sqlite:///weather.db)

想要做增删查的操作，就需要建立一个Session会话。这样我们就有一个session为我们敞开大门，等着我们进行操作。

from sqlalchemy.orm import Session

session = Session(engine)

session

<sqlalchemy.orm.session.Session at 0x214dbafbf40>

使用ORM 管理表

使用ORM最常见的方式就是使用 declarative_base() 函数构造一个基类，该函数会将声明性映射应用于从它派生的所有子类。所以我们需要建立class，然后从Base继承。 (https://docs.sqlalchemy.org/en/20/orm/mapping_styles.html)

参考 DataFrame的结构，我们创建一个 Python 类，并将 DataFrame 中的每一列作为属性包含在类中，每个类属性对应表中的特定列。

# create database table by defining python class
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column,Integer,String,Float
Base = declarative_base()

class WeatherInfo(Base):
    __tablename__ = 'weather'
    __table_args__ = {'extend_existing': True}
    id = Column(Integer,primary_key=True,autoincrement=True)  # use autoincrement
    timestamp = Column(String(55))
    cloudcover = Column(Integer)
    lifted_index = Column(Integer)
    prec_type = Column(String(10))
    prec_amount = Column(Integer)
    temp2m = Column(Integer)
    rh2m = Column(Integer)
    weather = Column(String(20))
    wind_direction = Column(String(4))
    wind_speed = Column(Integer)
    longitude = Column(Float(precision=10, decimal_return_scale=2))
    latitude = Column(Float(precision=10, decimal_return_scale=2))
    city=Column(String(50))

从数据库查询和插入

如下文所示，由于数据表是全新创建的，因此查询得到0结果。

我们将 DataFrame 插入数据库后，重新查询会获取更多数据。

请记住在所有事务之后关闭会话。

Base.metadata.create_all(engine)
result = session.query(WeatherInfo).all()
len(result)

83393

weather_info_df.to_sql('weather',engine,if_exists='append',index=False) # without index

64

result = session.query(WeatherInfo).all()
len(result)

83393

session.close()

previous

清理代码

next

处理多城市