MySQL LOAD DATA LOCAL INFILE example in python?

Question

I am looking for a syntax definition, example, sample code, wiki, etc. for executing a LOAD DATA LOCAL INFILE command from python.

I believe I can use mysqlimport as well if that is available, so any feedback (and code snippet) on which is the better route, is welcome. A Google search is not turning up much in the way of current info

The goal in either case is the same: Automate loading hundreds of files with a known naming convention & date structure, into a single MySQL table.

David

Are you using any particular library, like mysql-python or sqlalchemy? — dalloliogm
– dalloliogm, Commented Aug 5, 2009 at 10:36
I can use anything. Right now, I only have import MySQLdb in the script. — David Perron
– David Perron, Commented Aug 5, 2009 at 16:12

Mark Streatfield · Accepted Answer · 2009-08-05 17:46:39Z

30

Well, using python's MySQLdb, I use this:

connection = MySQLdb.Connect(host='**', user='**', passwd='**', db='**')
cursor = connection.cursor()
query = "LOAD DATA INFILE '/path/to/my/file' INTO TABLE sometable FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\\\'"
cursor.execute( query )
connection.commit()

replacing the host/user/passwd/db as appropriate for your needs. This is based on the MySQL docs here, The exact LOAD DATA INFILE statement would depend on your specific requirements etc (note the FIELDS TERMINATED BY, ENCLOSED BY, and ESCAPED BY statements will be specific to the type of file you are trying to read in).

answered Aug 5, 2009 at 17:46

Mark Streatfield

3,2191 gold badge25 silver badges19 bronze badges

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

4 Comments

Chiara Coetzee Over a year ago

In my case I was only missing the commit() at the end. Turns out this is required after a LOAD DATA or else the transaction gets lost.

Giovanni Di Milia Over a year ago

I wish I could uproot twice for the connection.commit() that is not shown in many places

NedStarkOfWinterfell Over a year ago

Precisely. In most places, the commit thingy is not mentioned, due to which it simply does not work.

Luke Murray Over a year ago

I would also add the argument to MySQLdb.Connect(local_infile=True)

Merwan · Accepted Answer · 2010-05-10 11:09:23Z

-2

You can also get the results for the import by adding the following lines after your query:

results = connection.info()

answered May 10, 2010 at 11:09

Merwan

4831 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL LOAD DATA LOCAL INFILE example in python?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related