data=pd.read_csv('G:\data_operation\python_book\chapter5\\sales.csv')

data.iloc[:,1]=pd.to_datetime(data.iloc[:,1])

class Jess_mysql():
 """
 設置mysql類，實現創(chuàng)建數據框，表，及添加數據
 """
 def __init__(self):
  self.mysql=MySQLdb.connect(user=mysql_name,host=mysql_host,password=mysql_password,database=mysql_database)
  self.conn=self.mysql.cursor()
 def create_table(self,table_names,col_names):
  """
  創(chuàng)建表
  :param table_names: 表名
  :param col_names: 列名，列表格式
  :return:
  """
  tables=' varchar(20),'.join(['%s'] *len(col_names))
  sql_yuju='create table if not exists `{t}` ({v} varchar(20))'.format(t=table_names,v=tables)#字段需要標注格式
  ss=sql_yuju %(tuple(col_names))
  print(ss)
  self.conn.execute(ss)
  self.mysql.commit()
 def add_data(self,table_name,col_names,col_data):
  """
  :param table_name: 表名
  :param col_names: 列名，字段名
  :param col_data: 字段值
  :return:
  """
  colname=','.join(['%s']*len(col_names))
  data=','.join(['%s']*len(col_data))
  sql_yuju='INSERT INTO `{t}` ({name}) VALUES ({data});'.format(t=table_name,name=colname,data=data)
  ss=sql_yuju%(*col_names,*col_data)
  #print(ss)
  self.conn.execute(ss)
  self.mysql.commit()

import sqlalchemy
#engine=sqlalchemy.create_engine('mysql + mysqldb://root:123456@118.24.26.227:3306/python_yuny')
engine=sqlalchemy.create_engine('mysql+mysqldb://{user}:{password}@{host}:3306/{database}'.format
        (user=mysql_name,password=mysql_password,host=mysql_host,database=mysql_database))

pd.io.sql.to_sql(frame=data,name='yunying',con=engine,index=False,if_exists='append')

x=data.shape[0]
for i in range(x):
 col_data=list(df.iloc[i,:])
 col_data[1]=datetime.date.strftime(col_data[1],'%Y%d%m')

df=pd.read_sql('select * from %s'%table_name,con=engine,index_col=None)