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

```