471,306 Members | 839 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

Feeding data into MySQLdb LOAD DATA from Python

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
Nov 14 '07 #1
1 1642
John Nagle wrote:
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
Nov 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dave Harrison | last post: by
5 posts views Thread by Chris Stromberger | last post: by
1 post views Thread by Derek Fountain | last post: by
1 post views Thread by Steve | last post: by
11 posts views Thread by Fred | last post: by
29 posts views Thread by dyork | last post: by
1 post views Thread by Sjoerd | last post: by
3 posts views Thread by Lawrence D'Oliveiro | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.