Pandas Dataframe存储到 MySql

Pandas Dataframe 存储到 MySql

存储到MySQL一般方法：

import pymysql
# 建立连接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='331303',
    charset='utf8',
)
# 获取光标
cursor=conn.cursor()
# sql语句
sql='insert into new_schema01.sbage (name,age) values (%s,%s)'
data=[('lily',8),('henery',19),('poter',26)]
# data=[('peter','12'),('jack','24'),('mary','34')]
# name = 'wuli'
# age = '24'
try:
    # 执行sql
    cursor.executemany(sql,data)
    # 提交并保存db表
    conn.commit()
except Exception as e:
    print(e)
    #有异常就回滚
    conn.rollback()

# 关闭链接
cursor.close()
conn.close()

这里我存的是MySql，而这次使用非常好使的插入方法：若主键数据相同，则_更新_选中列的数据。一般都是主键相同会更新所有列的数据，其Sql语句格式为：

‘Inser into 表名 (列名1,列名2,…列名n) values (%s,%s,...%s) on duplicate key update 列名1 = values(列名1) 列名2=values(列名2) … 列名n=values(列名n)'

以下函数就是将dataframe存储到MySql的方法，数据会自动增加，参数是“dataframe”和“数据库表名”，里面包含Sql语句的构建过程：

def Store_IpProxy(df,table_name):

    # 使用pandas过程中，数据转化成DataFrame格式会将缺失值会用NAN填充, 如果直接将数据用pymysql写入数据库会报错！
    #
    # 此时需要将NAN替换成None , 因为None插入数据库会被填写Null  也就是数据库中的空值

    # 存储列名
    keys = df.keys()
    values=df.values.tolist()

    key_sql=','.join(keys)
    value_sql=','.join(['%s']*df.shape[1])

    # !important构建Sql语句，若主键数据存在，则_替换_要替换的。
    insert_sql = 'insert into %s (%s) values (%s) on duplicate key update' % (table_name,key_sql,value_sql)
    update_str = ','.join([" {key} = values({key})".format(key=key) for key in keys])
    insert_sql += update_str

    # 创建连接对象
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='331303', charset='utf8', db='netsources')

    # 提交数据库操作
    try:
        conn.cursor().executemany(insert_sql,values)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()
    conn.cursor().close()
    conn.close()

注意：
1.pandas将数据转换为DataFrame时会把缺失值用nan填充~
2.如果直接将数据用pymysql写入数据库会报错！此时需要将nan替换成None , 因为None插入数据库会被填写Null 也就是数据库中的空值。

全表替换：df=df.where(df.notnull(),None)
单列替换：df[‘列名1’]=df[‘列名1’].where(df.notnull(),None)

3.pd里datetime64[ns]类型的日期时间列也是存不进MySql的！important
修复办法是：不要使用pd.to_datetime()方法！这方法转换出来的就是datetime[64ns]，不是python中的datetime类型。

# df[df.columns[8]] = df[df.columns[8]].str.replace('20-', '2020-')
# df[df.columns[8]] = pd.to_datetime(df[df.columns[8]].str.replace('20-', '2020-'), format='%Y/%m/%d %H:%M')
df[df.columns[8]] = df[df.columns[8]].str.replace('20-', '2020-').apply(lambda x: time.strptime(x,'%Y-%m-%d %H:%M'))

使用.apply方法，用py的time库来转换得出的datetime.datetime格式，在df.dtypes 中显示的是object类型，。