relationship with DeferredReflection raises NoForeignKeysError

[bsdz]

Hi

I'm trying to use DeferredReflection to encapsulate my data model so that it can easily be instantiated for different environments. However, I am having trouble creating relationships with a NoForeignKeysError being raised. I am guessing it is because the table metadata generation is being deferred, there's not enough information to create the foreign key information and therefore the relationships.

I've simplified my code to the following:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection


Base = declarative_base(cls=DeferredReflection)
 
class Country(Base):
    __tablename__ = 'country'
   
class User(Base):
    __tablename__ = 'user'
   
    country_id = sa.Column(sa.Integer, sa.ForeignKey('country.country_id'))
    country = relationship("Country", uselist=False)
       
class MyModel(object):
    def __init__(self, env):
        self._engine = sa.create_engine("...")
        Base.metadata.bind = self._engine
        Base.prepare(self._engine)
   
    def create_session_maker(self):
        return sessionmaker(bind=self._engine)
 
# This code is run in another module.
mymodel = MyModel("DEV")
Session = model.create_session_maker()
session = Session()
l1 = session.query(User).all()

The following error comes back:

NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.country - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Any suggestions on how I might get around this?


Thanks in advance,
Blair

[Mike Bayer]

> NoForeignKeysError:Couldnotdetermine join condition between parent/child

> tables on relationship User.country -there are noforeign keys linking
> these tables.Ensurethat referencing columns are associated witha
> ForeignKeyorForeignKeyConstraint,orspecify a 'primaryjoin'expression.

can't reproduce. Need information on database in use, and if distinct
schemas are involved. See below, and perhaps try to modify this test
case in your environment to reproduce your failure.

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection

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

e.execute("""
create table country (
country_id integer primary key
)
""")

e.execute("""
create table user (
user_id integer primary key,
country_id integer,
foreign key (country_id) references country(country_id)
)
""")

Base = declarative_base(cls=DeferredReflection)


class Country(Base):
__tablename__ = 'country'


class User(Base):
__tablename__ = 'user'

country_id = sa.Column(sa.Integer, sa.ForeignKey('country.country_id'))
country = relationship("Country", uselist=False)


class MyModel(object):
def __init__(self, env):

self._engine = e

Base.metadata.bind = self._engine
Base.prepare(self._engine)

def create_session_maker(self):
return sessionmaker(bind=self._engine)

# This code is run in another module.
mymodel = MyModel("DEV")

Session = mymodel.create_session_maker()

session = Session()
l1 = session.query(User).all()

print(User.country.property.primaryjoin)

> |
>
> Any suggestions on how I might get around this?
>
>
> Thanks in advance,
> Blair
>
>

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[bsdz]

I did some further checking and realized deeper down the real issue lies column name case and with column_reflect where I map my column names to lower case. This seems to confuse the ForeignKey function.

Here's a complete example with sqlite. Perhaps there's a simple workaround?

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection

from sqlalchemy.event import listens_for

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

e.execute("""
     create table country (
       COUNTRY_ID integer primary key
     )
""")

e.execute("""
     create table main.user (
         user_id integer primary key,
         COUNTRY_ID integer,

         foreign key (country_id) references country(country_id)
     )
"""

)

Base = declarative_base(cls=DeferredReflection)

class Country(Base):
     __tablename__ = 'country'

class User(Base):
     __tablename__ = 'user'

     __table_args__ = {'schema': 'main'}

     country_id = sa.Column("COUNTRY_ID", sa.Integer, sa.ForeignKey('country.COUNTRY_ID'))
     country = relationship("Country", uselist=False)

class MyModel(object):
     def __init__(self, env):
         self._engine = e
         Base.metadata.bind = self._engine
         Base.prepare(self._engine)


     def create_session_maker(self):
         return sessionmaker(bind=self._engine)
       

@listens_for(sa.Table, "column_reflect")
def column_reflect(inspector, table, column_info):
     """
     Map upper case column names to lower case.
     """
     column_info['key'] = column_info['name'].lower()            

# This code is run in another module.
mymodel = MyModel("DEV")
Session = mymodel.create_session_maker()
session = Session()
l1 = session.query(User).all()

print(User.country.property.primaryjoin)

The code above produces the following error:

NoReferencedColumnError: Could not initialize target column for ForeignKey 'country.COUNTRY_ID' on table 'user': table 'country' has no column named 'COUNTRY_ID'

Thanks again :)

 

[Mike Bayer]

name the COUNTRY_ID column in lower case in your mapping, all UPPERCASE
means case sensitive and it will be quoted "COUNTRY_ID", and not match
the case-insensitive country_id name in your actual schema.

On 07/29/2016 03:29 PM, bsdz wrote:
> I did some further checking and realized deeper down the real issue lies
> column name case and with column_reflect where I map my column names to
> lower case. This seems to confuse the ForeignKey function.
>
> Here's a complete example with sqlite. Perhaps there's a simple workaround?
>
> |

> importsqlalchemy assa
> fromsqlalchemy.orm importsessionmaker,relationship
> fromsqlalchemy.ext.declarative importdeclarative_base,DeferredReflection

> fromsqlalchemy.eventimportlistens_for
>
> e =sa.create_engine("sqlite://",echo=True)

>
> e.execute("""
> create table country (
> COUNTRY_ID integer primary key
> )
> """)
>
> e.execute("""
> create table main.user (
> user_id integer primary key,
> COUNTRY_ID integer,
> foreign key (country_id) references country(country_id)
> )
> """)
>

> Base=declarative_base(cls=DeferredReflection)
>
> classCountry(Base):
> __tablename__ ='country'
>
> classUser(Base):

> __tablename__ ='user'
> __table_args__ ={'schema':'main'}
>
> country_id

> =sa.Column("COUNTRY_ID",sa.Integer,sa.ForeignKey('country.COUNTRY_ID'))

> country =relationship("Country",uselist=False)
>
> classMyModel(object):
> def__init__(self,env):

> self._engine =e

> Base.metadata.bind =self._engine
> Base.prepare(self._engine)
>
>
> defcreate_session_maker(self):
> returnsessionmaker(bind=self._engine)
>

> @listens_for(sa.Table,"column_reflect")
> defcolumn_reflect(inspector,table,column_info):

> """
> Map upper case column names to lower case.
> """

> column_info['key']=column_info['name'].lower()

>
> # This code is run in another module.
> mymodel =MyModel("DEV")
> Session=mymodel.create_session_maker()
> session =Session()
> l1 =session.query(User).all()
>
> print(User.country.property.primaryjoin)
> |
>
>
> The code above produces the following error:
>
> |
>
> NoReferencedColumnError:Couldnotinitialize target column

> forForeignKey'country.COUNTRY_ID'on table 'user':table 'country'has
> nocolumn named 'COUNTRY_ID'
>
>
> |
>
> Thanks again :)
>
>

[bsdz]

Yes, that worked - thanks! :)