CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Aleksei Kozharin
Aleksei Kozharin

Reputation: 21

Create a child object based on a parent one

I'm developing a DB where you at first register as a regular User and subsequently become a Driver. I'd like to create a Driver object in SQL-Alchemy based on existing User object. How can I achieve that?

Suppose that the User table already has a user with ID=3. Now we would like to add a Driver based on that user.

Here are classes that I used:

# Base class
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(MAX_NAME_LENGTH), nullable=False)
    last_name = db.Column(db.String(MAX_SURNAME_LENGTH), nullable=False)
    email = db.Column(db.String(MAX_EMAIL_LENGTH), nullable=False, unique=True)


# Child class
class Driver(User):
    id = db.Column(db.Integer, db.ForeignKey(User.__tablename__ + '.id'), primary_key=True)
    photo_url= db.Column(db.String(MAX_URL_LENGTH), nullable=False)
    # ...

# we can easily add a new User
user = User(first_name='Martin', last_name='Smith', email='[email protected]')
db.session.add(user)
db.session.commit()
# But this code will create a new user and assign it to that driver instance!
# driver = Driver(# Will require fields of both User and Driver class #)

I expect to find some method for taking parent object and extending it with child's properties. Something like:

user_from_db = db.session.query(User).filter_by(User.id == 3).first()
driver = Driver(photo_url='/your_url/', based_on=user_from_db)
# And now driver has all properties of 'user_from_db'
# Furthermore, SQL-Alchemy would create a row only in `Driver` table

How can I accomplish that using SQL-Alchemy inheritance?

Upvotes: 1

Views: 1861

Answers (1)

Tiki
Tiki

Reputation: 780

First I recommend making the following changes:
The autoincrement field is to leave the task of assigning an id to the database.
For all relationship (OneToMany, ManyToMany or OneToOne) you need to specify a db.relationship in the parent class. Read this.
And __tablename__ is optional, but sometimes flask throw an error if this is not assigned.

# Base class
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    first_name = db.Column(db.String(MAX_NAME_LENGTH), nullable=False)
    last_name = db.Column(db.String(MAX_SURNAME_LENGTH), nullable=False)
    email = db.Column(db.String(MAX_EMAIL_LENGTH), nullable=False, unique=True)
    driver = db.relationship('Driver', backref='driver', lazy=True)


# Child class
class Driver(db.Model):
    __tablename__ = 'driver'
    id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    photo_url= db.Column(db.String(MAX_URL_LENGTH), nullable=False)
    # ...

user = User(first_name='Martin', last_name='Smith', email='[email protected]')
db.session.add(user) # first we create a user and add him to the current session
db.session.flush() # here we says that db.session.add(user) is a pending transaction. More info [here][1]
driver = Driver(id=user.id, photo_url='/your_url/')
# and we pass as driver id the id of user previously created
db.session.add(driver) # add driver to current session
db.session.commit() # commits those changes to the database.

Upvotes: 1

Related Questions