将CSV文件读入MySQL并使用executemany而不是execute

fzsnzjdm  于 2023-01-08  发布在  Mysql
关注(0)|答案(1)|浏览(154)

我有一个python脚本,可以将一个大的(4GB!!!)CSV文件读入MySQL。它可以正常工作,但是速度很慢。CSV文件有超过400万行。而且要将所有记录插入数据库需要很长时间。
我能举个例子说明在这种情况下如何使用executemany吗?
下面是我的代码:

source = os.path.join('source_files', 'aws_bills', 'march-bill-original-2019.csv')
try:
    with open(source) as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        next(csv_reader)
        insert_sql = """ INSERT INTO billing_info (InvoiceId, PayerAccountId, LinkedAccountId, RecordType, RecordId, ProductName, RateId, SubscriptionId, PricingPlanId, UsageType, Operation, AvailabilityZone, ReservedInstance, ItemDescription, UsageStartDate, UsageEndDate, UsageQuantity, BlendedRate, BlendedCost, UnBlendedRate, UnBlendedCost, ResourceId, Engagement, Name, Owner, Parent) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """
        #for row in csv_reader:
        for row_idx, row in enumerate(csv_reader):
            try:
                cursor.execute(insert_sql,row)
                #cursor.executemany(insert_sql, 100)
                mydb.commit()
                print('row', row_idx, 'inserted with LinkedAccountId', row[2], 'at', datetime.now().isoformat())
            except Exception as e:
                print("MySQL Exception:", e)
        print("Done importing data.")

同样,这段代码可以将记录插入到数据库中。但如果我能得到一个如何做的例子,我希望用executemany来加速这一过程。

svdrlsy4

svdrlsy41#

晚安我发现这个问题有点老了,我不知道你是否还需要它。我最近做了一些类似的事情,最初我把csv转换成一个列表,这样executemany函数就可以接受数据,在执行请求后,用列表传递它的插入,在你的例子中,它看起来像这样:

import pandas as pd

df = pd.read_csv(r'path_your_csv')
df1=pd.DataFrame(df)
df1=df1.astype(str)
List_Values=df1.values.tolist()
insert_sql = """ INSERT INTO billing_info (InvoiceId, PayerAccountId, LinkedAccountId, RecordType, RecordId, ProductName, RateId, SubscriptionId, PricingPlanId, UsageType, Operation, AvailabilityZone, ReservedInstance, ItemDescription, UsageStartDate, UsageEndDate, UsageQuantity, BlendedRate, BlendedCost, UnBlendedRate, UnBlendedCost, ResourceId, Engagement, Name, Owner, Parent) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """

cursor.executemany(insert_sql, List_Values)

相关问题