Join all PostgreSQL tables and make a Python dictionary
<p>I need to join <strong>all</strong> PostgreSQL tables and convert them in a Python dictionary. There are 72 tables in the database. The total number of columns is greater than <strong>1600</strong>. </p>

<p>I wrote a simple Python script that joins several tables but fails to join all of them due to <a href="https://gist.github.com/SergeyBondarenko/a753689a86b9af209e6dd6ef2c2f7eef" rel="nofollow">the memory error</a>. All memory is occupied during the script execution. And I run the script on a new virtual server with <strong>128GB</strong> RAM and 8 CPU. It fails during the lambda function execution. </p>

<p>How could the following code be improved to execute <strong>all</strong> tables join?</p>

<pre><code>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))
</code></pre>

<p>Is it ok to use <a href="http://pandas.pydata.org/pandas-docs/stable/" rel="nofollow">Pandas</a> for my purpose? Are there better solutions?</p>

<p>The ultimate goal is to <a href="https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html" rel="nofollow">denormalize</a> DB data to be able to index it into <a href="https://www.elastic.co" rel="nofollow">Elasticsearch</a> as documents, one document per user.</p>

<p>Why don't you create a postgres function instead of script?</p>

<p>Here are some advises that could help you to avoid the memory error:</p>

<ul>
<li>You can use <strong>WITH</strong> clause which makes better use of your memory.</li>
<li>You can create some physical tables for storing the information of
different groups of tables of your database. These physical tables will avoid to use a great amount of memory. After that, all you have to do is joining only those physical tables. You can create a function for it.</li>
<li>You can create a Data Warehouse by denormalizing the tables you need. </li>
<li>Last but not least: Make sure you are using <strong>Indexes</strong> appropriately.</li>
</ul>

python database postgresql pandas elasticsearch