Feeding data into MySQLdb LOAD DATA from Python

[John Nagle]

Is it possible to feed data into a LOAD DATA command in MySQL without
writing out the data to a file? It's possible to do this using the
MySQL command line and a UNIX FIFO, but that's kind of clunky.
I have to load a few gigabytes of data, and using INSERT takes
a whole day for each update.

And can this be done portably across UNIX and Windows? Thanks.

John Nagle

 

[Larry Bates]

Is it possible to feed data into a LOAD DATA command in MySQL without
writing out the data to a file? It's possible to do this using the
MySQL command line and a UNIX FIFO, but that's kind of clunky.
I have to load a few gigabytes of data, and using INSERT takes
a whole day for each update.

And can this be done portably across UNIX and Windows? Thanks.

John Nagle

Where is the data coming from? Two suggestions:

1) There is a MySQL option to not commit every insert, sorry I can't remember
what it is, but it REALLY speeds things up (especially if you have lots of
keys). Or drop all the keys load, the data, and recreate the keys. Sounds odd,
but it is a recommended method.
2) If data is coming from a file now, use LOAD DATA directly, don't go through
Python. You may even find writing to file and then loading it is very fast. I
recommend tab delimited as that seems to work well.

-Larry

 

[Dennis Lee Bieber]

Is it possible to feed data into a LOAD DATA command in MySQL without
writing out the data to a file? It's possible to do this using the
MySQL command line and a UNIX FIFO, but that's kind of clunky.
I have to load a few gigabytes of data, and using INSERT takes
a whole day for each update.

If loading a lot of data, MySQL AB recommends dropping all indices,
loading the data, and then recreating the indices.

Also, if you are using individual INSERT (that is
cursor.execute("insert ...", (data,)) ), it may help to collect batches
of the data in a list, and invoke cursor.executemany("insert...", [list
of (data,) rows])
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/