One of my declarative table definition not available in metadata even i attached the table with metadata

[Abhishek Sharma]

I am using SQLALCHEMY Version 1.2.17

I am using declarative approach to define models, One of my declarative table definition not available in metadata even i attached the table with metadata so when other table which is in same metadata is trying to create relationship with that table it throws the below error:

NoReferencedTableError(u"Foreign key associated with column 'deal_term_rate_options.deal_id' could not find table 'deal.deal' with which to generate a foreign key to target column 'deal_id'",)

Attaching the required files for reference.

deal_db is db connection

>>> from fni_django_sorcery.db import databases

>>> deal_db = databases.get("Deal")

>>> deal_db

<SQLAlchemy engine=oracle+cx_oracle://dtadm:***@DT20DB_ORA?allow_twophase=False&coerce_to_decimal=False&optimize_limits=True&use_binds_for_limits=False>

>>> deal_db.metadata

MetaData(bind=None)

>>> deal_db.metadata.tables

immutabledict({})

I don't see any error on console also.

Any help will be appreciated.

[Mike Bayer]

a MetaData collection is empty until you put some Table objects into it.   This is usually done by declaring them explicitly either through the Core API (https://docs.sqlalchemy.org/en/13/core/metadata.html) or through the ORM (https://docs.sqlalchemy.org/en/13/orm/mapping_styles.html#declarative-mapping ) .   Alternatively you can read Table objects from the database automatically using reflection (https://docs.sqlalchemy.org/en/13/core/reflection.html ).

The code above doesn't show what you are doing to produce the error, however the error means you've used a ForeignKey() object with the argument "deal.deal.deal_id" and there is no Table object in the metadata with that schema / table combination.    "deal.deal.deal_id" means the schema is explicitly set to "deal" and the table name is "deal".   It is unlikely that you want to be setting the "deal" schema name explicitly unless "deal" is a different schema from the one you are working within and you have Table objects that set "schema="deal"" explicitly.

Any help will be appreciated.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f6cd9899-2f0a-47fc-860f-1740fc4e2ddd%40googlegroups.com.

Attachments:

• basemodel.py
  
• deal.py
  
• declarative.py
  
• metadata.py
  
• termrateoptions.py
  

[Mike Bayer]

oh, also I see you attached six separate scripts.    Someone here might want to help you with that but usually people here don't have the resources to debug your whole application, you normally need to take the steps to isolate your problem down to a small, single file example that produces the message quickly.

For example, I can reproduce your error just like this:

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import ForeignKey

from sqlalchemy import Integer

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class DealTermRateOptions(Base):

    __tablename__ = 'deal_term_rate_options'

    id = Column(Integer, primary_key=True)

    # this should be "deal.deal_id", not "deal.deal.deal_id"

    deal_id = Column(ForeignKey("deal.deal.deal_id"))

class Deal(Base):

    __tablename__ = 'deal'

    deal_id = Column(Integer, primary_key=True)

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)  # <-- will cause the error you are seeing

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/15afc854-5d2c-4949-8cfd-f9aa3f59d7ce%40www.fastmail.com.

[Abhishek Sharma]

Thanks for information, my both the tables deal_term_rate_options and deal are in different schema, deal_term_rate_options is in menu schema and deal table in deal schema.

Do I still not required to set foreign key constraint like deal.deal.deal_id on deal_term_rate_options table?

[Abhishek Sharma]

I am able to figure out the issue but still I am not sure what caused that, I moved the relationship from deal model to deal_term_rate_options model with backref option and now I can see my table deal.deal in metadata, I am guessing may be some circular dependency was there which may be causing this.

[Mike Bayer]

OK, that is another problem to avoid, if you have two ORM models in different .py files, you need to make sure both .py files have been imported before you can use the mappings or do things like create_all().    from that description it sounds like your "deal" model wasn't imported.  

there's not really any way for SQLAlchemy to avoid that issue, perhaps for this error message a link to a documentation section that lists out all the possibilities can be added.

On Sun, Aug 4, 2019, at 8:58 AM, Abhishek Sharma wrote:

I am able to figure out the issue but still I am not sure what caused that, I moved the relationship from deal model to deal_term_rate_options model with backref option and now I can see my table deal.deal in metadata, I am guessing may be some circular dependency was there which may be causing this.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c34618d8-2a57-459c-9fe9-f8d949d8c739%40googlegroups.com.

[Abhishek Sharma]

Thanks so much for your help, better solution for this might be put all models imports in root model directory __init__ file.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/ICuMzbX_OOw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ed0280ea-30ef-4c14-bed4-1ff80ca37711%40www.fastmail.com.