# Importing and exporting data between pandas and MySQL database

import pymysql
import pandas as pd
import mysql.connector
from sqlalchemy import types, create_engine

# Create dataframe
dic={
'EMPNO':[7369,7499,7566,7654,7698,7782,7788,7839,7844,7900,7902,7934],
'ENMAE':['JAMES','ADAMS','CLARK','KING','WARD','JONES','ADAMS','SCOTT','FORD',

'BLAKE','MARTIN','TURNER'],
'JOB':['CLERK','CLERK','ANALYST','MANAGER','MANAGER','PRESIDENT','ANALYST',

'CLERK','MANAGER','ANALYST','SALESMAN','CLERK'],
'MGR':[7876,7876,7782,7900,7900 ,7900,7782,7876,7900,7782,7900,7876],
'HIREDATE':['2005/02/18','2005/01/04','2001/05/18','2003/04/19','2001/07/02',
'2006/09/21','2007/03/13','2005/03/06', '2007/01/12','2009/07/19','2009/01/05',

'2004/11/30'],
'SAL':[11400,19200,29400,60000,15000,95700,13200,36000,36000,34200,15000,18000],
'COMM':[4000,5000,5000,4000,2500,4000,2500,3000 ,3000,2500,2000 ,6000],
'DEPTT':[20,30,20,30,30,10,20,10,30,30,20,10]
}

data = pd.DataFrame(dic)
print('Our DataFrame is:\n',data)

tableName="employeedata"

# create sqlalchemy engine
sqlEngine = create_engine("mysql+pymysql://root:@localhost/Company")
dbConnection = sqlEngine.connect()

try:
    # Exporting dataframe to SQl
    frame = data.to_sql(tableName, dbConnection, if_exists='fail');

except ValueError as vx:

    print(vx)

except Exception as ex:   

    print(ex)

else:

    print("Table %s created successfully.\n"%tableName);   

finally:

    dbConnection.close()                       

# – Read a MySQL Database Table and write into a Pandas DataFrame:

sqlEngine   = create_engine('mysql+pymysql://root:@127.0.0.1')

dbConnection= sqlEngine.connect()

dframe       = pd.read_sql("select * from Company.employeedata", dbConnection);

print("After importing data from MySql:\n")
print(dframe)

dbConnection.close()