SQLAlchemy Automatically Create Entry If Doesn't Exist As Foreign Key

Question

I have a large number of .create() calls that rely on a ForeignKey in another table (Users). However, there is no point in the code where I actually create users.

Is there a way for there to be a Users entry created for each foreign key is specified on another table in SQLAlchemy?

For example:

class Rr(db.Model):
  __tablename__ = 'rr'
  id = db.Column(db.Integer, primary_key=True)
  submitter = db.Column(db.String(50), db.ForeignKey('user.username'))

class User(db.Model):
  __tablename__ = 'user'
  username = db.Column(db.String, primary_key=True)

so If I call Rr(id, submitter=John) is there a way for a John entry to be created in the user table if it does not already exist?

I understand that I can create a wrapper around the .create() method such that it checks the submitter and creates one if it doesn't exist but this seems excess as there are a large number of models that want Users to be automatically created.

score 0 · Answer 1 · edited May 23 '17 at 11:43

I can't think of any orm or sql implementation that does what you ask but there is something that effectively accomplishes what you seek to do described in this SO answer: Does SQLAlchemy have an equivalent of Django's get_or_create?

basically get the User from the db if it exists, if it doesn't create it.

The only down side to this method is that you would need to do 2 queries instead of one but I don't think there is a way to do what you seek in one query

SQLAlchemy Automatically Create Entry If Doesn't Exist As Foreign Key

1 Answers1

Linked