Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
JeevansSP opened this issue Sep 13, 2023 · 4 comments · May be fixed by #9
Open

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

JeevansSP opened this issue Sep 13, 2023 · 4 comments · May be fixed by #9

Comments

@JeevansSP
Copy link

JeevansSP commented Sep 13, 2023 •
edited
Loading

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...
@JeevansSP JeevansSP linked a pull request Sep 13, 2023 that will close this issue
Open
@wongjingping
Copy link
Collaborator

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
Copy link
Author

JeevansSP commented Sep 13, 2023 •
edited
Loading 

{
    "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
Copy link
Collaborator

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
Copy link
Author

Yes you are right

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[defog] Postgres table name format. JeevansSP/defog-python
2 participants
@wongjingping @JeevansSP