Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I am very new to SQLAlchemy. I am having some difficulty setting up a one to many relationship between two models in my application. I have two models User `Photo'. A user has only one role associated with it and a role has many users associated with it.
This is the code that I have in my data_generator.py file:
# coding=utf-8
from sqlalchemy import Column, Integer, String, BigInteger,Date, Enum, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
import time
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
import datetime
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer(), primary_key=True)
username = Column(String(30), unique=True, nullable=False)
password = Column(String, default='123456', nullable=False)
name = Column(String(30), nullable=False)
grade = Column(String(30))
emp_no = Column(BigInteger, unique=True, nullable=False)
roles = relationship('Role', back_populates='users')
class Scene(Base):
__tablename__ = 'scenes'
id = Column(Integer, primary_key=True)
scene_name = Column(String(30), nullable=False)
life_time = Column(Date, nullable=False,
default=datetime.datetime.strptime(
time.strftime("%Y-%m-%d", time.localtime(time.time() + (12 * 30 * 24 * 3600))),'%Y-%m-%d').date())
scene_description = Column(String(150), default="")
class Gateway(Base):
__tablename__ = 'gateways'
id = Column(Integer, primary_key=True)
gateway_name = Column(String(30), nullable=False)
gateway_api_key = Column(String(100), nullable=False, unique=True)
gateway_type = Column(Enum('up', 'down', 'soft', name="gateway_type"), nullable=False)
class Role(Base):
__tablename__ = 'roles'
id = Column(Integer, primary_key=True)
role_name = Column(String(30), unique=True, nullable=False)
users = relationship('User', back_populates='roles')
def __repr__(self):
return self.role_name
engine = create_engine('sqlite:///memory:')
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
Base.metadata.create_all(engine)
ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=1)
example_scene = Scene(scene_name='example_1', scene_description='example_description')
example_gateway = Gateway(gateway_name='example_1',gateway_api_key='11111',gateway_type='up')
# session.add(example_gateway)
# session.commit()
def init_user(flag, number):
while number >= 1:
if flag == 1:
ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1')
if flag == 2:
# TODO admin
if flag == 3:
# TODO teacher
number -= 1
def init_scene(number):
while number >= 1:
number -= 1
# TODO scene
def init_gateway(api_key, number):
# TODO gateway
if __name__ == '__main__':
with session.no_autoflush:
c = session.query(Gateway).all()
print c[0].id
The error that I keep encountering is shown below:
/usr/bin/python2.7 /home/pajamas/PycharmProjects/untitled5/data_generator.py
Traceback (most recent call last):
File "/home/pajamas/PycharmProjects/untitled5/data_generator.py", line 73, in <module>
ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=1)
File "<string>", line 2, in __init__
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 347, in _new_state_if_none
state = self._state_constructor(instance, self)
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 764, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 177, in _state_constructor
self.dispatch.first_init(self, self.class_)
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
fn(*args, **kw)
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 3088, in _event_on_first_init
configure_mappers()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2984, in configure_mappers
mapper._post_configure_properties()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1810, in _post_configure_properties
prop.init()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
self.do_init()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1658, in do_init
self._setup_join_conditions()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1733, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1991, in __init__
self._determine_joins()
File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2096, in _determine_joins
"specify a 'primaryjoin' expression." % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.roles - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
Process finished with exit code 1
Can someone assist me with this? Help would be greatly appreciated.
One to One(One user has only one Role)
Many to One(One user has many roles)
Many to Many(Many user has many roles)
For One to One:
class Role(Base):
id = Column(Integer, primary_key=True)
# ...
user_id = Column(Integer, ForeignKey("user.id"))
class User(Base):
id = Column(Integer, primary_key=True)
# ...
role = relationship("Role", back_populates="user", uselist=False)
For Many to One:
class Role(Base):
id = Column(Integer, primary_key=True)
# ...
user_id = Column(Integer, ForeignKey("user.id"))
class User(Base):
id = Column(Integer, primary_key=True)
# ...
roles = relationship("Role", back_populates="user")
For Many to Many:(In this relation, we need a associate table)
roles_users = Table("roles_users",
Column("role_id", Integer, ForeignKey("role.id")),
Column("user_id", Integer, ForeignKey("user.id")))
class Role(Base):
id = Column(Integer, primary_key=True)
# ...
class User(Base):
id = Column(Integer, primary_key=True)
# ...
roles = relationship("Role", back_populates="users", secondary=roles_users)
–
–
–
I made a low-level mistake because of my lack of database and SQL alchemy.
First of all, this is a typical "one to many" problem.Relationship connects two rows from two tables by users' foreign key. The role_id is defined as the foreign key, which builds the connections.
The parameter "roles.id" in "ForeignKey()" clarified this column is the id of Role's rows.
relationship() 's backref specialized the role model.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
