Using SQLAlchemy as an ORM for python - relation doesn't exist

Question

I'm having a problem with sqlalchemy in Python.

I have the following files :

base.py:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:mysecretpassword@localhost:5432/postgres',echo=True)
Base = declarative_base(engine)

Product.py:

from sqlalchemy import Table,Date,TEXT,Column,BIGINT,Integer,Boolean
from base import Base    

class Product(Base):

    __tablename__ = 'products'
    id = Column('id',BIGINT, primary_key=True)
    barcode = Column('barcode' ,BIGINT)
    productName = Column('name', TEXT)
    productType = Column('type', Integer)
    maufactureName=Column('maufacture_name',TEXT,nullable=True)
    manufactureCountry = Column('manufacture_country', TEXT)
    manufacturerItemDescription = Column('manufacture_description',TEXT)
    unitQuantity=Column('uniq_quantity',Integer)
    quantity=Column('quantity',Integer)
    quanityInPackage=Column('quantity_in_package',Integer)
    isWeighted=Column('is_weighted',Integer)
    picture=Column('picture_url',TEXT)


    def __init__(self,args...):
   .....

main.py:

from Product import Product
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from base import Base
Base.metadata.create_all()
Session = sessionmaker()
session=Session()
session.add(Product(...))
session.commit()

When I run the main I keep getting an error that the products relation doesn't exist :

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "products" does not exist

Any idea why ? From the sqlalchemy logs it doesn't seems like it even tries to create the table.

Dunno what *repository* is, but when you add that Base as the argument to your class then you *map* that model/class to specific table in your database. Please provide your models so we can figure out why your tables aren't created. Different files don't affect creation if all classes are subclasses of the same Base class (and those are bound to the same db_engine). — ipaleka, Aug 12 '19 at 10:35
By repository I meant the JPA Repository that we create in java for each DAO class. I added the sqlalchemy part in my class — JeyJ, Aug 12 '19 at 13:29
Did you already import all of your classes prior to `Base.metadata.create_all(engine)` call? Btw and OT: barcode shouldn't be your product's primary key, it could change and multiple barcodes can be assigned to a single product. — ipaleka, Aug 12 '19 at 13:40
What do you mean by "import all of your classes prior to .." ? Just the "import Product" command ?(I did that) I got your recommendation and changed the class according to it. — JeyJ, Aug 12 '19 at 16:21
By that, I mean that `Base.metadata.create_all(engine)` should at the time of calling have collected all the subclasses of Base. If you did import Product and then call `create_all`, then I don't know what is the reason for raised exception. Have you tried with file-based sqlite database, not that placed in memory? Should you create the database like with PostgreSQL it should be done with `db_engine.text("CREATE DATABASE database_name").execute()`? — ipaleka, Aug 12 '19 at 16:30
I'm not sure if it is the reason, but I put the following commands in each file that represents class (product,store etc...) : from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() Should I do it only once somewhere in a specific class or its fine that I import it before every class and create a new Base every time ? I'll try to create postgresql instance and check it again — JeyJ, Aug 12 '19 at 16:37
You should bind the same db_engine, call it like `declarative_base(engine)` where `engine` is that instance from your question, just switch the order, first `engine = ...` and then `Base = ...`. — ipaleka, Aug 12 '19 at 16:50
I switched to postgresql instance (used a container) and I also done what u wrote now but I'm still getting the same error. I'm updating the main post with the errors and code. — JeyJ, Aug 12 '19 at 16:52
I think that it might be connected to the fact that I initiate a base both in the product class and in the main files. @ipaleka what do u think ? — JeyJ, Aug 12 '19 at 17:40
Well, if you call that `Base.metadata.create_all(engine)` just after `Base = declarative_base(engine)` then you didn't import the right Product class as it has to be subclassed from that Base. — ipaleka, Aug 12 '19 at 17:46
so how can I share the same base instance between all my clases (product class, store class, main class) ? I tried to create a new python file and add the declartive_base command to there and then just import the new class in all the classes but I get the same error — JeyJ, Aug 12 '19 at 17:56
That's the proper way. Have you created the database, I suppose you've done that as the error kind of implies connection to a database. — ipaleka, Aug 12 '19 at 18:00
yeah I have a database working as a container.. When I look on the output of the sqlalchemy I dont see that it tries to create the tables.. I'm adding the log to the post — JeyJ, Aug 12 '19 at 18:02
What's that `def __init__(` in your Product class? That class' instances shouldn't be initialized that way. — ipaleka, Aug 12 '19 at 18:10
I have an __init__ function in the class, Its the class`s constructor. — JeyJ, Aug 12 '19 at 18:12
As I said, you're not supposed to have it there, the object relational mapper is the "owner" of that class, you can't mess with its internals, you may just expand its methods or so. You should follow some tutorial or official docs and strip all the code. Only when you've got it right you may add something that *might* work. — ipaleka, Aug 12 '19 at 18:15
I'm sending the constructor a dictionary, so what u says I must have only a default constructor ? According to the following link it should be fine : https://stackoverflow.com/questions/19258471/sqlalchemy-orm-init-method-vs — JeyJ, Aug 12 '19 at 18:18
I'm creating the products by reading them from files, afterwards I want to save them to a database. I have to use the init method — JeyJ, Aug 12 '19 at 18:21
If you added the engine as an argument to declaratibe_base then you shouldn't provide it to create_all, it is implied. I'm not sure if that helps, but try with `Base.metadata.create_all()` — ipaleka, Aug 12 '19 at 18:27
@ipaleka Tried it, still didnt help. I edited my main post with the current code so that it will be more readable. What do you think ? — JeyJ, Aug 12 '19 at 20:34
The point was to bind engine to base with`declarative_base(engine)` call. Rename that file from Base.py to base.py (no .py filename should have an uppercase letter in it), and create both engine and Base in it, and import it both in main.py. And you don't need to pass engine in create_all call. — ipaleka, Aug 12 '19 at 20:50
I done the changes you recommended (u can check them out in the main post) but i'm still getting the same error. — JeyJ, Aug 13 '19 at 05:35
now it works, I updated the code in the comment so that it might help other people. Thank u @ipaleka — JeyJ, Aug 13 '19 at 07:53

score 0 · Accepted Answer · answered Aug 13 '19 at 07:54

0

Not sure what was the original root cause, but by following the help in the comments I was able to solve the problem. I'm leaving the updated code in the post so that others who face the same problem can see the solution

answered Aug 13 '19 at 07:54

JeyJ

3,582
4
35
83

Using SQLAlchemy as an ORM for python - relation doesn't exist

1 Answers1