An addition: It keeps the correct name if the UniqueConstraint involves multiple columns. The duplication only happens if the constraint checks the uniqueness on a single column.
Also the CreateTableOp does not seem to duplicate the UniqueConstraints, so it must happen later on. Best regards David Am Mittwoch, 4. März 2020 16:27:26 UTC+1 schrieb David Siller: > > Hello Mike, > > and thank you very much for the solution. It is working flawlessly for > ordering the columns. > > For others finding this thread: Mike created an entry in the Alembic > cookbook > <https://alembic.sqlalchemy.org/en/latest/cookbook.html#apply-custom-sorting-to-table-columns-within-create-table> > . > > There is just one (minor) flaw, where I currently don't know how to fix > it, but I can adjust the generated script manually: > I'm using the naming schema for constraints and indexes as described in > the documentation <https://alembic.sqlalchemy.org/en/latest/naming.html>. > While the names for e.g. foreign keys, indexes or primary key constraints > are kept with your solution, UniqueConstraints are strangely duplicated, > once with the provided name and once just with the column name. This > happens just for UniqueConstraints. So you have e.g. something like the > following in the generated script: > > sa.UniqueConstraint("iso_name"), > sa.UniqueConstraint("iso_name", name=op.f("uq_country_iso_name")), > > So "uq_country_iso_name" in this case is the name provided by the naming > schema, while the first UniqueConstraint is generated as duplication. > > Maybe any hint on what I'm doing wrong? Or maybe a bug in the > Column.copy-method > (although the Column.constraints-set on the copied column seems to be > empty; also the copied columns-list as in your example contains the > correct UniqueConstraint with the correct name; so maybe it happens in > the CreateTableOp)? > > Thanks again for your solution > David > > > > Am Dienstag, 3. März 2020 15:54:17 UTC+1 schrieb Mike Bayer: >> >> >> >> On Tue, Mar 3, 2020, at 3:36 AM, David Siller wrote: >> >> Hello, >> >> first and foremost: thank you for SQLAlchemy and Alembic. I've worked >> with a lot of ORMs, but only with these two I feel very comfortable and I'm >> doing a lot of crazy stuff with it. >> >> The current problem that I have: I'm currently creating a lot of tables >> with a lot of mixins, as most of them have e.g. an id-column or columns >> for created_at-dates etc. However it seems that I can't control the >> order of the columns, resulting in e.g. the primary-key-id-column to >> show up in the middle of the other columns or the created_at-column at >> the beginning and the created_by-column at the end, especially when I >> autogenerate the versioning scripts. >> >> >> Declarative with mixins would hopefully be able to do a little better >> than that, if the issue is just amongst a small handful of columns maybe >> there's a way to fix it at that level. This ordering is actually >> controlled by an attribute on every column called "_creation_order". It >> gets set when the column is first created based on a global counter and >> that is how the sorting of the columns works within Declarative; it sorts >> on that attribute and sends the Column objects to Table in that order. >> >> also, I don't know how the column ordering that comes from the mappings >> would be different if you rendered metadata.create_all() versus using >> alembic autogenerate, it's the same Table objects. >> >> >> >> >> >> >> But alembic has also a method in this case (as always ;-)). So I created >> a Rewriter-method for the CreateTableOps in the env.py-file, re-order >> the columns in the correct way and reassign this ordered list of columns to >> op.columns. Unfortunately this doesn't work. Somehow it either uses >> whatever is already the to_table()-method (?) or ... something else. So >> I tried to create a new operation in the rewriter with the ordered list and >> returned this operation instead. But then I get an the error: >> sqlalchemy.exc.ArgumentError: >> Column object 'id' already assigned to Table 'user'. >> >> >> >> so the CreateTableOp and a lot of the other ops have an "_orig" attribute >> that points to the "original" object being autogenerated. For this >> object, it's "_orig_table". These objects did not anticipate being >> rewritten at the level at which you are attempting to do. So to make the >> columns-in-place get recognized, I have to delete _orig_table: >> >> op._orig_table = None >> >> then it tries to build the Table again, as we want it to do. but then we >> have the same problem which is that these columns are from your existing >> mapping and they are already associated with a table. >> >> There's a bunch of ways to go here but none of them would be something >> people could normally figure out without asking me here. I think the >> cleanest way is to copy the columns using their copy() method, and then to >> avoid dealing with _orig_table, make a new CreateTableOp: >> >> @writer.rewrites(ops.CreateTableOp) >> def order_columns(context, revision, op): >> >> special_names = {"id": -100, "created_at": 1001, "updated_at": 1002} >> >> cols_by_key = [ >> ( >> special_names.get(col.key, index) >> if isinstance(col, Column) >> else 2000, >> col.copy(), >> ) >> for index, col in enumerate(op.columns) >> ] >> >> columns = [ >> col for idx, col in sorted(cols_by_key, key=lambda entry: >> entry[0]) >> ] >> return ops.CreateTableOp(op.table_name, columns, schema=op.schema, ** >> op.kw) >> >> >> let me just stick this in the recipes now because people wouldn't know to >> do this. >> >> >> >> >> >> The code I'm using is the following: >> >> from operator import itemgetter >> from alembic.autogenerate import rewriter >> from alembic.operations.ops import CreateTableOp >> from sqlalchemy.sql.schema import Column >> >> >> writer = rewriter.Rewriter() >> >> >> @writer.rewriter(CreateTableOp) >> def order_columns(context, revision, op): >> """Reorder the columns before creating a table.""" >> preordered = [] >> for col in op.columns: >> k = 0 # key used for ordering later on >> if not isinstance(col, Column): >> k = 99 # All constraints or indexes should stay at the end >> of the definition >> elif col.primary_key and col.name=='id': >> k = 1 >> # + a lot of other ordering constraints for other columns >> else: >> k= 2 # All non-id-columns >> preordered.append((k, col)) # Creating my ordered list >> >> # Now sorting the list and extracting only the column-objects. >> # This list is indeed correctly sorted, just what I want >> ordered_column_list = [itm[1] for itm in sorted(preordered, key= >> itemgetter(0))] >> >> # Creating a new operation and returning it is not working, as it >> results in an error: >> # Returning: ArgumentError: Column object '...' already assigned to >> Table '...' >> # new_op = CreateTableOp(op.table_name, ordered_column_list, >> schema=op.schema) >> # return new_op >> >> # Reassigning the ordered column list is not working either, it >> seems to be ignored: >> op.columns = ordered_column_list >> return op >> >> [...] >> >> def run_migrations_online(): >> [...] >> with connectable.connect() as connection: >> context.configure( >> [...] >> process_revision_directives=writer >> ) >> [...] >> >> >> The problem is similar to Altering the behavior of AddColumn >> <https://groups.google.com/forum/#!searchin/sqlalchemy-alembic/order%7Csort:date/sqlalchemy-alembic/izYq2EMYotI/gMISQpjkAwAJ>, >> >> but I need to order multiple columns. The ordering works perfectly fine, >> the rewriter is also invoked (tested it e.g. by returning [] instead or >> debugging), just the reordered list of columns is then totally ignored. >> >> Any hint to point me in the right direction of what I'm doing wrong or >> any other possibility to do the reordering? >> >> Thank you in advance and thanks again >> >> David >> >> >> -- >> 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. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy-alembic/ef49db6c-420c-49ec-a708-86ad5d874fc1%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy-alembic/ef49db6c-420c-49ec-a708-86ad5d874fc1%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> >> -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/40838343-c3fb-4c90-9b7a-bf1d62b425bd%40googlegroups.com.
