Speed up Python insert in mysql

I've prepared a small script to insert csv data into mysql.

It works without problems...

Do you have any suggestions to speed it up? (I don't know if I made any programming errors in it because I'm far from being an expert):

def import_getcash(getcash_csv_file):
  '''
  Imports GetCash.csv into mysql 
  '''

  mydb = MySQLdb.connect(host="host", user="user", passwd="password", db="db")  
  cursor = mydb.cursor()
  firstline = True
  with open(os.path.join(SCRIPT_PATH, getcash_csv_file), 'r') as csvfile:
    csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"', quoting=csv.QUOTE_ALL)
    for row in csv_reader:
      if firstline:
        firstline = False
        continue
      csv_ip                      = row[0]
      csv_posto                   = row[1]
      csv_cash_date               = row[2]
      csv_cash_time               = row[3]
      csv_cash_counter            = row[4]
      csv_cash_mpay               = row[5]
      csv_cash_user               = row[6]
      csv_cash_event              = row[7]
      row[8] = row[8].replace(',','.')
      csv_cash_value              = row[8]
      csv_doc_log                 = row[9]
      csv_doc_type                = row[10]
      csv_doc_number              = row[11]
      csv_cash_obs                = row[12]
      
      print csv_cash_value
      cursor.execute('INSERT INTO getcash (ip, posto, cash_date, cash_time, cash_counter, cash_mpay, cash_user, cash_event, cash_value, doc_log, doc_type, doc_number, cash_obs) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (csv_ip, csv_posto, csv_cash_date, csv_cash_time, csv_cash_counter, csv_cash_mpay, csv_cash_user, csv_cash_event, csv_cash_value, csv_doc_log, csv_doc_type, csv_doc_number, csv_cash_obs))

Open in new window

LVL 1
ltpittAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Suggestions:

1) Disable autocommit if it is enabled. The only change that will require for you is to put as the last line of your script a conn.commit()

2) use executemany instead of execute.Then read from csv in block of hundred rows, for example, and insert them at once. Look the link below and serch for executemany
http://mysql-python.sourceforge.net/MySQLdb.html#some-mysql-examples
0
 
ltpittAuthor Commented:
Classy and useful: thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.