CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
rezkam
rezkam

Reputation: 1357

Model in Sqlalchemy

In each workspace i need to add many users, I need connection to for example 5 users how can i handle this sqlalchemy ?

My model is like this :

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(50), unique=True, nullable=False)
    username = db.Column(db.Unicode(20), unique=True, nullable=False, index=True)

    ws = db.relationship('Workspace', backref = 'user', lazy = 'dynamic')


class Workspace(db.Model):
    __tablename__ = 'workspaces'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Unicode(50))
    created_on = db.Column(db.DateTime, default=datetime.utcnow)



    users =

Upvotes: 0

Views: 909

Answers (1)

davidism
davidism

Reputation: 127190

You want a many-to-many relationship so that each workspace can have multiple users, and each user can have multiple workspaces.

A secondary table is used to track which users are in which workspaces.

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

engine = create_engine('sqlite:///:memory:', echo=True)
session = sessionmaker(bind=engine)()
Base = declarative_base(bind=engine)


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)


class Workspace(Base):
    __tablename__ = 'workspace'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    users = relationship(User, lambda: user_workspace, backref='workspaces')


user_workspace = Table(
    'user_workspace', Base.metadata,
    Column('user_id', Integer, ForeignKey(User.id), primary_key=True),
    Column('workspace_id', Integer, ForeignKey(Workspace.id), primary_key=True)
)


Base.metadata.create_all()

w1 = Workspace(name='Stack Overflow')
w2 = Workspace(name='Meta Stack Overflow')

u1 = User(name='davidism', workspaces=[w1, w2])
u2 = User(name='itmard', workspaces=[w1])
u3 = User(name='john doe')

w3 = Workspace(name='Hyphen Site', users=[u3])

session.add_all([w1, w2, w3])
session.commit()

print [u.name for u in w1.users]  # outputs ['davidism', 'itmard']

Upvotes: 2

Related Questions