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

            #
             -*- 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 ittry:
        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 existtry:
        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)

            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)

            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()
        

            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 ===")