Python学习
50个Pandas高级操作
盘点66个Pandas函数,轻松搞定“数据清洗”!
Python操作Mysql
学习 python requests模块
python怎么用Google AI
Python 连接 redis
本文档使用 MrDoc 发布
-
+
首页
Python操作Mysql
## 需要的库: ``` Pandas PyMySQL sqlalchemy ``` 手动下载pip库: ``` pip download numpy==1.21.2 ``` 资源库: ``` https://pypi.org/ ``` 案例: 从客户群日志表中按天统计每个客户群,渠道的数量,写入目标记录表中,每天执行一次 ``` import pymysql import pandas from sqlalchemy import create_engine ,text import datetime #从主库查询数据 def query(sql): df = pandas.read_sql(text(sql),con=engineProd.connect()) return df # 写入mysql数据库 def to_sql(table,df): df.to_sql(table, con=engine2, if_exists='append', index=False) #删除数据 def delete_data(table, date, host, user, password, db , port): # 连接到MySQL数据库 conn = pymysql.connect(host=host, user=user, password=password, db=db, port=port) cursor = conn.cursor() # 构建查询语句,检查指定日期是否存在 query = f"SELECT 1 FROM {table} WHERE data_time = '{date}' limit 1" cursor.execute(query) result = cursor.fetchall() # 如果指定日期存在,则删除该数据 if result: set_query="SET SQL_SAFE_UPDATES =0;" cursor.execute(set_query) delete_query = f"DELETE FROM {table} WHERE data_time = '{date}'" cursor.execute(delete_query) conn.commit() print(f"data exist,deleted data for {date} from {table}.") else: print(f"No data found for {date} in {table}.") cursor.close() conn.close() if __name__ =='__main__': #生产库的信息: mysql_usernameProd = 'ngmk_hn' mysql_passwordProd = 'TYE752qwu' mysql_ipProd = '172.20.34.111' portProd = 23307 dbProd = 'ngmk_hn_sit' engineProd = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(mysql_usernameProd, mysql_passwordProd, mysql_ipProd, portProd,dbProd)) #测试库的配置信息 mysql_usernameTest = 'ngmk_hn' mysql_passwordTest = 'Creator1qaz' mysql_ipTest = '172.20.34.15' portTest = 20001 dbTest = 'ngmk_hn_sit' engine2 = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(mysql_usernameTest, mysql_passwordTest, mysql_ipTest, portTest,dbTest)) data_time='20231101' data_month=data_time[0:6] #1。判断插入表是否存在当前日期历史数据,如果存在删除 delete_data('portrait_info_count', data_time, '172.20.34.15', 'ngmk_hn', 'Creator1qaz', 'ngmk_hn_sit', 20001) #2. 从源表读取数据放入df query_SQL = "select data_time,portrait_info_id,portrait_info_name,channel,count(1) as count " \ "from tl_user_portrait_channel_info_" +data_month +" where DATA_TIME ='" \ +data_time+"' group by data_time,portrait_info_id,portrait_info_name,channel" starttime = datetime.datetime.now() df_data = query(query_SQL) endtime = datetime.datetime.now() print(df_data) print('query time:' + str(endtime-starttime)) #3 df_data插入目标库 to_sql('portrait_info_count',df_data) inserttime = datetime.datetime.now() print ('insert time :' + str(inserttime-endtime)) ``` ## 参考 ``` https://blog.csdn.net/LeiLiFengX/article/details/109922043 ```
dz
2023年11月1日 17:17
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码