Python-Pandas学习之将数据帧（Dataframe）的数据插入到数据中，并且只插入新的数据

在使用数据帧的过程中，我们经常会需要数据库写入操作，特别是大量的基础数据。

在有的数据中，我们是需要设置主键或者唯一项的，那么这个时候，如果还是无脑的调用 to_sql，就会经常出现一个错误，说数据库的主键或唯一项冲突。

这个时候，我们就需要对新数据做一下处理，需要先把和数据库中的数据重合的数据删除掉，才能插入新数据。

思路就是，根据条件，将新数据中可能出现的情况，使用 read_sql 的方式从数据库中读取出来，然后将两个数据帧合并，去除里面重复或冲突的项，最后把新数据插入到数据库中去。

思路是这么个思路，但是做的方式有多种的，我这里就有两种方式：

• 一种是直接从数据库去除要出和新数据对比的数据，将两个数据放在数据帧中做对比去重处理；
• 还有一种是利用数据的语言进行处理，先把新数据加入到一个临时的表里面，然后联合两个表找出新数据，最后把新数据插入到数据库的表中，删除临时表；

下面是代码，两种方式写在一些，对比了下时间，相对来说，使用第二种效率要高一丢丢吧，但也影响不大

import os
import sys
import time
import pandas as pd
import numpy as np
import sqlite3
import threading
from timeit import default_timer as timer

os.path.dirname(os.path.abspath(__file__))

def clean_df_db_dups(df, tablename, engine, dup_cols=[], 
                         filter_continuous_col=None, filter_categorical_col=None):
    """
    从数据帧中删除已经存在于数据库中的行
    必要参数:
        df : 需要处理的数据帧
        engine: SQLAlchemy 数据库链接句柄
        tablename: 数据表名
        dup_cols: 需要检测重复的列名，list
    可选参数:
        filter_continuous_col: 判断一段时间内，是否有重复项，这里传递的是 datetime 类型的数据，可以自己改成不同条件
        filter_categorical_col : 增加条件，用于精确查找进行去重处理
    返回:
        返回的数据帧中只剩下唯一项，并且和数据库不冲突的唯一项
    """
    args = 'SELECT %s FROM %s' %(', '.join(['"{0}"'.format(col) for col in dup_cols]), tablename)
    args_contin_filter, args_cat_filter = None, None
    if filter_continuous_col is not None:
        if df[filter_continuous_col].dtype == 'datetime64[ns]':
            args_contin_filter = """ "%s" BETWEEN Convert(datetime, '%s') 
                                          AND Convert(datetime, '%s')""" %(filter_continuous_col, 
                              df[filter_continuous_col].min(), df[filter_continuous_col].max())

    
    if filter_categorical_col is not None:
        args_cat_filter = ' "%s" in(%s)' %(filter_categorical_col, 
                          ', '.join(["'{0}'".format(value) for value in df[filter_categorical_col].unique()]))

    if args_contin_filter and args_cat_filter:
        args += ' Where ' + args_contin_filter + ' AND' + args_cat_filter
    elif args_contin_filter:
        args += ' Where ' + args_contin_filter
    elif args_cat_filter:
        args += ' Where ' + args_cat_filter

    df.drop_duplicates(dup_cols, keep='last', inplace=True)
    df = pd.merge(df, pd.read_sql(args, engine), how='left', on=dup_cols, indicator=True)
    df = df[df['_merge'] == 'left_only']
    df.drop(['_merge'], axis=1, inplace=True)
    return df


def setup(db_con, tablename):
    db_con.execute("""DROP TABLE IF EXISTS "%s" """ % (tablename))

    db_con.execute("""CREATE TABLE "%s" (
                  "A" INTEGER,
                  "B" INTEGER,
                  "C" INTEGER,
                  "D" INTEGER,
                  CONSTRAINT pk_A_B PRIMARY KEY ("A","B")) 
                  """ % (tablename))

