如何使用Python高效地将CSV文件数据插入MYSQL？

我有一个带有 aprox 的 CSV 输入文件。 400 万条记录。 插入已运行超过 2 小时，但仍未完成。 数据库仍然是空的。

关于如何实际插入值的任何建议（使用insert into）并且更快，比如将插入物分成块？

我对 python 还很陌生。

• csv 文件示例

43293,cancelled,1,0.0,
1049007,cancelled,1,0.0,
438255,live,1,0.0,classA
1007255,xpto,1,0.0,

• 蟒蛇脚本

def csv_to_DB(xing_csv_input, db_opts):
    print("Inserting csv file {} to database {}".format(xing_csv_input, db_opts['host']))
    conn = pymysql.connect(**db_opts)
    cur = conn.cursor()
    try:
        with open(xing_csv_input, newline='') as csvfile:
            csv_data = csv.reader(csvfile, delimiter=',', quotechar='"')
            for row in csv_data:
                insert_str = "INSERT INTO table_x (ID, desc, desc_version, val, class) VALUES (%s, %s, %s, %s, %s)"
                cur.execute(insert_str, row)
        conn.commit()
    finally:
        conn.close()

UPDATE:感谢您的所有投入。 按照建议，我尝试使用计数器来批量插入 100 个和较小的 csv 数据集（1000 行）。 现在的问题是只插入了 100 条记录，尽管计数器多次超过 10 x 100。

代码更改：

def csv_to_DB(xing_csv_input, db_opts):
   print("Inserting csv file {} to database {}".format(xing_csv_input, db_opts['host']))
   conn = pymysql.connect(**db_opts)
   cur = conn.cursor()
   count = 0
   try:
       with open(xing_csv_input, newline='') as csvfile:
           csv_data = csv.reader(csvfile, delimiter=',', quotechar='"')
           for row in csv_data:
               count += 1
               print(count)
               insert_str = "INSERT INTO table_x (ID, desc, desc_version, val, class) VALUES (%s, %s, %s, %s, %s)"

               if count >= 100:
                  cur.execute(insert_str, row)
                  print("count100")
                  conn.commit()
                  count = 0

               if not row:
                  cur.execute(insert_str, row)
                  conn.commit()
   finally:
       conn.close()

有很多方法可以优化此插入。以下是一些想法：

1. 您对整个数据集有一个 for 循环。你可以做一个commit()每100个左右
2. You can 在一次插入中插入多行 https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql
3. 您可以将两者结合起来，在 CSV 上每 100 行插入多行
4. 如果不需要 python，您可以直接使用 MySQL 来完成，正如它所解释的那样here https://stackoverflow.com/a/42933107/943138。 （如果必须使用 python 执行此操作，您仍然可以在 python 中准备该语句并避免手动循环文件）。

例子：

对于列表中的数字 2，代码将具有以下结构：

def csv_to_DB(xing_csv_input, db_opts):
    print("Inserting csv file {} to database {}".format(xing_csv_input, db_opts['host']))
    conn = pymysql.connect(**db_opts)
    cur = conn.cursor()
    try:
        with open(xing_csv_input, newline='') as csvfile:
            csv_data = csv.reader(csvfile, delimiter=',', quotechar='"')
            to_insert = []
            insert_str = "INSERT INTO table_x (ID, desc, desc_version, val, class) VALUES "
            template = '(%s, %s, %s, %s, %s)'
            count = 0
            for row in csv_data:
                count += 1
                to_insert.append(tuple(row))
                if count % 100 == 0:
                    query = insert_str + '\n'.join([template % r for r in to_insert])
                    cur.execute(query)
                    to_insert = []
                    conn.commit()
            query = insert_str + '\n'.join(template % to_insert)
            cur.execute(query)
            conn.commit()
    finally:
        conn.close()