CODEWITHSUNDEEP
pythondatabasesqlalchemyflask-sqlalchemy
Chuma Umenze
Chuma Umenze

Reputation: 1066

Add SQLAlchemy foreign key ID to another table based on the value

I have two tables:

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

        id = db.Column(INT, primary_key=True)
        name = db.Column(db.String(16), nullable=False)
        uid = db.Column(db.String(16), unique=True)
        department_id = db.Column(INT, db.ForeignKey('department.id'), nullable=False)


    class Department(db.Model):
        __tablename__ = 'department'

        id = db.Column(INT, primary_key=True)
        name = db.Column(db.String(16), nullable=False)
        uid = db.Column(db.String(16), unique=True)

Whenever I am adding a new user, I always have the name of their department.

How can I add the new user simply with the department name without having to query the database to get the department.id?

Upvotes: 0

Views: 2150

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

There's no way around the fact that you'll have to get the department id, if you don't have it. The usual way:

dep = Department.query.filter_by(name=name).one()
usr = User(..., department_id=dep.id)

You could also use a scalar subquery in place of the id, if you really want to avoid fetching separately:

dep_id = db.session.query(Department.id).filter_by(name=name).as_scalar()
usr = User(..., department_id=dep_id)

Upvotes: 2

Related Questions