title: How do I load a csv into MySQL using pandas?
body: <p>Sorry for what is probably a simple question. I've been working with pandas for a little &amp; have had no trouble loading excel files into MSSQL, however, I haven't been able to figure it out for mysql. I wasn't able download the MySQLdb package, so I installed the mysql package &amp; used mysql.connector as shown below:</p>
  <p><strong>Original Code</strong></p>
  <pre><code>import pandas as pd
  import mysql.connector as mysql
  
  data = pd.read_excel(r&quot;[my file path]&quot;)
  df = pd.DataFrame(data, columns=['Name', 'country', 'age', 'gender'])
  
  
  conn = mysql.connect('Driver={SQL Server};'
                     'Server=[my server name];'
                     'Database=[my database];'
                     'Trusted_Connection=yes;')
  
  cursor = conn.cursor()
  
  print(df)
  
  
  for row in df.itertuples():
      cursor.execute('''
                  INSERT INTO [database].dbo.[table] (Name, country, age, gender)
                  VALUES (?,?,?,?)
                  ''',
                     row.Name,
                     row.country,
                     row.age,
                     row.gender
                     )
  conn.commit()
  </code></pre>
  <p>Not sure what exactly I should be doing to make this all work.</p>
  <p><strong>Code that works</strong></p>
  <pre><code>import mysql.connector
  import pandas as pd
  
  conn= mysql.connector.connect(user='[username]', password='[password]', host='[hostname]', database='[databasename]')
  cursor = conn.cursor()
  excel_data = pd.read_excel(r'[filepath]',sep=',', quotechar='\'')
  
  for row in excel_data.iterrows():
      testlist = row[1].values
      cursor.execute(&quot;INSERT INTO [tablename](Name, Country, Age, Gender)&quot;
                     &quot; VALUES('%s','%s','%s','%s')&quot; % tuple(testlist))
  
  conn.commit()
  cursor.close()
  conn.close()
  </code></pre>
  <p><strong>More concise code which also works</strong></p>
  <pre><code>import pandas as pd
  from sqlalchemy import create_engine, types
  
  engine = create_engine('mysql://root:[password]@localhost/[database]') # enter your password and database names here
  
  df = pd.read_excel(r&quot;[file path]&quot;,sep=',',quotechar='\'',encoding='utf8') # Replace Excel_file_name with your excel sheet name
  df.to_sql('[table name]',con=engine,index=False,if_exists='append') # Replace Table_name with your sql table name
  </code></pre>