python + docker, 实现天气数据 从FTP获取以及持久化（二）-- python操作MySQL数据库

前言

在这一节中，我们主要介绍如何使用python操作MySQL数据库。

 

准备

MySQL数据库使用的是上一节中的docker容器 “test-mysql”.

 

 

Python 操作 MySQL

我们使用的IDE是 “神奇” 的 pycharm：

1. 首先新建一个python的项目，并且安装 “mysql-connector-python”。

 

“mysql-connector-python” 是MySQL官方对于python的数据驱动，感兴趣的童鞋可以移步这里： https://dev.mysql.com/doc/connector-python/en/

 

 2. 创建 MySQLUtil.py

基于上一节的介绍，我们的任务是要读取FTP上的文本文件，从中解析出天气数据，并且插入到数据库表中（持久化）。因此，我们将基本的数据库操作都封装到这个python 文件中。

 

3.  准备工作 -- 在第一次连接的时候需要DDL操作（创建相应的数据库和表）； 之后的的操作是DML（增/删/改/查）

 

# -*- coding: utf-8 -*-


from __future__ import print_function

import mysql.connector
from mysql.connector import errorcode

config_prepare = {
  'user': 'root',
  'password': 'password',
  'host': '127.0.0.1',
  #'database': 'weather_db',
  'raise_on_warnings': True,
}

config_op = {
  'user': 'root',
  'password': 'password',
  'host': '127.0.0.1',
  'database': 'weather_db',
  #'raise_on_warnings': True,
}

def preparation(db_name='weather_db', table_name='weather'):
    try:
        cnx = mysql.connector.connect(**config)
    except mysql.connector.Error as err:
        print(err)
        return (-1)

    # try use db or create it
    try:
        cursor = cnx.cursor()
        cursor.execute("CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'UTF8MB4'".format(db_name))
        cnx.database = db_name
    except mysql.connector.Error as err:
        print(err)
        #return (-2)


    # Create table if not exist
    try:
        cnx.database = db_name
        cursor = cnx.cursor()
        createTableSql = """CREATE TABLE IF NOT EXISTS `weather`  ( \
        `weather_timestamp` varchar(32) NOT NULL, \
         `weather_data` varchar(256) NOT NULL, \
          PRIMARY KEY (`weather_timestamp`) \
        ) ENGINE=InnoDB;"""

        cursor.execute(createTableSql)
        cursor.close()
    except mysql.connector.errorcode as err:
        print(err.msg)
        return (-3)



    print ("Preparation OK")

    return (0)

 

4. 插入或更新数据

def insert_or_update_weather_data(datas):
    sql = "INSERT INTO weather(weather_timestamp, weather_data) " \
            "VALUES(%s,%s)" \
            "ON DUPLICATE KEY UPDATE weather_data = VALUES(weather_data);"

    try:
        cnx = mysql.connector.connect(**config_op)

        cursor = cnx.cursor()
        cursor.executemany(sql, datas)

        cnx.commit()

    except mysql.connector.errorcode as e:
        print('Error:', e)
        return (-1)

    finally:
        cursor.close()
        cnx.close()

    return (0)

PS: 由于天气数据是由供应商提供，提供的方式是 每日两次（早上8点；下午5点）放到FTP上面，每次提供7天的天气预报。 因此，当我们读取天气数据的时候，有一段时间的数据是重合的。因此采用插入或更新 （表中没有天气数据时：插入； 有数据时：更新）的方式来处理。

 

5. 根据时间条件，读取天气数据

def query_weather_data(begin_time_stamp, end_time_stamp):

    sql = "SELECT weather_timestamp, weather_data FROM weather " \
            "WHERE weather_timestamp BETWEEN %s AND %s;"

    try:
        cnx = mysql.connector.connect(**config_op)

        cursor = cnx.cursor()

        # The second parameter must be a tuple that contains all the delimeters

        cursor.execute(sql, (begin_time_stamp, end_time_stamp)) 

        rows = cursor.fetchall()

        return rows


    except mysql.connector.errorcode as e:
        print('Error:', e)
        return []

    finally:
        cursor.close()
        cnx.close()

 

6. 最后附上测试代码

if __name__ == '__main__':
    print("=== Unit test begin ===")

    # Test prepareation
    nRet = preparation()

    # Test insert or update datas in 'weather' table
    datas1 = (
        ('201804280000', '0.0    0.0    0.0    16.461    95.163    6.038    97.493    1013.791'),
        ('201804280015', '0.0    0.0    0.0    16.347    95.532    6.046    97.606    1013.713'),
        ('201804280030', '0.0    0.0    0.0    16.233    95.901    6.055    97.719    1013.634'),
        ('201804280045', '0.0    0.0    0.0    16.139    96.269    6.063    97.832    1013.556'),
    )
    nRet1 = insert_or_update_weather_data(datas1)

    datas2 = (

        ('201804280030', '{data1:0.0, data2:0.0, data3:0.0, data4:16.233, data5:95.901, data6:6.055, data7:97.719, data8:1013.030}'),
        ('201804280045', '{data1:0.0, data2:0.0, data3:0.0, data4:11.111, data5:93.901, data6:6.099, data7:97.700, data8:1013.045}'),
    )
    nRet2 = insert_or_update_weather_data(datas2)

    # Test query data from 'weather' table
    ret_list = query_weather_data('201804270000', '201804280040')
    if len(ret_list) == 0:
        print ("No data queried out.")
    else:
        for timestamp, data in ret_list:
            print ('timestamp:%s data:%s' %(timestamp, data))


    print("=== Unit test done ===")

 

小结

至此，我们操作MySQL的工具代码就全部编写完成。 在下一节中，我们将着重FTP部分的说明。

 

感谢大家的收看，欢迎积极留言讨论~~