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. Advertisements

  2. John Nagle

    Larry Bates Guest

    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. Advertisements

  3. 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. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.