I had a SQLAlchemy model like -
<code>
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 "<User(name='%s', email='%s', hashed_password='%s')>" %
(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 "<Role(name='%s')>" % 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 "<Permission(name='%s', description='%s')>" % (self.name,
self.description)
</code>
However, Alembic is not generating the correct upgrade, i try to make it
<code>
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_column('users', sa.Column('roles', BigInteger))
op.add_column('users', sa.Column('permissions', BigInteger))
op.create_foreign_key(constraint_name="users_roles_fk",
source_table="users", referent_table="users_roles",
local_cols=["roles"], remote_cols=["roles_id"],
ondelete='CASCADE')
op.create_foreign_key("users_permissions_fk", "users",
"users_permissions", ["permissions"], ["permissions_id"],
ondelete='CASCADE')
op.create_foreign_key("permissions_users_fk", "permissions",
"users_permissions", ["users"], ["users_id"],
ondelete='CASCADE')
op.create_foreign_key("permissions_roles_fk", "permissions",
"roles_permissions", ["roles"], ["roles_id"],
ondelete='CASCADE')
op.create_foreign_key("roles_users_fk", "roles", "users_roles",
["users"], ["users_id"],
ondelete='CASCADE')
op.create_foreign_key("roles_permissions_fk", "roles",
"roles_permissions", ["permissions"], ["permissions_id"],
ondelete='CASCADE')
</code>
i have an error, primary key for users_roles is a composite keys and i dont
know how declare it, how make that
<code>
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no
unique constraint matching given keys for referenced table "users_roles"
[SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN KEY(roles)
REFERENCES users_roles (roles_id) ON DELETE CASCADE'] (Background on this
error at: http://sqlalche.me/e/f405)
</code>
--
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.
