Postgres Python Option

[alevites]

First, let me just say how great this tool is. It is now my go-to for flattening JSON files. And now the question...

I'm trying to use the Postgres adapter in the python library but keep getting the following error:

FileNotFoundError: [Errno 2] No such file or directory: 'C:\Users\al14435\Downloads\flatterer_test\fields.csv'

Are there other options that need to be enabled/disabled to get this working? I am able to successfully create CSVs, parquet, etc. files using the python lib but the Postgres connector is giving me fits. When working with other output types, I see the fields.csv file and in that exact directory but with this adapter, it seems to get cleaned up before being used for the write out to Postgres. In fact, in this scenario, the whole output folder is gone after the error. Here's a snippet of the py script that's throwing the error:

output = flatterer.flatten(input=inputPath, output_dir=outputPath,
postgres='postgres://un:pw@host/db', postgres_schema='public',
csv=False, parquet=False, dataframe=False, sql_scripts=True, force=True)

Another thing maybe worth mentioning is that using the CLI, the Postgres adapter worked fine for me. Could it be something with the python lib that I'm missing?

Any help would be appreciated!

Alex

EDIT: I made the flatten call simpler (removed everything except the input, postgres, and postgres_schema options).

output = flatterer.flatten(input=inputPath, postgres='postgres://un:pw@host/db', postgres_schema='public')

Here's the full error trace (looks like it's calling shutil.rmtree, perhaps before writing to Postgres?):

Traceback (most recent call last):
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\flatterer_init_.py", line 167, in flatten
fields=pandas.read_csv(os.path.join(output_dir, 'fields.csv')),
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\util_decorators.py", line 211, in wrapper
return func(*args, **kwargs)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\util_decorators.py", line 331, in wrapper
return func(*args, **kwargs)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\parsers\readers.py", line 950, in read_csv
return _read(filepath_or_buffer, kwds)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\parsers\readers.py", line 605, in _read
parser = TextFileReader(filepath_or_buffer, **kwds)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\parsers\readers.py", line 1442, in init
self._engine = self._make_engine(f, self.engine)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\parsers\readers.py", line 1735, in _make_engine
self.handles = get_handle(
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\io\common.py", line 856, in get_handle
handle = open(
FileNotFoundError: [Errno 2] No such file or directory: 'C:\Users\al14435\AppData\Local\Temp\flatterer-wdoepmev\fields.csv'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "c:\Users\al14435\OneDrive - Baker Tilly US\Documents\ALevites\MRF\MRF3.py", line 14, in
output = flatterer.flatten(input=inputPath, postgres='postgres://un:pw@host/db, postgres_schema='public')
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\site-packages\flatterer_init_.py", line 190, in flatten
shutil.rmtree(output_dir)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\shutil.py", line 759, in rmtree
return _rmtree_unsafe(path, onerror)
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\shutil.py", line 610, in _rmtree_unsafe
onerror(os.scandir, path, sys.exc_info())
File "C:\Users\al14435\AppData\Local\Programs\Python\Python39\lib\shutil.py", line 607, in _rmtree_unsafe
with os.scandir(path) as scandir_it:
FileNotFoundError: [WinError 3] The system cannot find the path specified: 'C:\Users\al14435\AppData\Local\Temp\flatterer-wdoepmev'

[kindly]

There seems to be an issue with errors not being raised early when using postgres for some reason.

There is some underlying issue with the command you ran, but what that issue is, is not being reported properly. I am working on a fix for that.

My guess is, for your case though, is that you should be using either drop=True or evolve=True
e.g.

output = flatterer.flatten(input=inputPath, postgres='postgres://un:pw@host/db', postgres_schema='public', drop=True)

drop will remove existing tables and evolve will add to existing tables, migrating tables as needed. If neither is set and the tables already exist in the database, it will fail. However, for some reason, there is no error raised that says that the reason is due to a table already existing, and you are getting the above error which is misleading. Or it could be some other issue that is also not being reported properly.

[kindly]

Just released a new version of flatterer. This will not fix the issue, but should at least give back an error that explains what is happening. So could you upgrade and try?

Also, if at all possible, could you give me some example JSON that fails in this way, as I think this failure only happens for some data.

[alevites]

I've updated to 19.6 but getting the same error. The JSON that I'm using is below (link to gz that I run directly through flatterer):

inputPath = r'https://uhc-tic-mrf.azureedge.net/public-mrf/2023-06-01/2023-06-01_MD-Individual-Practice-Association--Inc-_Insurer_PPO---NDC_PPO-NDC_in-network-rates.json.gz'

(this is healthcare-related data but it is publicly accessible, ie. no sensitive info)

[alevites]

Apologies, I take that back, I only saw the last part of the error, which was the same but the initial portion now points to some sort of connection error to Postgres. Still strange because I am able to connect using DBeaver and even psycopg2 with no issues.

Either way, now I know where the true error is happening and can debug from here. Appreciate all the help!

[kindly]

I tried your file, and it did not work for me either. The issue I was having, was that the indexes created were too long and being truncated and made indexes that had the same name.

I have fixed the index names to be unique and shorter and released a new version. The above file works for me now.

[alevites]

Thank you! I will try tomorrow morning. The next trick will be to run a full large file through flatterer, as the one I sent you was a "small" sample (I know, still fairly large). Here's the huge one I want to try (about 100gb unzipped).

inputPath = r'https://uhc-tic-mrf.azureedge.net/public-mrf/2023-06-01/2023-06-01_UnitedHealthcare-Insurance-Company-of-New-York_Insurer_PPO-00_P3_in-network-rates.json.gz'

Unrelated to this issue, so not expecting you to run this. Thanks again for all the help and keep up the awesome work!