python - MySQL `Load Data Infile Local` fails for .csv unless I open and save the file first. How can I avoid this step? -

i generate .csv files using python script writing pandas dataframe to_csv, using utf8 encoding.

consex.to_csv(os.path.join(base_dir, "database/tables/consumption expenditure/consumptionexpenditure.csv"), encoding = 'utf8', index = false) 

next, upload amazon rds mysql via load data local infile, table has charset set utf8.

create  table if not exists consumer.expenditure (   ceid int not null auto_increment ,   cecategory int not null,   year int null,   countryid int not null,   ceyoy dec(15,2) null,   datecreated date not null ,   lastmodified datetime null default null ,   datedeleted datetime null default null ,   primary key (ceid) ,   constraint ce_fk_countries     foreign key (countryid)     references consumeralpha.countries (countryid)     on delete no action     on update no action)  charset utf8  engine = innodb; show warnings; 

now, upload works if first open , manually save csv file - same name, same location. otherwise, fails without error - doesn't import anything. however, if open, save, , close, upload works perfectly.

load data local  infile '/users/xxx/dropbox/data/database/tables/consumption expenditure/consumptionexpenditure.csv' table consumer.expenditure fields terminated ',' optionally enclosed '"' lines terminated '\r' ignore 1 lines (cecategory, year, countryid, ceyoy, datecreated) set datecreated = now(); 

is there i'm missing avoid step? large tables, there may many .csv files. i've looked around quite bit, haven't come across particular issue anywhere. thanks!

considering skipping csv step using pandas.io.sql.write_frame.

import mysqldb conn = mysqldb.conn(host='...', user='...', passwd='...',db='...')  import pandas.io.sql psql psql.write_frame(dataframe, 'tablename', conn, 'mysql') 

if appending existing table, use keyword argument if_exists='append'.

edit: forgot @andy wrote same answer awhile back: https://stackoverflow.com/a/16477603/1221924. notes, added mysql support neglected document it.