Create entry in another table when one entry is created

Question

Alright, so I'm sure the title was rather confusing so I'll do my best to explain here. I'm in the process of creating a simple website that allows people to search for movies and TV shows. If they're a user they also have the ability to add Movie/Tv show to their watchlist. As of right now, I have these tables:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    watchList_id = db.Column(db.Integer, db.ForeignKey('watchList.watchList_id'), nullable=False, autoincrement=True)
    first_name = db.Column(db.String(120))
    last_name = db.Column(db.String(120))
    email = db.Column(db.String(180), unique=True)
    password = db.Column(db.String(255))
    is_admin = db.Column(db.Boolean(), default=False)
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime())
    roles = db.relationship('Role', secondary=roles_users, backref=db.backref('users', lazy='dynamic'))


class watchList(db.Model):
    __tablename__ = "watchList"

    watchList_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    title = db.Column(db.String(200), unique=True)
    releaseDate = db.Column(db.Date)
    producer = db.Column(db.String(100))
    description = db.Column(db.String(300))
    genre = db.Column(db.String(50))
    image = db.Column(db.String(300))

I basically want the functionality to where when a user gets created a watchlist for that user also gets created, but right now I keep getting the error that theirs no value for watchList_id when a user gets created which makes sense since I have to idea how to do that. I was thinking of somehow setting the user_id == watchList_id, but I wasn't sure how to do that. Any ideas? I can send more code if needed. I'm using flask-security so I don't know how to see the python/flask code for Register.

score 0 · Answer 1 · answered Nov 19 '17 at 04:30

I think you should use custom method for this purpose. It can be useful for you but i did not run code so u can modify according to you.

class watchList(db.Model):
     __tablename__ = "watchList"

     watchList_id = db.Column(db.Integer, autoincrement=True, 
     primary_key=True)
     title = db.Column(db.String(200), unique=True)
     releaseDate = db.Column(db.Date)
     producer = db.Column(db.String(100))
     description = db.Column(db.String(300))
     genre = db.Column(db.String(50))
     image = db.Column(db.String(300))

     @classmethod
     def create_func(cls, **kw):
          obj = cls(**kw)
          #----creating User object with obj(watchList)

When you done please tell me.Thanks.

So do I call that class method somewhere else? Do I have to do anything to the code I wrote to create the User table? Also, what is cls? — humbleCoder, Nov 19 '17 at 14:30
when you create watchList object then call this method......and cls please visit https://stackoverflow.com/questions/7554738/python-self-no-self-and-cls — GauravInno, Nov 19 '17 at 15:31

score 0 · Answer 2 · answered Nov 20 '17 at 17:47

If you're using the session correctly, this is as simple as adding it to the constructor for user. You have the user constructor make a new watchlist object and append it to the mapped relationship. When the call to create the user is committed, both will be created and both will get their ids at that time. Looking at your code though, it seems to me you have the relationships perhaps backwards. If the watchlist is owned by the user, you would normally have a user_id column on watchlist that is a foreign key to User, and then have a mapped relationship on Users for "watchlists". If you want to ensure there is only one per user, you can use the same pattern but pass the uselist=False arg in to the relationship.

Then your user class can do:

class User(db.Model, UserMixin):
    def __init__(self, **kwargs):
        # set up cols for user
        for k,v in kwargs.items():
            setattr(self, k, v)
        # create watchlist: watchlist and user will both get their ids
        # created at the same time when the session creating User 
        # is committed, and watchlist will have the user_id key too
        self.watchlist = Watchlist()

I would recommend working your way through the excellent SQLAlchemy ORM tutorial if you haven't already done so. A good rule of thumb is that if you have to do anything manually with your id cols, you're using the session wrong. HTH!

Create entry in another table when one entry is created

2 Answers2