pymysql操作mysql数据库

pandas中read_sql用起来比较方便，但我目前没有找到更新、删除、插入的方法，因此我用pymysql做了补充。

#导入pymysql和pandas
import pymysql
import pandas as pd

• 见表语句

CREATE TABLE
IF
    NOT EXISTS bi_operate_kpi_stat_v1 (
        id INT( 11 ) AUTO_INCREMENT NOT NULL COMMENT '自增id',
        thedate VARCHAR (50) NOT NULL COMMENT '统计日期(T+1)',
        stat_kind INT ( 2 ) NOT NULL COMMENT '统计类型，1：按日统计，2：按周统计，3按月统计',
        user_kind INT ( 2 ) NOT NULL COMMENT '用户类型，1新用户，2老用户',
        cid INT ( 11 ) NOT NULL COMMENT '城市id，0表示全部城市',
        uv INT ( 11 ) NOT NULL COMMENT '活跃用户数',
        pv INT ( 11 ) NOT NULL COMMENT '用户页面跳转次数',
        page_num INT ( 11 ) NOT NULL COMMENT '用户访问页面数',
        local_uv INT ( 11 ) NOT NULL COMMENT '本地用户数',
        tools_uv INT ( 11 ) NOT NULL COMMENT '工具用户数',
        hunpin_uv INT ( 11 ) NOT NULL COMMENT '婚品用户数',
        community_uv INT ( 11 ) NOT NULL COMMENT '社区用户数',
        connect_uv INT ( 11 ) NOT NULL COMMENT '本地建联用户数',
        connect_umv INT ( 11 ) NOT NULL COMMENT '本地建联数',
        connect_umv2 INT ( 11 ) NOT NULL COMMENT '婚策建联数',
        connect_umv6 INT ( 11 ) NOT NULL COMMENT '婚摄建联数',
        connect_umv12 INT ( 11 ) NOT NULL COMMENT '礼服建联数',
        connect_umv13 INT ( 11 ) NOT NULL COMMENT '酒店建联数',
        hunpin_paid_uv INT ( 11 ) NOT NULL COMMENT '婚品支付用户数',
        hunpin_paid_num INT ( 11 ) NOT NULL COMMENT '婚品支付订单数',
        hunpin_paid_gmv INT ( 11 ) NOT NULL COMMENT '婚品支付订单金额',
        card_gift_money INT ( 11 ) NOT NULL COMMENT '礼物礼金金额',
        add_values_money INT ( 11 ) NOT NULL COMMENT '增值服务收入：请帖、MV、会员收益',
        create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
  UNIQUE KEY `date_kind_city` (`thedate`,`stat_kind`,`user_kind`,`cid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COMMENT='运营核心指标';

#连接数据库
conn = pymysql.connect(host='服务地址', port=3306,database='默认库',user='用户名',password='密码',charset='utf8')

#定义函数
#定义函数
def inser_to_table(table,df,conn):
    """将df数据插入到数据库(如果存在，则更新数据)"""
    with conn.cursor() as cursor:
            try:
                col = '('+','.join(df.columns)+')'
                par = '('+','.join(['%s' for i in df.columns])+')'
                keys = ','.join([f'{i} = values({i})' for i in df.columns])
                sql = r"""insert into {0}{1} values{2} on duplicate key update {3};""".format(table,col,par,keys)
                cursor.executemany(sql,[tuple(i) for i in df.values])
                conn.commit()
                print("数据插入成功！")
            except:
                print("数据插入失败，开始回滚！")
                conn.rollback()
                print("数据回滚成功！")
              
def query_sql(sql,conn):
    """删除、更新操或者插入几条数据用这个"""
    with conn.cursor() as cursor:
        try:
            cursor.execute(sql)
            conn.commit()
            print("执行成功！")
        except:
            conn.rollback()
            print("数据回滚成功！")
            
def query_data_sql(sql,conn):
    """查询数据，返回为DataFrame类型"""
    with conn.cursor() as cursor:
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            cols = cursor.description
            print("数据查询成功，共{0}条数据".format(len(result)))
            return pd.DataFrame(result,columns = [i[0] for i in cols])
        except:
            print("数据查询失败！")

query_data_sql(sql,conn) 和 pd.read_sql(sql,conn)效果一样

#调用以上函数
df = query_data_sql(sql,conn) 
#调用pandas的方法
df = pd.read_sql(sql,conn)

需要注意的是，使用inser_to_table(table,df,conn)方法，最好是先把格式转为str类型，不然容易报格式错误。