Databases
StackOverflow2013
Postsdbo
POHelp with MySQL LOAD DATA INFILE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with MySQL LOAD DATA INFILE
    primarykey
    data
    text
    <p>I want to load a CSV file that looks like this:</p> <pre><code>Acct. No.,1-15 Days,16-30 Days,31-60 Days,61-90 Days,91-120 Days,Beyond 120 Days 2314134101,898.89,8372.16,5584.23,7744.41,9846.54,2896.25 2414134128,5457.61,7488.26,9594.02,6234.78,273.7,2356.13 2513918869,2059.59,7578.59,9395.51,7159.15,5827.48,3041.62 1687950783,4846.85,8364.22,9892.55,7213.45,8815.33,7603.4 2764856043,5250.11,9946.49,8042.03,6058.64,9194.78,8296.2 2865446086,596.22,7670.04,8564.08,3263.85,9662.46,7027.22 ,4725.99,1336.24,9356.03,1572.81,4942.11,6088.94 ,8248.47,956.81,8713.06,2589.14,5316.68,1543.67 ,538.22,1473.91,3292.09,6843.89,2687.07,9808.05 ,9885.85,2730.72,6876,8024.47,1196.87,1655.29 </code></pre> <p>But if you notice, some of the fields are incomplete. I'm thinking MySQL will just skip the row where the first column is missing. When I run the command:</p> <pre><code>LOAD DATA LOCAL INFILE 'test-long.csv' REPLACE INTO TABLE accounts FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (cf_535, cf_580, cf_568, cf_569, cf_571, cf_572); </code></pre> <p>And the MySQL output is:</p> <pre><code>Query OK, 41898 rows affected, 20948 warnings (0.78 sec) Records: 20949 Deleted: 20949 Skipped: 0 Warnings: 20948 </code></pre> <p>The number of lines is only 20,949 but MySQL reports it as 41,898 rows affected. Why so? Also, nothing really changed in the table. I also couldn't see what the warnings generated is all about. I wanted to use the LOAD DATA INFILE because it takes python half a second to update each row which translates to 2.77 hours for a file with 20,000+ records.</p> <p><strong>UPDATE:</strong> Modified the code to set auto-commit to 'False' and added a db.commit() statement:</p> <pre><code># Tell MySQLdb to turn off auto-commit db.autocommit(False) # Set count to 1 count = 1 while count &lt; len(contents): if contents[count][0] != '': cursor.execute(""" UPDATE accounts SET cf_580 = %s, cf_568 = %s, cf_569 = %s, cf_571 = %s, cf_572 = %s WHERE cf_535 = %s""" % (contents[count][1], contents[count][2], contents[count][3], contents[count][4], contents[count][5], contents[count][0])) count += 1 try: db.commit() except: db.rollback() </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USFrancis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHelp with MySQL LOAD DATA INFILE
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload