Could not determine join condition between parent/child tables on relationship UserDevice.keys - there are no foreign keys linking these tables.

[humoyun]

I am getting the below exception as I run example code provided in the documentation of sqlservice:

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper|UserDevice|user_devices'. Original exception was: Could not determine join condition between parent/child tables on relationship UserDevice.keys - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

`from sqlalchemy import Column, types, orm, ForeignKey

class User(MyModel):
__tablename__='users'

id = Column(types.Integer(), primary_key=True)
name = Column(types.String(55))
email = Column(types.String(55))

about   = orm.relation('UserAbout', uselist=False)
devices = orm.relation('UserDevice')

 class UserAbout(MyModel):
__tablename__='user_about'

id = Column(types.Integer(), ForeignKey('users.id'), primary_key=True)
nickname = Column(types.String(55))
city = Column(types.String(55))

class UserDevice(MyModel):
__tablename__='user_devices'

id = Column(types.Integer(), primary_key=True)
user_id = Column(types.Integer(), ForeignKey('users.id'), nullable=False)
device_name = Column(types.String(55))

keys = orm.relation('UserDeviceKeys')

 class UserDeviceKeys(MyModel):
__tablename__='user_device_keys'

id = Column(types.Integer(), primary_key=True)
device_id = Column(types.Integer(), ForeignKey('user_devices.id'), nullable=False)
key = Column(types.String(55))

from sqlservice import SQLClient

 config = {'SQL_DATABASE_URL':'sqlite:///', 'SQL_ECHO':True}
 db = SQLClient(config, model_class=MyModel)
 db.create_all()`

Need help!

[dgilland]

Based on what you posted, I see that there is a foreign key defined for UserDevice and UserDeviceKeys on UserDeviceKeys.device_id using table/column user_devices.id.

I'm also not getting any errors when I put the following into a file and execute it:

from sqlalchemy import Column, types, orm, ForeignKey
from sqlservice import ModelBase, declarative_base

@declarative_base
class MyModel(ModelBase):
    pass

class User(MyModel):
    __tablename__ = 'users'

    id = Column(types.Integer(), primary_key=True)
    name = Column(types.String(55))
    email = Column(types.String(55))

    about = orm.relation('UserAbout', uselist=False)
    devices = orm.relation('UserDevice')

class UserAbout(MyModel):
    __tablename__ = 'user_about'

    id = Column(types.Integer(), ForeignKey('users.id'), primary_key=True)
    nickname = Column(types.String(55))
    city = Column(types.String(55))

class UserDevice(MyModel):
    __tablename__ = 'user_devices'

    id = Column(types.Integer(), primary_key=True)
    user_id = Column(types.Integer(), ForeignKey('users.id'), nullable=False)
    device_name = Column(types.String(55))

    keys = orm.relation('UserDeviceKeys')

class UserDeviceKeys(MyModel):
    __tablename__ = 'user_device_keys'

    id = Column(types.Integer(), primary_key=True)
    device_id = Column(types.Integer(), ForeignKey('user_devices.id'), nullable=False)
    key = Column(types.String(55))

from sqlservice import SQLClient

config = {'SQL_DATABASE_URL':'sqlite:///', 'SQL_ECHO':True}
db = SQLClient(config, model_class=MyModel)
db.create_all()

user = User(name='a',
            email='b',
            about=UserAbout(nickname='c', city='d'),
            devices=[UserDevice(device_name='e',
                                keys=[UserDeviceKeys(key='f')])])

db.save(user)
users = (db.User
         .join(UserAbout)
         .join(UserDevice)
         .join(UserDeviceKeys)
         .options(orm.contains_eager('about'),
                  orm.contains_eager('devices', 'keys'))
         .all())

print(users[0].to_dict())

which outputs:

2017-02-17 09:16:42,896 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-02-17 09:16:42,897 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,897 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-02-17 09:16:42,897 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,898 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2017-02-17 09:16:42,898 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,899 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("user_about")
2017-02-17 09:16:42,899 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,899 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("user_devices")
2017-02-17 09:16:42,899 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,899 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("user_device_keys")
2017-02-17 09:16:42,900 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,900 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
    id INTEGER NOT NULL, 
    name VARCHAR(55), 
    email VARCHAR(55), 
    PRIMARY KEY (id)
)

2017-02-17 09:16:42,901 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,901 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-17 09:16:42,901 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user_about (
    id INTEGER NOT NULL, 
    nickname VARCHAR(55), 
    city VARCHAR(55), 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES users (id)
)

2017-02-17 09:16:42,901 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,902 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-17 09:16:42,902 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user_devices (
    id INTEGER NOT NULL, 
    user_id INTEGER NOT NULL, 
    device_name VARCHAR(55), 
    PRIMARY KEY (id), 
    FOREIGN KEY(user_id) REFERENCES users (id)
)

2017-02-17 09:16:42,902 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,902 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-17 09:16:42,903 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user_device_keys (
    id INTEGER NOT NULL, 
    device_id INTEGER NOT NULL, 
    "key" VARCHAR(55), 
    PRIMARY KEY (id), 
    FOREIGN KEY(device_id) REFERENCES user_devices (id)
)

2017-02-17 09:16:42,903 INFO sqlalchemy.engine.base.Engine ()
2017-02-17 09:16:42,903 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-17 09:16:42,911 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-02-17 09:16:42,912 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, email) VALUES (?, ?)
2017-02-17 09:16:42,912 INFO sqlalchemy.engine.base.Engine ('a', 'b')
2017-02-17 09:16:42,913 INFO sqlalchemy.engine.base.Engine INSERT INTO user_devices (user_id, device_name) VALUES (?, ?)
2017-02-17 09:16:42,913 INFO sqlalchemy.engine.base.Engine (1, 'e')
2017-02-17 09:16:42,914 INFO sqlalchemy.engine.base.Engine INSERT INTO user_about (id, nickname, city) VALUES (?, ?, ?)
2017-02-17 09:16:42,914 INFO sqlalchemy.engine.base.Engine (1, 'c', 'd')
2017-02-17 09:16:42,915 INFO sqlalchemy.engine.base.Engine INSERT INTO user_device_keys (device_id, "key") VALUES (?, ?)
2017-02-17 09:16:42,915 INFO sqlalchemy.engine.base.Engine (1, 'f')
2017-02-17 09:16:42,916 INFO sqlalchemy.engine.base.Engine COMMIT
2017-02-17 09:16:42,918 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-02-17 09:16:42,919 INFO sqlalchemy.engine.base.Engine SELECT user_about.id AS user_about_id, user_about.nickname AS user_about_nickname, user_about.city AS user_about_city, user_device_keys.id AS user_device_keys_id, user_device_keys.device_id AS user_device_keys_device_id, user_device_keys."key" AS user_device_keys_key, user_devices.id AS user_devices_id, user_devices.user_id AS user_devices_user_id, user_devices.device_name AS user_devices_device_name, users.id AS users_id, users.name AS users_name, users.email AS users_email 
FROM users JOIN user_about ON users.id = user_about.id JOIN user_devices ON users.id = user_devices.user_id JOIN user_device_keys ON user_devices.id = user_device_keys.device_id
2017-02-17 09:16:42,920 INFO sqlalchemy.engine.base.Engine ()
{'devices': [{'user_id': 1, 'device_name': 'e', 'id': 1, 'keys': [{'key': 'f', 'device_id': 1, 'id': 1}]}], 'id': 1, 'name': 'a', 'about': {'city': 'd', 'nickname': 'c', 'id': 1}, 'email': 'b'}

Can you copy/paste the Python code above and see whether that works for you or not? If it doesn't work, can you provide what version of Python, SQLAlchemy, and sqlservice you're using?

[humoyun]

I copied and ran the code you provide, but did not work, getting the same error. Python v 2.7.6 SQLAlchemy v 1.1.5 SQLService v 0.9.1

[humoyun]

Surprisingly it is working now, I just restarted the jupyter notebook. however, I cannot import ModelBase neither in the python shell nor in the case when I ran it as a script, but it is working in the jupyter notebook?

Python 2.7.6 (default, Jun 22 2015, 17:58:13) 
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlservice import ModelBase, declarative_base
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: cannot import name ModelBase
>>> 

[dgilland]

Glad it's working! If you have any other problems, feel free to open another issue.