有没有一种更快的方法可以使用Python将数百万行从Excel移动到SQL数据库

Question

我是一名金融分析师，在Python方面有大约两个月的经验，我正在从事一个使用Python和SQL自动编译报告的项目。该过程包括访问保存在共享驱动器中的数量不断变化的Excel文件，从每个文件中提取两个选项卡(摘要和引用(，并将数据集组合成两个大的"；引用"以及"；摘要"；表。下一步是从每个列中提取各种列，组合、计算等。

问题是数据集最终是3.4毫米的行和大约30列。我在下面写的程序是有效的，但完成第一部分(创建数据帧列表(需要40分钟，创建数据库和导出数据需要4.5小时，更不用说使用大量内存了。

我知道一定有更好的方法来实现这一点，但我没有CS背景。如有任何帮助，我们将不胜感激。

import os
import pandas as pd
from datetime import datetime
import sqlite3
from sqlalchemy import create_engine
from playsound import playsound
reportmonth = '2020-08'
month_folder = r'C:syncedSharePointFolder'
os.chdir(month_folder)
starttime = datetime.now()
print('Started', starttime)
c = 0
tables = list()
quote_combined = list()
summary_combined = list()
# Step through files in synced Sharepoint directory, select the files with the specific
# name format. For each file, parse the file name and add to 'tables' list, then load
# two specific tabs as pandas dataframes.  Add two columns, format column headers, then 
# add each dataframe to the list of dataframes. 
for xl in os.listdir(month_folder):
if '-Amazon' in xl:
ttime = datetime.now()
table_name = str(xl[11:-5])
tables.append(table_name)
quote_sheet = pd.read_excel(xl, sheet_name='-Amazon-Quote')
summary_sheet = pd.read_excel(xl, sheet_name='-Amazon-Summary')

quote_sheet.insert(0,'reportmonth', reportmonth)
summary_sheet.insert(0,'reportmonth', reportmonth)
quote_sheet.insert(0,'source_file', table_name)
summary_sheet.insert(0,'source_file', table_name)
quote_sheet.columns = quote_sheet.columns.str.strip()
quote_sheet.columns = quote_sheet.columns.str.replace(' ', '_')
summary_sheet.columns = summary_sheet.columns.str.strip()
summary_sheet.columns = summary_sheet.columns.str.replace(' ', '_')

quote_combined.append(quote_sheet)
summary_combined.append(summary_sheet)

c = c + 1

print('Step', c, 'complete: ', datetime.now() - ttime, datetime.now() - starttime)
# Concatenate the list of dataframes to append one to another.  
# Totals about 3.4mm rows for August
totalQuotes = pd.concat(quote_combined)
totalSummary = pd.concat(summary_combined)     
# Change directory, create Sqlite database, and send the combined dataframes to database
os.chdir(r'H:AaronSDatabases')
conn = sqlite3.connect('AMZN-Quote-files_' + reportmonth)
cur = conn.cursor()
engine = create_engine('sqlite:///AMZN-Quote-files_' + reportmonth + '.sqlite', echo=False)
sqlite_connection = engine.connect()
sqlite_table = 'totalQuotes'
sqlite_table2 = 'totalSummary'
totalQuotes.to_sql(sqlite_table, sqlite_connection, if_exists = 'replace')    
totalSummary.to_sql(sqlite_table2, sqlite_connection, if_exists = 'replace')  

print('Finished. It took: ', datetime.now() - starttime)
'''

Answer 1

我看到了一些你可以做的事情。首先，由于你的第一步只是将数据传输到你的SQL数据库，所以你不一定需要将所有文件相互附加。你可以一次攻击一个文件(这意味着你可以多处理！(——然后，无论需要完成什么计算，都可以稍后完成。这也会减少RAM的使用，因为如果你的文件夹中有10个文件，你就不会同时加载所有10个文件
我推荐以下产品：

1. 构造需要访问的文件名数组
2. 编写一个可以获取文件名的包装函数，打开并解析文件，然后将内容写入MySQL数据库
3. 使用Python多处理。池类以同时处理它们。例如，如果你运行4个进程，你的任务就会快4倍！如果您需要从这些数据中派生计算，因此需要对其进行聚合，请在数据进入MySQL DB后执行此操作。这会快得多
4. 如果您需要根据聚合数据定义一些计算，请在MySQL数据库中立即执行。SQL是一种非常强大的语言，几乎所有的东西都有一个命令

我添加了一个简短的代码片段，向您展示我所说的内容：(

from multiprocessing import Pool
PROCESSES = 4
FILES = []
def _process_file(filename):
print("Processing: "+filename)
pool = Pool(PROCESSES)
pool.map(_process_file, FILES)

SQL澄清：您不需要为移动到SQL的每个文件都有一个独立的表！您可以根据给定的模式创建一个表，然后逐行将所有文件中的数据添加到该表中。这基本上就是您用来从DataFrame到表的函数所做的，但它创建了10个不同的表。您可以在这里查看一些将行插入表的示例

但是，在您所拥有的特定用例中，将if_exists参数设置为"append"应该可以工作，正如您在评论中提到的那样。我只是在中添加了前面的参考资料，因为你提到你对Python相当陌生，我在金融行业的很多朋友都发现，对SQL有一个稍微细致的理解是非常有用的。

Answer 2

试试这个，这里的大部分时间都是在将数据从excel加载到Dataframe的过程中。我不确定下面的脚本会把时间减少到几秒钟内，但它会减少RAM的负担，这反过来会加快进程。它可能会将时间减少至少5-10分钟。由于我无法访问数据，我无法确定。但是你应该试试这个

import os
import pandas as pd
from datetime import datetime
import sqlite3
from sqlalchemy import create_engine
from playsound import playsound
os.chdir(r'H:AaronSDatabases')
conn = sqlite3.connect('AMZN-Quote-files_' + reportmonth)
engine = create_engine('sqlite:///AMZN-Quote-files_' + reportmonth + '.sqlite', echo=False)
sqlite_connection = engine.connect()
sqlite_table = 'totalQuotes'
sqlite_table2 = 'totalSummary'

reportmonth = '2020-08'
month_folder = r'C:syncedSharePointFolder'
os.chdir(month_folder)
starttime = datetime.now()
print('Started', starttime)

c = 0
tables = list()
for xl in os.listdir(month_folder):
if '-Amazon' in xl:
ttime = datetime.now()

table_name = str(xl[11:-5])
tables.append(table_name)

quote_sheet = pd.read_excel(xl, sheet_name='-Amazon-Quote')
summary_sheet = pd.read_excel(xl, sheet_name='-Amazon-Summary')

quote_sheet.insert(0,'reportmonth', reportmonth)
summary_sheet.insert(0,'reportmonth', reportmonth)

quote_sheet.insert(0,'source_file', table_name)
summary_sheet.insert(0,'source_file', table_name)

quote_sheet.columns = quote_sheet.columns.str.strip()
quote_sheet.columns = quote_sheet.columns.str.replace(' ', '_')

summary_sheet.columns = summary_sheet.columns.str.strip()
summary_sheet.columns = summary_sheet.columns.str.replace(' ', '_')

quote_sheet.to_sql(sqlite_table, sqlite_connection, if_exists = 'append')    
summary_sheet.to_sql(sqlite_table2, sqlite_connection, if_exists = 'append')  

c = c + 1
print('Step', c, 'complete: ', datetime.now() - ttime, datetime.now() - starttime)