[defog] Postgres table names should be passed along with their respective schemas

[JeevansSP]

I was trying to generate postgre schema

from defog import Defog

defog = Defog()

schema = defog.generate_postgres_schema(tables=['demos.restaurants','demos.restaurant_menu'],  upload=False)

But was getting an empty schema

Connection details found. Reading connection details from file...
Connection details saved to C:\Users\admin6996\.defog\connection.json.
Retrieved the following tables:
        demos.restaurants
        demos.restaurant_menu
Getting schema for each table in your database...
Getting foreign keys for each table in your database...
Getting indexes for each table in your database...
Sending the schema to the defog servers and generating a Google Sheet. This might take up to 2 minutes...
{'demos.restaurants': [], 'demos.restaurant_menu': []}

And if I pass the tables without the schema prefix I would get the below error

from defog import Defog

defog = Defog()

print(defog.generate_postgres_schema(tables=['restaurants','restaurant_menu'],  upload=False))

(venv) PS D:\> & "d:/work/venv/Scripts/python.exe" "d:/work/degog_poc.py"
Connection details found. Reading connection details from file...
Connection details saved to C:\Users\admin6996\.defog\connection.json.
Retrieved the following tables:
        restaurants
        restaurant_menu
Getting schema for each table in your database...
Getting foreign keys for each table in your database...
Traceback (most recent call last):
  File "d:\work\degog_poc.py", line 23, in <module>
    print(defog.generate_postgres_schema(tables=['restaurants','restaurant_menu'],  upload=False))
  File "D:\work\venv\lib\site-packages\defog\__init__.py", line 260, in generate_postgres_schema
    cur.execute(query)
psycopg2.errors.UndefinedTable: relation "restaurants" does not exist
LINE 6:                 AND conrelid::regclass IN ('restaurants'::re...

[wongjingping]

Hi @JeevansSP , just checking if you could share your ~/.defog/connection.json (with the credentials redacted)? I believe that the schema/db details should be in that file already and you shouldn't need to specify the schema/database when using the python library.

[JeevansSP]

{
    "api_key": "key",
    "db_type": "postgres",
    "db_creds": {
        "host": "url",
        "port": "5432",
        "database": "db",
        "user": "postgres",
        "password": "pass"
    }
}

Even during the defog init I would get the error where tables had empty schema generated hence it was failing
Also the fix I had pushed takes care of situation when tables passed into generate_postgres_schema belong to two different schemas, as the postgre query fetching the column name and type to generate schema was only matching for table_name in the where clause and not the table_schema

[wongjingping]

Thanks for the clarification and the connection.json file. To check my understanding: the database name here is db, the schema is demos and the tables of interest are restaurants and restaurant_menu?

[JeevansSP]

Yes you are right