CODEWITHSUNDEEP
pythonmysql
manxing
manxing

Reputation: 3335

convert data type and then load data into Mysql by Python

Besides LOAD DATA INFILE command, is there any other way to read a .txt file which contains three columns into Mysql? I want to read the data line by line and put them into a table in Mysql.

It is impossible to convert the datatype in the followingLOAD DATA INFILE command, right?

LOAD DATA INFILE 'filename' into TABLE info (col1, col2, col3)

My data sample is like this:

123456.123455   dsfd  wqoe   
123455.123237   dert  2222
098878.938475   lkjf  2346

So before I load the data into Mysql, I need to convert the first column from unix time into datetime since the data type of the first column in Mysql is DATETIME, I want to use FROM_UNIXTIME to solve this problem. But how can I use a python variable as the fieldname in FROM_UNIXTIME(fieldname)which is a Mysql command?

UPDATED: This is my solution I have three columns in the table video, they are datetime, src_ip, video_id respectively The problem is, @unixtime variable can only save one value or? since now my first column in mysql are exactly the same, maybe it equals to the very last result..how can I solve this?

insertQuery = "LOAD DATA LOCAL INFILE 'all-filter.txt' INTO TABLE video (@unixtime, src_ip, video_id); UPDATE video SET datetime=FROM_UNIXTIME(@unixtime)"

Upvotes: 0

Views: 794

Answers (1)

Maarten van Leunen
Maarten van Leunen

Reputation: 452

I've tried the following which works:

create table info (s datetime not null primary key, str varchar(100), str2 varchar(100));

load data local infile '/home/maartenl/sample.txt' 
into table info (@s, str, str2) 
set s = from_unixtime(@s);

And that works fine with your sample set.

Upvotes: 1

Related Questions