python3 Pandas数据转SQL

开发场景:
    对df.to_sql()函数入库不满意,改用SQL语句入库
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Author  : jia666
# Time    : 2021/4/28 11:34

import pandas as pd
import pymysql


def df_trans_sql(tablename, df):
    '''df数据转sql插入语句-存在则忽略
    传值:
        表名称,df
    返回值:拼接后的SQL语句
    '''

    columns = df.columns.tolist()  # 表字段
    values = df.values.tolist()  # 表字段对应的值

    tmp = []
    for v in values:
        value = [pymysql.escape_string(str(s)) for s in v]
        tmp.append('("' + '","'.join(value) + '")')
    SQL = '''INSERT IGNORE INTO `{0}`({1}) VALUES {2};'''.format(tablename, '`' + '`,`'.join(columns) + '`',
                                                                 ','.join(tmp))
    SQL = SQL.replace('%', '%%')  # 特殊字符转换
    return SQL


if __name__ == '__main__':
    tmp_data_list = [{'pid': '29806862300260', 'columns': 'directors', 'total': 1},
                     {'pid': '29806862300260', 'columns': 'changeRecord', 'total': 8},
                     {'pid': '29806862300260', 'columns': 'annuallist', 'total': 7},
                     {'pid': '29806862300260', 'columns': 'headcompany', 'total': 1},
                     {'pid': '29806862300260', 'columns': 'lawWenshu', 'total': 1},
                     {'pid': '29806862300260', 'columns': 'getCompPersonList', 'total': 1},
                     {'pid': '29806862300260', 'columns': 'license', 'total': 2}]
    df = pd.DataFrame(tmp_data_list)
    sql = df_trans_sql('aqc_test', df)
    print(sql)


    df=df[0:1]#单条验证
    sql = df_trans_sql('aqc_test', df)
    print(sql)


相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页