title: How to insert a Pandas Dataframe into MySql using PyMySQL
body: <p>I have got a DataFrame which has got around 30,000+ rows and 150+ columns. So, currently I am using the following code to insert the data into MySQL. But since it is reading the rows one at a time, it is taking too much time to insert all the rows into MySql. </p>
  
  <p>Is there any way in which I can insert the rows all at once or in batches? The constraint here is that I need to use only PyMySQL, I cannot install any other library. </p>
  
  <pre><code>import pymysql
  import pandas as pd
  
  # Create dataframe
  data = pd.DataFrame({
      'book_id':[12345, 12346, 12347],
      'title':['Python Programming', 'Learn MySQL', 'Data Science Cookbook'],
      'price':[29, 23, 27]
  })
  
  
  # Connect to the database
  connection = pymysql.connect(host='localhost',
                           user='root',
                           password='12345',
                           db='book')
  
  
  # create cursor
  cursor=connection.cursor()
  
  # creating column list for insertion
  cols = "`,`".join([str(i) for i in data.columns.tolist()])
  
  # Insert DataFrame recrds one by one.
  for i,row in data.iterrows():
      sql = "INSERT INTO `book_details` (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
      cursor.execute(sql, tuple(row))
  
      # the connection is not autocommitted by default, so we must commit to save our changes
      connection.commit()
  
  # Execute query
  sql = "SELECT * FROM `book_details`"
  cursor.execute(sql)
  
  # Fetch all the records
  result = cursor.fetchall()
  for i in result:
      print(i)
  
  connection.close()
  
  </code></pre>
  
  <p>Thank You. </p>