MySQL: Importing CSV file from script

Question

I have script to parse some data and save the data into Output.csv (Output.csv is located on server (/tmp) ) Then I want to import this Output.csv in to a MySQL existing table.

        with open("/tmp/Output.csv", "w",0600) as text_file:
          text_file.write(csv)
          mydbs.connect()     
          mydbs.sqlcmd("""LOAD DATA LOCAL INFILE '/tmp/Output.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;""")
          text_file.close()
          os.remove("/tmp/Output.csv")

SQL query works in some way, for example if I change "my_table" to "my_tableasd" I get an error:

ProgrammingError: (1146, "Table 'dbs.my_tableasd' doesn't exist").

Also I can do any kind of Selects but this import simply does not work. I don't get any errors or warnings..nothing, but when I have a look into PHPmyAdmin there is no data imported.

I tried to import Output.csv through PhpMyAdmin and it works..

I also tried to set local-infile=1 to my.cnf ([mysql], [mysqld]) but nothing happened

Thank you very much

PaulF · Accepted Answer · 2015-10-30 14:20:42Z

1

Close the file before you try to import it.

answered Oct 30, 2015 at 14:20

PaulF

6,7932 gold badges20 silver badges30 bronze badges

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

Comments

Cyrbil · Accepted Answer · 2015-10-30 14:22:38Z

0

Separate writting and processing:

with open("/tmp/Output.csv", "w",0600) as text_file:
    text_file.write(csv)

mydbs.connect()     
mydbs.sqlcmd("""
    LOAD DATA LOCAL INFILE '/tmp/Output.csv'
    INTO TABLE my_table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' ;
""")

os.remove("/tmp/Output.csv")

answered Oct 30, 2015 at 14:22

Cyrbil

6,5081 gold badge27 silver badges42 bronze badges

Comments

Ondrej · Accepted Answer · 2015-11-02 07:30:43Z

0

Problem solved, I tried to close database and it works. It's interesting because I never do that in whole project and I'm working with dbs all the time, but maybe this import needs to be submitted or smth like that

mydbs.close()

answered Nov 2, 2015 at 7:30

Ondrej

1,0271 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL: Importing CSV file from script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related