Feeding data into MySQLdb LOAD DATA from Python

Discussion in 'Python' started by John Nagle, Nov 14, 2007.

  1. John Nagle

    John Nagle Guest

    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
     
    John Nagle, Nov 14, 2007
    #1
    1. Advertising

  2. John Nagle

    Larry Bates Guest

    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
     
    Larry Bates, Nov 14, 2007
    #2
    1. Advertising

  3. On Wed, 14 Nov 2007 11:09:15 -0800, John Nagle <>
    declaimed the following in comp.lang.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.
    >

    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

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Nov 15, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. judy
    Replies:
    0
    Views:
    368
  2. xerj
    Replies:
    2
    Views:
    404
  3. Replies:
    12
    Views:
    1,003
    Jerry Coffin
    Jun 2, 2008
  4. kuru
    Replies:
    3
    Views:
    246
  5. Chris Markle
    Replies:
    3
    Views:
    146
    Chris Markle
    Feb 25, 2009
Loading...

Share This Page