Writing to MySQL database with pandas using SQLAlchemy, to_sql

Using the engine in place of the raw_connection() worked:

import pandas as pdimport mysql.connectorfrom sqlalchemy import create_engineengine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)

Not clear on why when I tried this yesterday it gave me the earlier error.

Alternatively, use pymysql package...

import pymysqlfrom sqlalchemy import create_enginecnx = create_engine('mysql+pymysql://[user]:[pass]@[host]:[port]/[schema]', echo=False)data = pd.read_sql('SELECT * FROM sample_table', cnx)data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)

Using pymysql and sqlalchemy, this works for Pandas v0.22:

import pandas as pdimport pymysqlfrom sqlalchemy import create_engineuser = 'yourUserName'passw = 'password'host =  'hostName'  # either localhost or ip e.g. '172.17.0.2' or hostname address port = 3306 database = 'dataBaseName'mydb = create_engine('mysql+pymysql://' + user + ':' + passw + '@' + host + ':' + str(port) + '/' + database , echo=False)directory = r'directoryLocation'  # path of csv filecsvFileName = 'something.csv'df = pd.read_csv(os.path.join(directory, csvFileName ))df.to_sql(name=csvFileName[:-4], con=mydb, if_exists = 'replace', index=False)"""if_exists: {'fail', 'replace', 'append'}, default 'fail'     fail: If table exists, do nothing.     replace: If table exists, drop it, recreate it, and insert data.     append: If table exists, insert data. Create if does not exist."""