可以使用pandas的
1、read_sql_query来执行sql语句
2、read_sql实现获取整个表的数据
3、to_sql实现向数据库中添加数据
--------------------------------------------------------------------------------------------------------------------------------------------------------
read_sql_table(table_name, con[, schema, ...]) Read SQL database table into a DataFrame. read_sql_query(sql, con[, index_col, ...]) Read SQL query into a DataFrame. read_sql(sql, con[, index_col, ...]) Read SQL query or database table into a DataFrame. DataFrame.to_sql(name, con[, flavor, ...]) Write records stored in a DataFrame to a SQL database.--------------------------------------------------------------------------------------------------------------------------------------------------------
实例：
today = datetime.datetime.now() daogang = today.replace(hour =8, minute=10,second=3) ligang = today.replace(hour =17, minute=05,second=3) logindate = daogang.strftime('%Y-%m-%d') df['logindate'] = logindate [m1,n1] = df.shape for i in range(m1): daogang_time = (daogang-datetime.timedelta(minutes = random.randint(0,15),seconds=random.randint(0,59))).strftime('%H:%M:%S') ligang_time = (ligang-datetime.timedelta(minutes = random.randint(0,10),seconds=random.randint(0,59))).strftime('%H:%M:%S') df.iloc[i,5] = daogang_time df.iloc[i,6] = ligang_time engine = create_engine('mysql+pymysql://root:123456@localhost:3306/office?charset=utf8') loginList = pd.read_sql_query("select * from kaoqin_sub where logindate ='"+str(logindate)+u"'", engine) [m2,n2] = loginList.shape mypd = loginList[0:m1].copy() mypd = mypd.drop([u"daogang_time","ligang_time"],axis=1) if m2 > 0: for i in range(m1): mypd.loc[i,"id"] = df.loc[i,"id"] mypd.loc[i,u"name"] = df.loc[i,u"name"] mypd.loc[i,u"groupid"] = df.loc[i,u"groupid"] mypd.loc[i,u"dept"] = df.loc[i,u"dept"] mypd.loc[i,u"logindate"] = df.loc[i,u"logindate"] mypd.loc[i,u"daogang_time"] = df.loc[i,u"daogang_time"] mypd.loc[i,u"ligang_time"] = df.loc[i,u"ligang_time"] mypd.loc[i,u"daogang_IP"] = df.loc[i,u"daogang_IP"] mypd.loc[i,u"ligang_IP"] = df.loc[i,u"ligang_IP"] mypd.to_sql('kaoqin_sub',engine,if_exists='append',index=False)