if __name__ == '__main__':
    TABLENAME = "inter_test"
    conn = sqlite3.connect("test_in.db")
    print('设置数据库表')
    setup(conn, TABLENAME)

    try:
        i=0
        prev = timer()
        start = timer()
        for i in range(10):
            print('加入随机数据 %s' %(str(i)))

            # 生成随机的数据帧数据
            df = pd.DataFrame(
                np.random.randint(0, 500, size=(100000, 4)), columns=list('ABCD'))

            # 调用去重函数，将本次生成的数据中有重复主键的数据删除掉
            df = clean_df_db_dups(df, TABLENAME, conn, dup_cols=['A', 'B'])
            print('去重之后，数据帧剩余行数: %s' %(df.shape[0]))

            # 将数据加入到数据库中
            df.to_sql(TABLENAME, conn, if_exists='append', index=False)

            end = timer()
            elapsed_time = end - prev
            prev = timer()
            print('完成插入数据花费 %s 秒!' %(elapsed_time))

        end = timer()
        elapsed_time = end - start
        print('第一种方式完成插入花费 %s 秒!' %(elapsed_time))
  
        inserted = pd.read_sql('SELECT count("A") from %s' %(TABLENAME), conn)
        print('插入 %s 行新数据到数据库！' %(inserted.iloc[0]['count("A")']))

        print('\n设置数据库表')
        setup(conn, TABLENAME)
        print('\n')

        i=0
        prev = timer()
        start = timer()
        for i in range(10):
            print('加入随机数据 %s' %(str(i)))

            # 生成随机的数据帧数据
            df = pd.DataFrame(
                np.random.randint(0, 500, size=(100000, 4)), columns=list('ABCD'))
            
            # 先自身去重，可能读取的数据中就有重复数据。这里设定 user_pseudo_id 和 ga_session_id 为联合主键
            df.drop_duplicates(['A', 'B'], keep='last', inplace=True)

            # 将要加入的新数据插入到一个新的临时表中
            df.to_sql('temp', conn, if_exists='replace', index=False)
            connection = conn.cursor()

            # 下面这句 sql 语句表示：
            # 先取 a（temp） 和 b（user_engagement）两个表中 a 的完全集合，
            #   条件是 a 的 user_pseudo_id 和 ga_session_id 和 b 的一样，并且 b 表中 user_pseudo_id 为空也就是不存在的一项
            #   这么做就排除掉 a 中有 b 的重复项了
            # 然后 a 中所有满足条件的数据，插入到 b 表之中，这样数据就不存在冲突的了
            args1 = f""" INSERT INTO "{TABLENAME}"
                        SELECT * FROM 
                        (SELECT a.* 
                        FROM "temp" a LEFT OUTER JOIN "{TABLENAME}" b 
                            ON (a."A" = b."A" and a."B"=b."B")
                            WHERE b."A" is null) b"""
            result = connection.execute(args1)

            # 删除临时创建的 temp 表
            args2 = """ DROP Table If Exists "temp" """
            connection.execute(args2)
            connection.close()
            
            end = timer()
            elapsed_time = end - prev
            prev = timer()
            print('完成插入数据花费 %s 秒!' %(elapsed_time))
            
        end = timer()
        elapsed_time = end - start
        print('第二种方式完成插入花费 %s 秒!' %(elapsed_time))
        inserted = pd.read_sql('SELECT count("A") from %s' %(TABLENAME), conn)
        print('插入 %s 行新数据到数据库！' %(inserted.iloc[0]['count("A")']))
              
    except KeyboardInterrupt:
        print("插入出错... 退出...")

conn.commit()
conn.close()

最后，我运行的结果是：

[Running] python -u "/Users/guojicheng/Desktop/Python/3/Projects/CsvToDatabase/maintest2.py"
设置数据库表
加入随机数据 0
去重之后，数据帧剩余行数: 82205
完成插入数据花费 0.35755471799999994 秒!
加入随机数据 1
去重之后，数据帧剩余行数: 55443
完成插入数据花费 0.25805679800000003 秒!
加入随机数据 2
去重之后，数据帧剩余行数: 36913
完成插入数据花费 0.28262331800000007 秒!
加入随机数据 3
去重之后，数据帧剩余行数: 24994
完成插入数据花费 0.310438875 秒!
加入随机数据 4
去重之后，数据帧剩余行数: 16609
完成插入数据花费 0.31721769300000013 秒!
加入随机数据 5
去重之后，数据帧剩余行数: 11116
完成插入数据花费 0.30040306500000025 秒!
加入随机数据 6
去重之后，数据帧剩余行数: 7504
完成插入数据花费 0.3106185309999998 秒!
加入随机数据 7
去重之后，数据帧剩余行数: 5011
完成插入数据花费 0.303050877 秒!
加入随机数据 8
去重之后，数据帧剩余行数: 3356
完成插入数据花费 0.29538198299999996 秒!
加入随机数据 9
去重之后，数据帧剩余行数: 2260
完成插入数据花费 0.3035904989999998 秒!
第一种方式完成插入花费 3.038973234 秒!
插入 245411 行新数据到数据库！

设置数据库表


加入随机数据 0
完成插入数据花费 0.22206225700000015 秒!
加入随机数据 1
完成插入数据花费 0.2525819679999999 秒!
加入随机数据 2
完成插入数据花费 0.26193947200000034 秒!
加入随机数据 3
完成插入数据花费 0.27443220700000026 秒!
加入随机数据 4
完成插入数据花费 0.2703861139999999 秒!
加入随机数据 5
完成插入数据花费 0.26506472900000055 秒!
加入随机数据 6
完成插入数据花费 0.26182276800000004 秒!
加入随机数据 7
完成插入数据花费 0.24834169199999945 秒!
加入随机数据 8
完成插入数据花费 0.24788912599999957 秒!
加入随机数据 9
完成插入数据花费 0.24383380800000065 秒!
第二种方式完成插入花费 2.548393745 秒!
插入 245361 行新数据到数据库！

[Done] exited with code=0 in 6.045 seconds

可以看到，第二种方式会快1秒左右！