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.
> 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/9729f06e-ace6-4326-93bf-bf7d0cac3a4a%40www.fastmail.com.
