Hello Mike, sorry for not having provided the table definition initially. You are absolutely right, I used to define the tables and columns in a declarative way and had the uniqueness-constraints defined on the columns.
As you proposed, setting the unique=False and index=False before invoking the CreateTableOp solves the problem indeed. So thanks a lot once more! Best regards David Am Mittwoch, 4. März 2020 17:54:30 UTC+1 schrieb Mike Bayer: > > > > On Wed, Mar 4, 2020, at 10:27 AM, David Siller wrote: > > 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)? > > > are you using the unique=True flag on the Column itself? that would be > why. It would probably work to set col.unique=False, col.index=False > before applying the Column to the new operation so that these don't double > up for the constraints that we are already getting from the table. let me > know if that works and we can update the demo. > > > > 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 <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy-alembic/b552b5d5-e5f2-4553-85be-9e87350aed92%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy-alembic/b552b5d5-e5f2-4553-85be-9e87350aed92%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/484a1f33-462f-4499-89e4-55a025e0b943%40googlegroups.com.
