Parsing text data and inserting in MySQL

Question

I am confused with the approach i need to write a python/any script that this needs to be dealt with the below situation.

I generate a text file from the server with column name, time-stamp, server name and 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 that i need to insert these values is given below. Load id is something i would populate uniquely within the program. What is the best way to parse the above information and load it in the 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`));

ajerneck · Accepted Answer · 2015-07-21 20:06:13Z

1

You can use pandas to read data like this, and write it to SQL as well.

import pandas as pd
import sqlite3

## Tweak the options here to match the exact file format.  
x = pd.read_table('test.txt') 
## add column names (not necessary if the file has a 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 the options here to get the keys and constraints.
x.to_sql('test', con)

You can read more about using pandas for reading and writing here.

answered Jul 21, 2015 at 20:06

ajerneck

7611 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing text data and inserting in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related