python - 使用 to_sql 将数据从 pandas dataframe 导入 SQL 数据库时 PC 挂起

Question

所以，我目前正在处理大量数据(62个文件，每个~150mb，每个~350万行)，我需要将这些平面文件上传到MySQL数据库，所以我使用pandas库来读取.csv 文件并创建一个包含所有数据的数据框。

关于数据:

数据被分成多个文件，因为每个文件包含有关不同日期的特定位置(坐标)的信息。例如:一个文件包含特定日期大约 350 万个坐标的数据(这些坐标是固定的并且在所有文件中都是通用的)，我有 62 个这样的文件，意味着 62 个不同日期的数据。
我想在一个表中查看所有这些数据，即我想将额外的列(对应于位置列表的不同日期的信息)添加到表中，并且常见的列(位置/坐标)应该只出现一次在此表中。 here 展示了我希望表中的数据如何呈现。

到目前为止，我的代码如下所示:

#relevant imports:
import os
import MySQLdb as sql
import numpy as np
from functools import reduce
from sqlalchemy import create_engine
import glob
import pandas as pd

#Accessing database:
engine = create_engine("mysql://root:PASSWORD@localhost/DBNAME")
con = engine.connect()

#Directory change:

path="C:\\Users\\DELL\\Desktop\\DataAnalysis-Internship 18'\\extracted data\\"
os.chdir(path)

#listing file names:
files=glob.glob("IN.201*.csv")
num_files=len(files)

#DATAFRAMES:
#initialiasing first dataframe:
df=pd.read_csv(path+files[0])
df.rename(columns={"avg":"avg"+str(0)}, inplace=True)

for file in files[1:]:
  i=1
  df_next=pd.read_csv(path+file)
  df_next.rename(columns={"avg":"avg"+str(i)}, inplace=True)
  df=df.merge(df_next, on=['lon','lat','country'])
  i=i+1

df.to_sql(con=con, name='final_table', if_exists='replace', chunksize=10000)

con.close() 

当我运行此代码时，我的电脑开始执行命令，性能慢慢下降，最终电脑开始挂起。可能消耗了太多内存，可能是因为我正在从许多数据帧中创建一个数据帧，然后将其传输到数据库(我不确定)。

我现在应该做什么才能按照我想要的方式将所有这些文件上传到我的表中？有没有更优化的方法？我想过一次从 5 个文件合并和制作表格，然后使用“to_sql”命令中的属性“if_exists='append'”将这些多个数据帧连接到一个表中，而不是制作一个巨大的数据帧然后传输它，但是我不确定“append”属性是否会按照我想要的方式连接表。

Answer 1

我的猜测是，问题是由循环中发生的 df = df.merge 引起的

Never call DataFrame.append or pd.concat inside a for-loop. It leads to quadratic copying. Source: Why does concatenation of DataFrames get exponentially slower?

尝试将所有数据帧添加到列表 df_list 中，然后在末尾将它们连接一次:

import pandas as pd

df_list = []
df_list.append(pd.DataFrame(dict(a=[1, 2, 3], b=[4, 5, 6])))
df_list.append(pd.DataFrame(dict(c=[1, 2, 3], d=[4, 5, 6])))

pd.concat(df, axis=1)
Out[]:
   a  b  c  d
0  1  4  1  4
1  2  5  2  5
2  3  6  3  6