连接所有的PostgreSQL表并创建一个Python字典

Question

我需要连接 所有 PostgreSQL 表并将它们转换为 Python 字典。数据库中有 72 张表，总列数超过 1600。

我编写了一个简单的 Python 脚本来连接几个表格，但由于 内存错误，无法连接所有表格。在脚本执行期间，所有内存都被占用。我在一台新的虚拟服务器上运行此脚本，该服务器配备 128GB RAM 和 8 个 CPU。在 lambda 函数执行期间失败了。

如何改进以下代码以执行 所有 表格连接？

from sqlalchemy import create_engine
import pandas as pd

auth = 'user:pass'
engine = create_engine('postgresql://' + auth + '@host.com:5432/db')

sql_tables = ['table0', 'table1', 'table3', ..., 'table72']        
df_arr = []
[df_arr.append(pd.read_sql_query('select * from "' + table + '"', con=engine)) for table in sql_tables]

df_join = reduce(lambda left, right: pd.merge(left, right, how='outer', on=['USER_ID']), df_arr)
raw_dict = pd.DataFrame.to_dict(df_join.where((pd.notnull(df_join)), 'no_data'))

print(df_join)
print(raw_dict)
print(len(df_arr))

对于我的目的，使用Pandas可以吗？还有更好的解决方案吗？

最终目标是将数据库数据去规范化以便将其索引为Elasticsearch文档，每个用户一个文档。

- srgbnd

---

你所有的72个表中有多少列？如果你的结果数据框架将会有<= 250列，那么你可以和应该在PostgreSQL中连接它们 - 你将不会遇到内存错误。 - MaxU - stand with Ukraine

为什么不告诉我们您的最终目标，这样我们就可以帮助您找到更好的解决方案。 - Neil McGuigan

最简单的方法是使用更大的机器。128MB的RAM不多。启动一个带有4GB的东西，看看它是否运行（虽然我怀疑它会）。你最好一次只从一个表中提取一行数据，而且可能还要从一个表中提取。72个表的连接将消耗大量内存和交换空间，并在Python中占用大量空间。 - Jonathan Vanasco

@JonathanVanasco 我使用拥有 128GB 的服务器。 - srgbnd

抱歉，我读错了。那是一个巨大的联接操作，你需要分步骤重新格式化数据。 - Jonathan Vanasco

Answer 1

为什么不创建一个Postgres函数而不是脚本？

以下是一些建议，可以帮助您避免内存错误：

- 您可以使用WITH子句更好地利用您的内存。 - 您可以创建一些物理表来存储数据库中不同组表的信息。这些物理表将避免使用大量内存。之后，您只需加入这些物理表即可。您可以为此创建一个函数。 - 您可以通过去规范化需要的表来创建数据仓库。 - 最后但并非最不重要的：确保您正确使用索引。

- Luis Teijon

---

总列数大于1600。 - srgbnd

如果您尝试使用数据仓库呢？ - Luis Teijon

你建议使用哪种数据仓库服务？ - srgbnd

创建数据仓库时，您可以通过合并、修改或删除列和行来对表进行去规范化。这可能是您计划进行的大型连接的一种方法。 - Luis Teijon

Answer 2

我不确定这会不会有帮助，但你可以尝试使用 pd.concat

raw_dict = pd.concat([d.set_index('USER_ID') for d in df_arr], axis=1)

或者，为了更加区分

raw_dict = pd.concat([d.set_index('USER_ID') for d in df_arr], axis=1, keys=sql_tables)

---

如果这不是有帮助的，请告诉我，我会将其删除。

- piRSquared

---

在执行pd.concat过程中，我遇到了以下错误：https://gist.github.com/SergeyBondarenko/f78444f709cffec3e6777d3a16e38f2f - srgbnd