python - Parsing text data and inserting in MySQL -

i confused approach need write python/any script needs dealt below situation.

i generate text file server column name, time-stamp, server name , column value

cpunumber   1437501780  l09fr01 40.0 cpunice 1437501780  l09fr01 0.0 cpusystem   1437501780  l09fr01 0.0 cpuwait 1437501780  l09fr01 0.0 cpuuser 1437501780  l09fr01 1.0 cpudile 1437501780  l09fr01 0.0 

the table need insert these values given below. load id populate uniquely within program. best way parse above information , load in table?

 create table `test`.`cpu_util_all` (  `loadid` int not null,   `servername` varchar(45) not null,   `date_time` datetime not null,   `cpu_number` decimal(10,2) null,   `cpu_user` decimal(10,2) null,   `cpu_nice` decimal(10,2) null,   `cpu_system` decimal(10,2) null,   `cpu_wait` decimal(10,2) null,   `cpu_idle` decimal(10,2) null,    primary key (`loadid`, `servername`, `date_time`,`cpu_user`)); 

you can use pandas read data this, , write sql well.

import pandas pd import sqlite3  ## tweak options here match exact file format.   x = pd.read_table('test.txt')  ## add column names (not necessary if file has header) x.names = ['loadid','servername','date_time','cpu_number','cpu_nice','cpu_system','cpu_wait','cpu_idle']  ## create sql connection. con = sqlite3.connect('test.db',) ## tweak options here keys , constraints. x.to_sql('test', con) 

you can read more using pandas reading , writing here.