Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a problem with SqlAlchemy. When I define the schema in my Oracle database, foreign keys are not recognized in other tables. Tested without using layout and it worked. But I need to define the schema of this application because the user is not the owner of the tables. I would like your help to solve this problem. code:

Base = declarative_base()
SCHEMA = {'schema' : 'SIATDESV'}

class Pgdasd(Base):
    __tablename__ = 'PGDASD'
    __table_args__ = SCHEMA

    PGDASD_00000_ID_DECLARACAO    = Column(String(17), primary_key = True)

class Pgdasd_01000(Base):
    __tablename__ = 'pgdasd_01000'
    __table_args__ = SCHEMA

    PGDASD_00000_ID_DECLARACAO  = Column(String, ForeignKey('PGDASD.PGDASD_00000_ID_DECLARACAO'))   
    PGDASD_01000_NRPAGTO        = Column(String, primary_key = True)

error: *Foreign key associated with column 'pgdasd_01000.PGDASD_00000_ID_DECLARACAO' could not find table 'PGDASD' with which to generate a foreign key to target column 'PGDASD_00000_ID_DECLARACAO'*

thanks!

share|improve this question
 
Could you please add the following information from the logs - create table queries generated by SQLAlchemy? –  vvladymyrov May 31 at 22:53
add comment

1 Answer

up vote 0 down vote accepted

Step 1: check that table(s) are created in db. BTW How did you create these tables? Have you run metadata.create_all() method ?

Step 2: Try to add the name of the schema to the ForeignKey defintion:

Base = declarative_base()
SCHEMA = {'schema' : 'SIATDESV'}

class Pgdasd(Base):
    __tablename__ = 'PGDASD'
    __table_args__ = SCHEMA

    PGDASD_00000_ID_DECLARACAO    = Column(String(17), primary_key = True)

class Pgdasd_01000(Base):
    __tablename__ = 'pgdasd_01000'
    __table_args__ = SCHEMA

    PGDASD_00000_ID_DECLARACAO  = Column(String, ForeignKey('SIATDESV.PGDASD.PGDASD_00000_ID_DECLARACAO'))   
    PGDASD_01000_NRPAGTO        = Column(String, primary_key = True)

If this will help you may also make code to use schema value to avoid hardcoding the schema.

    PGDASD_00000_ID_DECLARACAO  = Column(String, ForeignKey(SCHEMA['schema']+'.PGDASD.PGDASD_00000_ID_DECLARACAO'))

PS: Anyway it is always useful to check SQLAlchemy log what SQL queries it generates.

share|improve this answer
 
The problem continues ... When I inserted the foreign key in the scheme generated the same error. –  fredsilva Jun 3 at 18:49
 
Please make sure that there is table PGDASD in schema SIATDESV and it has such column. –  vvladymyrov Jun 3 at 21:24
 
Run the following query in sqlplus: select PGDASD_00000_ID_DECLARACAO from SIATDESV.PGDASD; to check does such table exist in db. DB would complain if there are no such table or column. –  vvladymyrov Jun 3 at 21:38
 
I tested it now and it worked! I think I had done something wrong. Thank you! –  fredsilva Jun 4 at 16:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.