CODEWITHSUNDEEP
pythonsqlalchemy
ashim
ashim

Reputation: 25560

sqlalchemy, why relationship is not established

I want to create one to one relationship. Why the output is None?

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy import Sequence
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship, backref


engine = create_engine('sqlite:///:memory:')#, echo=True)
Session = sessionmaker(bind=engine)

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))

    def __init__(self, name):
        self.name = name


class Item(Base):
    __tablename__ = 'item'
    id = Column(String, primary_key=True)
    value = Column(Float, default=0)
    user_id = Column(Integer, ForeignKey(User.id))
    user = relationship("User", backref=backref('itemA', uselist=False))

    def __init__(self, id, value, user_id = None):
        self.id = id
        self.value = value
        self.user_id = user_id


session = Session()
Base.metadata.create_all(engine)
ed_user = User('ed')
session.add(ed_user)

session.flush()
it1 = Item('it1', 1, user_id='ed')

session.add(it1)
session.flush()

print  it1.user
print  ed_user.itemA

Upvotes: 0

Views: 127

Answers (1)

rkhayrov
rkhayrov

Reputation: 10260

You give User an (autoincrementing) integer ID (primary key) and refer to it from Item. But then you pass user's name (a string!) to the newly created Item instead of ID. With most database engines this code would just blow up. But SQLite:

  1. Does not enforce foreign key constraints by default.
  2. Has a loose dynamic typing.

So it fails silently.

You should pass an actual ID instead.

it1 = Item('it1', 1, ed_user.id)

Or assign the User object itself to relationship:

it1 = Item('it1', 1)
it1.user = ed_user

The former requires the session to be flushed to get the ID value assigned by the database. The latter "just works": SQLAlchemy resolves the data dependencies automatically.

Upvotes: 3

Related Questions