Hi Mike, Thank you for the time you gave me. The other problem I had to do was that my models were distributed in different files. Alembic could not access their class and therefore could not auto-generate his file.
Best regards, Le jeu. 13 déc. 2018 à 19:48, Mike Bayer <mike...@zzzcomputing.com> a écrit : > I can't reproduce any problem. > > Filling out your mappings from your first email, these look like: > > from sqlalchemy import * > from sqlalchemy.orm import relationship > from datetime import datetime > > from sqlalchemy.ext.declarative import declarative_base as db > target_metadata = MetaData() > > DeclarativeBase = db(metadata=target_metadata) > > users_roles = Table( > 'users_roles', target_metadata, > Column('users_id', ForeignKey('users.id'), primary_key=True), > Column('roles_id', ForeignKey('roles.id'), primary_key=True), > ) > > roles_permissions = Table( > 'roles_permissions', target_metadata, > Column('permissions_id', ForeignKey('permissions.id'), > primary_key=True), > Column('roles_id', ForeignKey('roles.id'), primary_key=True), > ) > users_permissions = Table( > 'users_permissions', target_metadata, > Column('users_id', ForeignKey('users.id'), primary_key=True), > Column('permissions_id', ForeignKey('permissions.id'), > primary_key=True), > ) > > > 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, > onupdate=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) > > > then i run autogenerate, migrations are generated as: > > def upgrade(): > # ### commands auto generated by Alembic - please adjust! ### > op.create_table('permissions', > sa.Column('id', sa.BigInteger(), nullable=False), > sa.Column('name', sa.String(length=100), nullable=False), > sa.Column('description', sa.String(length=255), nullable=True), > sa.PrimaryKeyConstraint('id'), > sa.UniqueConstraint('name') > ) > op.create_table('roles', > sa.Column('id', sa.BigInteger(), nullable=False), > sa.Column('name', sa.String(length=100), nullable=False), > sa.PrimaryKeyConstraint('id'), > sa.UniqueConstraint('name') > ) > op.create_table('users', > sa.Column('id', sa.BigInteger(), nullable=False), > sa.Column('email', sa.String(length=100), nullable=False), > sa.Column('name', sa.String(length=100), nullable=False), > sa.Column('hashed_password', sa.String(length=100), nullable=False), > sa.Column('is_admin', sa.BOOLEAN(), nullable=True), > sa.Column('is_active', sa.BOOLEAN(), nullable=True), > sa.Column('created', sa.DateTime(), nullable=True), > sa.Column('modified', sa.DateTime(), nullable=True), > sa.PrimaryKeyConstraint('id'), > sa.UniqueConstraint('email') > ) > op.create_table('roles_permissions', > sa.Column('permissions_id', sa.BigInteger(), nullable=False), > sa.Column('roles_id', sa.BigInteger(), nullable=False), > sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ), > sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ), > sa.PrimaryKeyConstraint('permissions_id', 'roles_id') > ) > op.create_table('users_permissions', > sa.Column('users_id', sa.BigInteger(), nullable=False), > sa.Column('permissions_id', sa.BigInteger(), nullable=False), > sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ), > sa.ForeignKeyConstraint(['users_id'], ['users.id'], ), > sa.PrimaryKeyConstraint('users_id', 'permissions_id') > ) > op.create_table('users_roles', > sa.Column('users_id', sa.BigInteger(), nullable=False), > sa.Column('roles_id', sa.BigInteger(), nullable=False), > sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ), > sa.ForeignKeyConstraint(['users_id'], ['users.id'], ), > sa.PrimaryKeyConstraint('users_id', 'roles_id') > ) > > > then I run upgrade, SQL script output matches what you specified: > > INFO [alembic.runtime.migration] Running upgrade -> 678e61b9e311, rev1 > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE permissions ( > id BIGSERIAL NOT NULL, > name VARCHAR(100) NOT NULL, > description VARCHAR(255), > PRIMARY KEY (id), > UNIQUE (name) > ) > > > INFO [sqlalchemy.engine.base.Engine] {} > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE roles ( > id BIGSERIAL NOT NULL, > name VARCHAR(100) NOT NULL, > PRIMARY KEY (id), > UNIQUE (name) > ) > > > INFO [sqlalchemy.engine.base.Engine] {} > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE users ( > id BIGSERIAL NOT NULL, > email VARCHAR(100) NOT NULL, > name VARCHAR(100) NOT NULL, > hashed_password VARCHAR(100) NOT NULL, > is_admin BOOLEAN, > is_active BOOLEAN, > created TIMESTAMP WITHOUT TIME ZONE, > modified TIMESTAMP WITHOUT TIME ZONE, > PRIMARY KEY (id), > UNIQUE (email) > ) > > > INFO [sqlalchemy.engine.base.Engine] {} > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE roles_permissions ( > permissions_id BIGINT NOT NULL, > roles_id BIGINT NOT NULL, > PRIMARY KEY (permissions_id, roles_id), > FOREIGN KEY(permissions_id) REFERENCES permissions (id), > FOREIGN KEY(roles_id) REFERENCES roles (id) > ) > > > INFO [sqlalchemy.engine.base.Engine] {} > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE users_permissions ( > users_id BIGINT NOT NULL, > permissions_id BIGINT NOT NULL, > PRIMARY KEY (users_id, permissions_id), > FOREIGN KEY(permissions_id) REFERENCES permissions (id), > FOREIGN KEY(users_id) REFERENCES users (id) > ) > > > INFO [sqlalchemy.engine.base.Engine] {} > INFO [sqlalchemy.engine.base.Engine] > CREATE TABLE users_roles ( > users_id BIGINT NOT NULL, > roles_id BIGINT NOT NULL, > PRIMARY KEY (users_id, roles_id), > FOREIGN KEY(roles_id) REFERENCES roles (id), > FOREIGN KEY(users_id) REFERENCES users (id) > ) > > > > > On Thu, Dec 13, 2018 at 12:18 PM patrick payet <papa...@gmail.com> wrote: > > > > Hi Mike, > > The message has been truncated. > > > > The CREATE TABLE statements is : > > CREATE TABLE users ( > > id BIGSERIAL NOT NULL, > > email VARCHAR(100) NOT NULL, > > name VARCHAR(100) NOT NULL, > > hashed_password VARCHAR(100) NOT NULL, > > is_admin BOOLEAN, > > is_active BOOLEAN, > > created TIMESTAMP WITHOUT TIME ZONE, > > modified TIMESTAMP WITHOUT TIME ZONE, > > PRIMARY KEY (id), > > UNIQUE (email) > > ) > > > > CREATE TABLE permissions ( > > id BIGSERIAL NOT NULL, > > name VARCHAR(100) NOT NULL, > > description VARCHAR(255), > > PRIMARY KEY (id), > > UNIQUE (name) > > ) > > > > CREATE TABLE roles ( > > id BIGSERIAL NOT NULL, > > name VARCHAR(100) NOT NULL, > > PRIMARY KEY (id), > > UNIQUE (name) > > ) > > > > CREATE TABLE users_roles ( > > users_id BIGINT NOT NULL, > > roles_id BIGINT NOT NULL, > > PRIMARY KEY (users_id, roles_id), > > FOREIGN KEY(users_id) REFERENCES users (id), > > FOREIGN KEY(roles_id) REFERENCES roles (id) > > ) > > > > CREATE TABLE roles_permissions ( > > roles_id BIGINT NOT NULL, > > permissions_id BIGINT NOT NULL, > > PRIMARY KEY (roles_id, permissions_id), > > FOREIGN KEY(roles_id) REFERENCES roles (id), > > FOREIGN KEY(permissions_id) REFERENCES permissions (id) > > ) > > > > CREATE TABLE users_permissions ( > > users_id BIGINT NOT NULL, > > permissions_id BIGINT NOT NULL, > > PRIMARY KEY (users_id, permissions_id), > > FOREIGN KEY(users_id) REFERENCES users (id), > > FOREIGN KEY(permissions_id) REFERENCES permissions (id) > > ) > > > > My mistake is to have mixed in my mind the information of the object > mapping and the relational constraints of the database. > > But why Alembic can’t create this model in auto-generation mode? > > > > Best regards > > > > Le jeu. 13 déc. 2018 à 18:12, patrick payet <papa...@gmail.com> a écrit > : > >> > >> PRIMARY KEY (id), > >> UNIQUE (email) > >> ) > >> > >> CREATE TABLE permissions ( > >> id BIGSERIAL NOT NULL, > >> name VARCHAR(100) NOT NULL, > >> description VARCHAR(255), > >> PRIMARY KEY (id), > >> UNIQUE (name) > >> ) > >> > >> CREATE TABLE roles ( > >> id BIGSERIAL NOT NULL, > >> name VARCHAR(100) NOT NULL, > >> PRIMARY KEY (id), > >> UNIQUE (name) > >> ) > >> > >> CREATE TABLE users_roles ( > >> users_id BIGINT NOT NULL, > >> roles_id BIGINT NOT NULL, > >> PRIMARY KEY (users_id, roles_id), > >> FOREIGN KEY(users_id) REFERENCES users (id), > >> FOREIGN KEY(roles_id) REFERENCES roles (id) > >> ) > >> > >> CREATE TABLE roles_permissions ( > >> roles_id BIGINT NOT NULL, > >> permissions_id BIGINT NOT NULL, > >> PRIMARY KEY (roles_id, permissions_id), > >> FOREIGN KEY(roles_id) REFERENCES roles (id), > >> FOREIGN KEY(permissions_id) REFERENCES permissions (id) > >> ) > >> > >> CREATE TABLE users_permissions ( > >> users_id BIGINT NOT NULL, > >> permissions_id BIGINT NOT NULL, > >> PRIMARY KEY (users_id, permissions_id), > >> FOREIGN KEY(users_id) REFERENCES users (id), > >> FOREIGN KEY(permissions_id) REFERENCES permissions (id) > >> ) > >> > >> My mistake is to have mixed in my mind the information of the object > mapping and the relational constraints of the database. > >> But why Alembic can’t create this model in auto-generation mode? > >> > >> Best regards > >> > >> Le jeu. 13 déc. 2018 à 14:44, Mike Bayer <mike...@zzzcomputing.com> a > écrit : > >>> > >>> On Thu, Dec 13, 2018 at 7:11 AM patrick payet <papa...@gmail.com> > wrote: > >>> > > >>> > Hi Mike, > >>> > Thanks for your answer. > >>> > My problem it’s a classical problem for application development, I > want to make authentication and authorisation for a web application. > >>> > I want to understand why Alembic can’t create the model in > autogeneration mode and how modify this model to resolve this problem. > >>> > With this model I have a join table to provide a many-to-many > relationship between users and roles (users group) for example. > >>> > This allows me to access all roles for one user. For this, I declare > a variable roles in users class and I want to make a foreign key with this > join table > >>> > to reach the roles. > >>> > This joins table has a composite primary key (users.id foreign key > and roles.id foreign key) and I don’t know how to make the link (foreign > key) with alembic for the users.roles and this composite primary key. > >>> > >>> the link is the user_roles table and the FOREIGN KEY constraints it > >>> has between users and roles tables. you can't locate a unique row > >>> in user_roles given only the user table because it has no column that > >>> refers to user_roles.roles_id. at least I'm trying to parse your > >>> words as given. however CREATE TABLE statements will show exactly > >>> what you mean in case it is something different that just happens to > >>> sound the same when described. > >>> > >>> When I declare just one element of this primary key, I have the error > >>> quoted previously ( there is no unique constraint matching given keys > >>> for referenced table "users_roles"). > >>> > I will send you the CREATE TABLE statements by email in a moment. > >>> > >>> yup > >>> > >>> > >>> > >>> > Best regards, > >>> > Patrick > >>> > > >>> > Le mer. 12 déc. 2018 à 19:40, Mike Bayer <mike...@zzzcomputing.com> > a écrit : > >>> >> > >>> >> On Wed, Dec 12, 2018 at 1:01 PM patrick payet <papa...@gmail.com> > wrote: > >>> >> > > >>> >> > 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 > >>> >> > >>> >> it's not clear here if you're saying alembic is adding foreign keys > >>> >> where they dont belong during autogenerate, or if you dont know why > >>> >> you are getting the error you are getting. The error is because you > >>> >> don't need FOREIGN KEY constraints on roles, permissions, or users - > >>> >> the FOREIGN KEY is only on the many-to-many tables, in this case > >>> >> users_roles, users_permissions, roles_permissions. > >>> >> > >>> >> otherwise if you can illustrate the exact CREATE TABLE statements > >>> >> you'd like to see, I can illustrate a correct model for that which > >>> >> Alembic will generate. > >>> >> > >>> >> > >>> >> > >>> >> > <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. > >>> >> > >>> >> -- > >>> >> 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. > >>> > >>> -- > >>> 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. > > -- > 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.