CODEWITHSUNDEEP
postgresqlflasksqlalchemyflask-sqlalchemy
cansadadeserfeliz
cansadadeserfeliz

Reputation: 3133

Set server_default value for relationship table

I have two tables for administrators and roles, connected vía the third table assignments (many-to-many relationship) with the fields role_id, administrator_id and some extra fields created_at and updated_at, which I would like to populate automatically:

assignments = db.Table('assignments',
    db.Column('role_id', db.Integer, db.ForeignKey('roles.id')),
    db.Column('administrator_id', db.Integer,
              db.ForeignKey('administrators.id')),
    db.Column('created_at', db.DateTime, server_default=db.func.now()),
    db.Column('updated_at', db.DateTime, server_default=db.func.now(),
              onupdate=db.func.now()),
    db.ForeignKeyConstraint(['administrator_id'], ['administrators.id']),
    db.ForeignKeyConstraint(['role_id'], ['roles.id'])
)

class Administrator(db.Model, UserMixin):
    __tablename__ = 'administrators'

    id = Column(Integer, primary_key=True, server_default=text("nextval('administrators_id_seq'::regclass)"))
    email = Column(String(255), nullable=False, unique=True, server_default=text("''::character varying"))
    name = Column(String(255))
    surname = Column(String(255))

    roles = db.relationship('Role', secondary=assignments,
                            backref=db.backref('users', lazy='dynamic'))


class Role(db.Model):
    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True, server_default=text("nextval('roles_id_seq'::regclass)"))
    name = Column(String(255))

But when I assign a role to an administrator

admin.roles = [role1]
db.session.add(admin)
db.session.commit()

it breaks with the following error:

IntegrityError: (psycopg2.IntegrityError) null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (1265, 19, 3, null, null).
[SQL: 'INSERT INTO assignments (role_id, administrator_id) VALUES (%(role_id)s, %(administrator_id)s)'] [parameters: {'administrator_id': 19, 'role_id': 3}]

Is there any way to set a default value for created_at and updated_at fields in assignments table?

Upvotes: 4

Views: 1556

Answers (2)

cansadadeserfeliz
cansadadeserfeliz

Reputation: 3133

It worked using default and onupdate parameters instead of server_default and server_onupdate:

db.Column('created_at', db.DateTime, default=db.func.now()),
db.Column('updated_at', db.DateTime, default=db.func.now(),
          onupdate=db.func.now()),

Upvotes: 3

Danil
Danil

Reputation: 5181

Try this

db.Column('created_at', db.DateTime, server_default=text("now()"))

Upvotes: 1

Related Questions