Inserting data into MySQL

Question

My code currently doesn't insert data into MySQL fast enough. However I don't know how to measure the current speed.

def insert():
      mycursor = mydb.cursor()
      plik = input(":::")
      agefile = open("C:/Users/juczi/Desktop/New Folder/" + plik, "r")
      sql = "INSERT INTO wyciek (nick, ip) VALUES (%s, %s)"
      for row in agefile:
            mydb.commit()
            data = row.split(':')
            val = (data[0].replace("\n",""), data[1].replace("\n", ""))
            mycursor.execute(sql, val)
      agefile.close()

Answer 1

If you are trying to insert data in bulk to Mysql the best would be to use LOAD DATA instead of doing it in a loop on top of an interpreted language, which is inevitably slower. However there is less flexibility if you need to transform data but that shouldn't be a problem in your case. All you're doing is get rid of carriage return. This is a plain delimited file without heavy transformation.

The mydb.commit() is misplaced. It should be called after running an insert/update. On the first iteration there is nothing to be committed. But this is not efficient. I suggest that you take full advantage of transactions. Start a transaction at the beginning, do your loop and commit at the end. In case of error, you roll back. The added benefit is data integrity. If an error occurs, the data already inserted is "undone" so you don't end up with garbage due to a partial import of data.

Schema:

try:
    conn.autocommit = False

    # insert loop..

    # Commit your changes
    conn.commit()

except mysql.connector.Error as error:
    print("Failed to update record to database rollback: {}".format(error))
    # reverting changes because of exception
    conn.rollback()

See for example: Use Commit and Rollback to Manage MySQL Transactions in Python

This alone should improve performance but you have to test it to measure the gain.

Not performance-related, but you can also use a context manager whenever possible, for instance when opening/writing to a file. So:

agefile = open("C:/Users/juczi/Desktop/New Folder/" + plik, "r")

becomes:

with open("C:/Users/juczi/Desktop/New Folder/" + plik, "r") as agefile:
   # do something

and you don't have to bother closing the file, this is done automatically.

Answer 2

Plan A:

Build a 'batch' INSERT:

INSERT INTO wyciek (nick, ip) VALUES (12, 34), (234, 456), ...

Then execute it. If your columns are VARCHAR instead of INT, be sure to put quotes around each value.

For 100 rows, this will run about 10 times as fast as 100 single-row INSERTs.

(And that is aside from having the COMMIT inside the loop!)

Plan B:

If your data comes from a file, consider using LOAD DATA INFILE .... It may be even faster than plan A.

If you data is not already in a file, building the file to do LOAD may be slower than Plan A.