使用Python导出并映射CSV输出到MySQL表

Question

我有多个客户端连接到一个服务器，使用双向的iperf来监控网络。iperf服务器运行得很好，并根据客户端设置的定时任务以CSV格式显示输出。

我想写一个Python脚本，自动将这些CSV输出映射到MySQL数据库中；这样就可以定期更新和保存，而不需要人工干预。

我在一台运行Ubuntu 13.10的机器上做iperf服务器。以下是我得到的一个CSV输出示例。这个输出并没有存储到文件中，只是在屏幕上显示。

s1:~$ iperf -s -y C  
20140422105054,172.16.10.76,41065,172.16.10.65,5001,6,0.0-20.0,73138176,29215083
20140422105054,172.16.10.76,5001,172.16.10.65,56254,4,0.0-20.0,46350336,18502933
20140422105100,172.16.10.76,54550,172.16.10.50,5001,8,0.0-20.0,67895296,27129408
20140422105100,172.16.10.76,5001,172.16.10.50,58447,5,0.0-20.1,50937856,20292796
20140422105553,172.16.10.76,5001,172.16.10.65,47382,7,0.0-20.1,51118080,20358083
20140422105553,172.16.10.76,41067,172.16.10.65,5001,5,0.0-20.1,76677120,30524007
20140422105600,172.16.10.76,5001,172.16.10.50,40734,4,0.0-20.0,57606144,23001066
20140422105600,172.16.10.76,54552,172.16.10.50,5001,8,0.0-20.0,70123520,28019115
20140422110053,172.16.10.76,41070,172.16.10.65,5001,5,0.0-20.1,63438848,25284066
20140422110053,172.16.10.76,5001,172.16.10.65,46462,6,0.0-20.1,11321344,4497094

我想将这些数据映射到的字段有：时间戳、服务器IP、服务器端口、客户端IP、客户端端口、标签ID、间隔、传输量、带宽。

我希望定期将这个CSV输出映射到MySQL数据库中，因此我知道需要写一个Python脚本（放在定时任务里），来查询并存储到MySQL数据库。我对Python脚本和数据库查询还是个初学者。

我还看过另一个讨论，关于服务器故障的内容，链接在这里：[https://serverfault.com/questions/566737/iperf-csv-output-format]；我想基于这个来构建我的查询。

Answer 1

如果你打算使用Python，我建议你使用sqlalchemy这个库。

一般的做法是：

• 先定义一个类，这个类里包含你想要存储的所有属性。
• 把这个类的所有属性和数据库中的列以及数据类型对应起来。
• 从csv文件中读取数据（可以用比如说csv模块），对于每一行数据，创建一个之前定义的类的对象，然后把这个对象存储起来。

sqlalchemy会提供更多的细节和使用说明，你的需求看起来比较简单。

另外一个选择是找一个现成的csv导入工具，MySQL里已经有一些这样的工具，其他地方也有很多类似的工具。

Answer 2

这可能不是你想要的答案，但如果你稍微学一下sqlite3（这是Python自带的一个模块，使用时只需“import sqlite3”），通过网上的基础教程，你会发现其实你的问题并不难解决。然后只需用一个标准的计时器，比如time.sleep()，来重复这个过程就可以了。

Answer 3

生成SQL脚本，然后运行它

如果你不想使用像 sqlalchemy 这样复杂的解决方案，可以试试下面的方法。

1. 先把你的CSV数据转换成SQL脚本
2. 然后用MySQL命令行工具来运行这个脚本

在第一次操作之前，确保你在数据库中创建了所需的数据库结构（这部分就留给你自己去做）。

下面的示例使用了一个叫 docopt 的包，出于方便考虑，你需要先安装它：

$ pip install docopt

CSV转SQL脚本工具

csv2sql.py：

"""
Usage:
    csv2sql.py [--table <tablename>] <csvfile>

Options:
    --table <tablename>  Name of table in database to import into [default: mytable]

Convert csv file with iperf data into sql script for importing
those data into MySQL database.
"""
from csv import DictReader
from docopt import docopt

if __name__ == "__main__":
    args = docopt(__doc__)
    fname = args["<csvfile>"]
    tablename = args["--table"]
    headers = ["timestamp",
               "server_ip",
               "server_port",
               "client_ip",
               "client_port",
               "tag_id",
               "interval",
               "transferred",
               "bandwidth"
              ]
    sql = """insert into {tablename}
    values ({timestamp},"{server_ip}",{server_port},"{client_ip}",{client_port},{tag_id},"{interval}",{transferred},{bandwidth});"""
    with open(fname) as f:
        reader = DictReader(f, headers, delimiter=",")
        for rec in reader:
            print(sql.format(tablename=tablename, **rec)) # python <= 2.6 will fail here

将CSV转换为SQL脚本

首先让转换工具自我介绍一下：

$ python csv2sql.py -h
Usage:
    csv2sql.py [--table <tablename>] <csvfile>

Options:
    --table <tablename>  Name of table in database to import into [default: mytable]

Convert csv file with iperf data into sql script for importing
those data into MySQL database.

假设你的数据在文件 data.csv 中：

$ python csv2sql.py data.csv 
insert into mytable
    values (20140422105054,"172.16.10.76",41065,"172.16.10.65",5001,6,"0.0-20.0",73138176,29215083);
insert into mytable
    values (20140422105054,"172.16.10.76",5001,"172.16.10.65",56254,4,"0.0-20.0",46350336,18502933);
insert into mytable
    values (20140422105100,"172.16.10.76",54550,"172.16.10.50",5001,8,"0.0-20.0",67895296,27129408);
insert into mytable
    values (20140422105100,"172.16.10.76",5001,"172.16.10.50",58447,5,"0.0-20.1",50937856,20292796);
insert into mytable
    values (20140422105553,"172.16.10.76",5001,"172.16.10.65",47382,7,"0.0-20.1",51118080,20358083);
insert into mytable
    values (20140422105553,"172.16.10.76",41067,"172.16.10.65",5001,5,"0.0-20.1",76677120,30524007);
insert into mytable
    values (20140422105600,"172.16.10.76",5001,"172.16.10.50",40734,4,"0.0-20.0",57606144,23001066);
insert into mytable
    values (20140422105600,"172.16.10.76",54552,"172.16.10.50",5001,8,"0.0-20.0",70123520,28019115);
insert into mytable
    values (20140422110053,"172.16.10.76",41070,"172.16.10.65",5001,5,"0.0-20.1",63438848,25284066);
insert into mytable
    values (20140422110053,"172.16.10.76",5001,"172.16.10.65",46462,6,"0.0-20.1",11321344,4497094);

把所有内容放到文件 data.sql 中：

$ python csv2sql.py data.csv > data.sql

将 data.sql 应用到你的MySQL数据库

最后，使用 mysql 命令（这是MySQL提供的）将数据导入到数据库中：

$ myslq --user username --password password db_name < data.sql