python - LOAD DATA INFILE 没有加载我所有的数据

Question

我需要读入 1048 行 csv 数据。数据如下所示:

\N,Olenevka,Оленевка,2596
\N,Urzuf,Урзуф,2904
\N,Lebedyn,ЛЕБЕДИН,27695
\N,Staryy Dobrotvor,Staryy Dobrotvor,6436

这是表格:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| city_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | text             | YES  |     | NULL    |                |
| alt_name   | text             | YES  |     | NULL    |                |
| population | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

代码如下:

db.query(""" 
    LOAD DATA INFILE '%s' 
    INTO TABLE %s 
    CHARACTER SET utf8
    FIELDS TERMINATED BY ',' 
    (@skip, name, alt_name, population) 
    """ % (temp_file, table))

class Database(object):
    def __init__(self, database):
        self.database = database
    def query(self, cmd):
        return subprocess.call(['mysql -u root -e "%s" %s' % (cmd, self.database)], shell=True)

当我从 python 发出命令时，它只读入 1025 行。但是，如果我从 mysql 提示符下发出相同的命令，我就会得到所有的命令。

这是运行代码后 SELECT * 的最后一行:

|    1023 | Alchevs’k        | Алчевск                             |     116000 |
|    1024 | Yakymivka        | Якимівка                            |      12353 |
|    1025 | Okhtyrka         | Охтирка                             |      49818 |
|    1026 | Adzhamka         | А                                   |          0 |
+---------+------------------+-------------------------------------+------------+

最后一行只读了一部分。这是文件的其余部分。

\N,Okhtyrka,Охтирка,49818
\N,Adzhamka,Аджамка,3859
\N,Malynivka,Малинівка,7502
\N,Mykhaylivka,Mykhaylivka,3352
\N,Kopashnovo,Копашново,3010
\N,Narkevychi,Наркевичі,1631
\N,Kirove,Кірове,3291
\N,Orikhove,Оріхове,3000
\N,Krasni Okny,Красні Окни,5461
\N,Pavlivka,Pavlovka,738
\N,Kuchurhan,Кучурган,3183
\N,Smyga,Смыга,2800
\N,Tayirove,Таїрове,1849
\N,Teplodar,Теплодар,8502
\N,Komsomolsk,Комсомольськ,51740
\N,Славутич,Славутич,25000
\N,Розгирче,Розгирче,566
\N,Orzhiv,Оржів,4125
\N,Shkil’ne,Шкільне,2127
\N,Denyshi,Деныши,1164
\N,Chetfalva,Четфалва,755
\N,Stepove,Степовое,1549
\N,Scholkine,,11677
\N,Yuzhnoukrains'k,Южноукраїнськ,39430

想法？

Answer 1

尝试在每一行的末尾放置一个反斜杠，如下所示:

db.query("""\
    LOAD DATA INFILE '%s' \
    INTO TABLE %s \
    CHARACTER SET utf8 \
    FIELDS TERMINATED BY ',' \
    (null, name, alt_name, population) \
    """ % (temp_file, table))

如果这不起作用，请尝试从“subprocess.call”调用中禁用 shell 处理。

例如:

shell=False