利用pandas实现从数据库读写数据

来源：互联网发布：mac macintosh hd 编辑：程序博客网时间：2024/05/17 04:05

可以使用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[,ﬂavor, ...]) 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 iin 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 iin 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)

1 0