Re: many to many relation with foreign key to composite primary key
On Wed, Dec 12, 2018 at 1:01 PM patrick payet wrote:
>
> I had a SQLAlchemy model like -
>
> class User(DeclarativeBase):
> __tablename__ = 'users'
>
> id = Column(BigInteger, primary_key=True)
> email = Column(String(100), unique=True, nullable=False)
> name = Column(String(100), nullable=False)
> hashed_password = Column(String(100), nullable=False)
> is_admin = Column(BOOLEAN, default=False)
> is_active = Column(BOOLEAN, default=True)
> created = Column(DateTime, default=datetime.now)
> modified = Column(DateTime, default=datetime.now,
> onpudate=datetime.datetime.now)
> roles = relationship('Role', secondary=users_roles,
> back_populates='users', cascade="all, delete-orphan")
> permissions = relationship('Permission', secondary=users_permissions,
> back_populates='users',
>cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" %
> (self.name, self.email, self.hashed_password)
>
>
> class Role(DeclarativeBase):
> __tablename__ = 'roles'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> users = relationship('User', secondary=users_roles,
> back_populates='roles',cascade="all, delete-orphan")
> permissions = relationship('Permission', secondary=roles_permissions,
> back_populates='roles',
>cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" % self.name
>
>
> class Permission(DeclarativeBase):
> __tablename__ = 'permissions'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> description = Column(String(255))
> users = relationship('User', secondary=users_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
> roles = relationship('Role', secondary=roles_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" % (self.name,
> self.description)
>
> However, Alembic is not generating the correct upgrade, i try to make it
>
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> # op.drop_table('users')
> # ### end Alembic commands ###
>
> op.create_table('roles',
> sa.Column('id', sa.BIGINT(), autoincrement=True,
> nullable=False),
> sa.Column('name', sa.String(100), autoincrement=False,
> nullable=False),
> sa.Column('users', BigInteger),
> sa.Column('permissions', BigInteger),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name', name='roles_name_key')
> # sa.ForeignKeyConstraint(['users'],
> ['users_roles.users_id'], ondelete='CASCADE'),
> # sa.ForeignKeyConstraint(['permissions'],
> ['roles_permissions.permissions_id'], ondelete='CASCADE')
> )
> op.create_table('permissions',
> sa.Column('id', sa.BIGINT(), autoincrement=True,
> nullable=False),
> sa.Column('name', sa.String(100), autoincrement=False,
> nullable=False),
> sa.Column('description', sa.String(255)),
> sa.Column('users', BigInteger),
> sa.Column('roles', BigInteger),
> sa.PrimaryKeyConstraint('id')
> # sa.ForeignKeyConstraint(['users'],
> ['users_permissions.users_id'], ondelete='CASCADE'),
> # sa.ForeignKeyConstraint(['roles'],
> ['role_permissions.roles_id'], ondelete='CASCADE')
> )
> op.create_table('users_roles',
> sa.Column('users_id', BigInteger,
> sa.ForeignKey('users.id'), primary_key=True),
> sa.Column('roles_id', BigInteger,
> sa.ForeignKey('roles.id'), primary_key=True)
> )
> op.create_table('users_permissions',
> sa.Column('users_id', BigInteger,
> sa.ForeignKey('users.id'), primary_key=True),
> sa.Column('permissions_id', BigInteger,
> sa.ForeignKey('permissions.id'), primary_key=True)
> )
> op.create_table('roles_permissions',
> sa.Column('roles_id', BigInteger,
> sa.ForeignKey('roles.id'), primary_key=True),
> sa.Column('permissions_id', BigInteger,
> sa.ForeignKey('permissions.id'), primary_key=True)
> )
> op.drop_constraint('users_name_key', 'users')
> op.add_column('users', sa.Column('is_admin', sa.BOOLEAN,
> autoincrement=False, nullable=False, default=False))
> op.add_column('users', sa.Column('is_active', sa.BOOLEAN,
> autoincrement=False, nullable=False, default=True))
> op.add_column('users', sa.Column('created', sa.DateTime,
> autoi
Re: What would be the proper way to implement a post migration hook ?
On Wed, Dec 12, 2018 at 10:40 AM wrote:
>
> Dear Alembic User Community,
>
> I am looking for suggestion for the best way to address the following problem:
>
> We have a use case where we'd like to make sure some SQL queries are always
> executed after any set migration.
> Our particular use case is with PostgreSQL. We have several users who can
> create new tables and types in a database.
> But we want to make sure the ownership of objects is a group all those users
> belong to. But we'd rather make that
> transparent so it does not get forgotten in some migrations.
>
> So far we managed to do this by overriding the do_run_migrations() function
> in the env.py (cf. code below).
> That has some good and bad qualities:
>
> - The "hook" runs once whether we pass 1 or more migrations;
> - The migrations are applied, even if this last step fails;
> - It does not work in "online" mode unless we force a commit (offline mode w.
> `--sql` does emit a commit afterwards);
>
> So I wondered if someone would have encountered similar use cases and if
> there would be other / better ways to achieve this ?
> In particular, a better way to know what is the command being invoked, cause
> you don't want to apply this "hook" for commands other
> than upgrade and downgrade.
>
> Here is the code snippet:
>
> def do_run_migrations(context):
> with context.begin_transaction():
> context.run_migrations()
> command_name = context.config.cmd_opts.cmd[0].__name__
> if command_name in ('downgrade', 'upgrade', ):
> context.execute('REASSIGN OWNED BY "{user}" TO "schema-admin"'
> .format(user=settings.ALEMBIC_DB.username))
> context.execute('COMMIT') # Does not work "online", unless we do
> this
There's support for running a hook after each migration:
https://alembic.sqlalchemy.org/en/latest/api/runtime.html?highlight=on_version_apply#alembic.runtime.environment.EnvironmentContext.configure.params.on_version_apply
although to use that hook and figure out how to just run for the
"last" step would require comparing the step given to the last step
requested.
if you are looking to run this only once at the end, what you're doing
should not require that "COMMIT" unless you are running
transaction_per_migration=True. Tried it here and with
transactional_ddl=True the COMMIT is emitted just once at the end of
the block, so I cannot reproduce your "does not work in 'online'" case
without transaction_per_migration=True.
More generally, if what you're doing is a step that should be before
*any* commit, which is what it seems like, use a commit event:
https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connectionevent#sqlalchemy.events.ConnectionEvents.commit
@event.listens_for(connection, "commit")
def on_commit(conn):
conn.execute("ddl...")
>
>
> Regards,
> Nicolas.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
many to many relation with foreign key to composite primary key
I had a SQLAlchemy model like -
class User(DeclarativeBase):
__tablename__ = 'users'
id = Column(BigInteger, primary_key=True)
email = Column(String(100), unique=True, nullable=False)
name = Column(String(100), nullable=False)
hashed_password = Column(String(100), nullable=False)
is_admin = Column(BOOLEAN, default=False)
is_active = Column(BOOLEAN, default=True)
created = Column(DateTime, default=datetime.now)
modified = Column(DateTime, default=datetime.now,
onpudate=datetime.datetime.now)
roles = relationship('Role', secondary=users_roles,
back_populates='users', cascade="all, delete-orphan")
permissions = relationship('Permission', secondary=users_permissions,
back_populates='users',
cascade="all, delete-orphan")
def __repr__(self):
return "" %
(self.name, self.email, self.hashed_password)
class Role(DeclarativeBase):
__tablename__ = 'roles'
id = Column(BigInteger, primary_key=True)
name = Column(String(100), unique=True, nullable=False)
users = relationship('User', secondary=users_roles,
back_populates='roles',cascade="all, delete-orphan")
permissions = relationship('Permission', secondary=roles_permissions,
back_populates='roles',
cascade="all, delete-orphan")
def __repr__(self):
return "" % self.name
class Permission(DeclarativeBase):
__tablename__ = 'permissions'
id = Column(BigInteger, primary_key=True)
name = Column(String(100), unique=True, nullable=False)
description = Column(String(255))
users = relationship('User', secondary=users_permissions,
back_populates='permissions',
cascade="all, delete-orphan")
roles = relationship('Role', secondary=roles_permissions,
back_populates='permissions',
cascade="all, delete-orphan")
def __repr__(self):
return "" % (self.name,
self.description)
However, Alembic is not generating the correct upgrade, i try to make it
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
# op.drop_table('users')
# ### end Alembic commands ###
op.create_table('roles',
sa.Column('id', sa.BIGINT(), autoincrement=True,
nullable=False),
sa.Column('name', sa.String(100), autoincrement=False,
nullable=False),
sa.Column('users', BigInteger),
sa.Column('permissions', BigInteger),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name', name='roles_name_key')
# sa.ForeignKeyConstraint(['users'],
['users_roles.users_id'], ondelete='CASCADE'),
# sa.ForeignKeyConstraint(['permissions'],
['roles_permissions.permissions_id'], ondelete='CASCADE')
)
op.create_table('permissions',
sa.Column('id', sa.BIGINT(), autoincrement=True,
nullable=False),
sa.Column('name', sa.String(100), autoincrement=False,
nullable=False),
sa.Column('description', sa.String(255)),
sa.Column('users', BigInteger),
sa.Column('roles', BigInteger),
sa.PrimaryKeyConstraint('id')
# sa.ForeignKeyConstraint(['users'],
['users_permissions.users_id'], ondelete='CASCADE'),
# sa.ForeignKeyConstraint(['roles'],
['role_permissions.roles_id'], ondelete='CASCADE')
)
op.create_table('users_roles',
sa.Column('users_id', BigInteger,
sa.ForeignKey('users.id'), primary_key=True),
sa.Column('roles_id', BigInteger,
sa.ForeignKey('roles.id'), primary_key=True)
)
op.create_table('users_permissions',
sa.Column('users_id', BigInteger,
sa.ForeignKey('users.id'), primary_key=True),
sa.Column('permissions_id', BigInteger,
sa.ForeignKey('permissions.id'), primary_key=True)
)
op.create_table('roles_permissions',
sa.Column('roles_id', BigInteger,
sa.ForeignKey('roles.id'), primary_key=True),
sa.Column('permissions_id', BigInteger,
sa.ForeignKey('permissions.id'), primary_key=True)
)
op.drop_constraint('users_name_key', 'users')
op.add_column('users', sa.Column('is_admin', sa.BOOLEAN,
autoincrement=False, nullable=False, default=False))
op.add_column('users', sa.Column('is_active', sa.BOOLEAN,
autoincrement=False, nullable=False, default=True))
op.add_column('users', sa.Column('created', sa.DateTime,
autoincrement=False, nullable=False,
default=datetime.datetime.now))
op.add_column('users', sa.Column('modified', sa.DateTime,
autoincrement=False, nullable=False,
default=datetime.datetime.now))
op.add
What would be the proper way to implement a post migration hook ?
Dear Alembic User Community,
I am looking for suggestion for the best way to address the following
problem:
We have a use case where we'd like to make sure some SQL queries are always
executed after any set migration.
Our particular use case is with PostgreSQL. We have several users who can
create new tables and types in a database.
But we want to make sure the ownership of objects is a group all those
users belong to. But we'd rather make that
transparent so it does not get forgotten in some migrations.
So far we managed to do this by overriding the do_run_migrations() function
in the env.py (*cf.* code below).
That has some good and bad qualities:
- The "hook" runs once whether we pass 1 or more migrations;
- The migrations are applied, even if this last step fails;
- It does not work in "online" mode unless we force a commit (offline mode
w. `--sql` does emit a commit afterwards);
So I wondered if someone would have encountered similar use cases and if
there would be other / better ways to achieve this ?
In particular, a better way to know what is the command being invoked,
cause you don't want to apply this "hook" for commands other
than upgrade and downgrade.
Here is the code snippet:
def do_run_migrations(context):
with context.begin_transaction():
context.run_migrations()
command_name = context.config.cmd_opts.cmd[0].__name__
if command_name in ('downgrade', 'upgrade', ):
context.execute('REASSIGN OWNED BY "{user}" TO "schema-admin"'
.format(user=settings.ALEMBIC_DB.username))
context.execute('COMMIT') # Does not work "online", unless we
do this
Regards,
Nicolas.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
