How to insert a CSV file data into MYSQL using Python efficiently?

Question

I have a CSV input file with aprox. 4 million records. The insert is running since +2hours and still has not finished. The Database is still empty.

Any suggestions on how to to actually insert the values (using insert into) and faster, like breaking the insert in chunks?

I'm pretty new to python.

• csv file example

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

• python script

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: Thanks for all the inputs. As suggested, I tried a counter to insert in batches of 100 and a smaller csv data set (1000 lines). The problem now is only 100 records are inserted, although the counter passes 10 x 100 several times.

code change:

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

Answer 1

There are many ways to optimise this insert. Here are some ideas:

1. You have a for loop over the entire dataset. You can do a commit() every 100 or so
2. You can insert many rows into one insert
3. you can combine the two and make a multi-row insert every 100 rows on your CSV
4. If python is not a requirement for you can do it directly using MySQL as it's explained here. (If you must do it using python, you can still prepare that statement in python and avoid looping through the file manually).

Examples:

for number 2 in the list, the code will have the following structure:

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