python mysql 文件数据入库

概述

使用python读取数据文件(本文用的csv)，处理后批量插入MySQL数据库中，在此做个记录。

程序

import pandas as pd
from datetime import datetime
import pymysql

# GLBS_
real_power_mv_no = 1000001
real_power_file_file_path = './GLBS__DATA.csv'

# mysql
mysql_host = '127.0.0.1'
mysql_port = 3306
mysql_user = 'test_user'
mysql_password = '123456'
mysql_charset = 'utf8'
mysql_db = 'test_db'


# 连接数据库
def connect_mysql():
    conn = pymysql.connect(host=mysql_host, port=mysql_port, user=mysql_user,
                           password=mysql_password, charset=mysql_charset,
                           db=mysql_db)
    cursor = conn.cursor()
    print('connect mysql success: host=' + mysql_host + ', db=' + mysql_db)
    return conn, cursor


# 关闭数据库连接
def close_mysql(conn, cursor):
    cursor.close()
    conn.close()
    print('close mysql connection finished...')


def read_real_power():
    df_data = pd.read_csv(real_power_file_file_path)
    df_data = df_data.set_index(df_data['Time'], drop=False)
    print(df_data[['Time', 'Real Power']].head())
    return df_data[['Time', 'Real Power']]


def insert_real_power_to_mysql(df_real_power, conn, cursor):
    # 先删除数据
    dt_min = df_real_power.index.min()
    dt_max = df_real_power.index.max()
    del_sql = 'delete from MV_HIS_DATA1 where mv_no=' + str(
        real_power_mv_no) + ' and data_time>="' + dt_min + '" and data_time<="' + dt_max + '"'
    cursor.execute(del_sql)
    conn.commit()

    # 拼接数据
    insert_sql_origin = 'insert into MV_HIS_DATA1 values'
    insert_sql = insert_sql_origin
    count = 0
    for idx, data in df_real_power.iterrows():
        insert_sql = insert_sql + '(' + str(real_power_mv_no) + ', "' + idx + '",' + str(
            data['Real Power']) + ',' + str(data['Real Power']) + '),'
        count = count + 1

        # 每5000条，插入1次
        if count % 5000 == 0:
            # 去掉最后一个,
            insert_sql = insert_sql[:-1]

            cursor.execute(insert_sql)
            conn.commit()
            print('insert finished...data_time=' + idx)

            count = 0
            insert_sql = insert_sql_origin

    # 最后提交一次
    insert_sql = insert_sql[:-1]
    cursor.execute(insert_sql)
    conn.commit()
    print('insert data success...real_power_mv_no=' + str(real_power_mv_no))


if __name__ == '__main__':
    # 从文件中读取实际功率
    df_real_power = read_real_power()

    # 连接数据库
    conn, cursor = connect_mysql()

    # 实际功率入库
    insert_real_power_to_mysql(df_real_power, conn, cursor)

    # 关闭数据库连接
    close_mysql(conn, cursor)