mysql 数据加载_关于mysql加载数据的帮助

我要加载如下所示的csv文件:

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

但是如果你注意到了,有些字段是不完整的。我想MySQL只会跳过第一列缺失的那一行。当我运行命令时:

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);

mysql输出为:

Query OK, 41898 rows affected, 20948 warnings (0.78 sec)

Records: 20949 Deleted: 20949 Skipped: 0 Warnings: 20948

行数只有20949行,但MySQL报告它受影响的行数为41898行。为什么?而且,表中没有真正的变化。我也看不到生成的警告是关于什么的。我想使用LOADDataInfile,因为更新每一行需要半秒钟的时间,对于一个记录超过20000条的文件,这一时间转换为2.77小时。

更新:

修改代码以将auto-commit设置为“false”,并添加了db.commit()语句:

# Tell MySQLdb to turn off auto-commit

db.autocommit(False)

# Set count to 1

count = 1

while count < 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()