Python + Docker for weather data acquisition and persistence from FTP (ii)--Python operation MySQL Database

Objective

In this section, we mainly describe how to use Python to manipulate the MySQL database.

Get ready

The MySQL database uses the Docker container "Test-mysql" in the previous section.

Python Operation MySQL

The IDE we use is the "Magic" Pycharm:

1. First create a new Python project and install "Mysql-connector-python".

"Mysql-connector-python" is the MySQL official data-driven python, interested in children's shoes can be a place to shift: https://dev.mysql.com/doc/connector-python/en/

2. Create mysqlutil.py

Based on the previous section, our task is to read the text file on the FTP, parse out the weather data, and insert into the database table (persist). Therefore, we encapsulate the basic database operations in this Python file.

3. Preparation-A DDL operation is required for the first connection (creating the appropriate database and table), followed by DML (increment/delete/change/check)

#-*-coding:utf-8-*- from __future__ Importprint_functionImportMysql.connector fromMysql.connectorImportErrorcodeconfig_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,}defPreparation (db_name='weather_db', table_name='Weather'):    Try: CNX= Mysql.connector.connect (* *config)exceptMysql.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_nameexceptMysql.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 (+) not NULL, ' Weather_data ' Varc Har (n) not NULL, PRIMARY KEY (' Weather_timestamp ')) Engine=innodb;"""Cursor.execute (Createtablesql) cursor.close ()exceptMysql.connector.errorcode as err:Print(err.msg)return(-3)    Print("Preparation OK")    return(0)

4. Inserting or updating data

defInsert_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 ()exceptMysql.connector.errorcode as E:Print('Error:', E)return(-1)    finally: Cursor.close () cnx.close ()return(0)

PS: Because the weather data is provided by the supplier, it is provided two times a day (8 a.m. in the morning; 5 o'clock in the afternoon) on the FTP, providing a 7-day weather forecast each time. Therefore, when we read the weather data, there is a time when the data is coincident. Therefore, it is handled in a way that is inserted or updated (when there is no weather data in the table: inserted; There is data: Updated) .

5. Read the weather data according to the time conditions

def Query_weather_data (Begin_time_stamp, End_time_stamp):     "  " WHERE weather_timestamp between%s and%s;              "    Try :         = Mysql.connector.connect (* *config_op        )= cnx.cursor ()

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

        

        rows = cursor.fetchall ()        return  rows    except  Mysql.connector.errorcode as E:        print('Error:', E)         return []    finally:        cursor.close ()        cnx.close ()

6. Finally attach the test code

if __name__=='__main__':    Print("= = = Unit Test begin = = =")    #Test prepareationNret =Preparation ()#Test Insert or update datas in ' weather ' tableDATAS1 = (        ('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 ' tableRet_list = Query_weather_data ('201804270000','201804280040')    ifLen (ret_list) = =0:Print("No data queried out.")    Else:         forTimestamp, datainchret_list:Print('timestamp:%s data:%s'%(timestamp, data))Print("= = = Unit Test done = = =")

Summary

At this point, our tool code to operate MySQL is all written and done. In the next section, we'll focus on the instructions for the FTP section.

Thank you for watching, welcome to active message Discussion ~ ~

Python + Docker for weather data acquisition and persistence from FTP (ii)--Python operation MySQL Database