Stand-alone Sequences are not created by create_all

[Chris Macklin]

If I declare a Sequence at the class level in a model class declaration using declarative (SQLAlchemy 0.9.10 on postgres), the sequence is not created by create_all unless the sequence is explicitly associated with a Column.  My use case is a column which stores a URI; if a row is inserted which already has a URI, it is passed in.  If an insert doesn't come with a URI, we need to mint a new one, and thus call a default function which retrieves the next value of a sequence that is exclusively used for minting these URIs, and appends it to a namespace before adding that value to the column.  Thus, the sequence isn't actually associated with a column, but should still be table-level and created/destroyed when the table is added or dropped.

All of the workarounds I've found so far are somewhat distasteful:

- creating a dummy table, associating the sequence with a column in that table, and never inserting into it (not great as the sequence is detached from the table it is logically associated with, plus the chaff of a totally meaningless table sitting in my db)

- manually calling create on the sequence with the engine; this doesn't really work, as all of this code is sitting in a larger framework in which sqlalchemy is executing and it would require significant alterations to the framework to pass these sequences all the way out to the top level where create_all is called.

Any suggestions?  Am I missing something obvious here?

[Simon King]

According to:

http://docs.sqlalchemy.org/en/rel_1_0/core/defaults.html#sqlalchemy.schema.Sequence.params.metadata

“””
Additionally, the appropriate CREATE SEQUENCE/ DROP SEQUENCE DDL commands will be emitted corresponding to this Sequence when MetaData.create_all() andMetaData.drop_all() are invoked.
“””

For this to work, the Sequence needs to be attached to the Metadata, which you can access as Base.metadata.

Does that make any difference?

Simon

[Chris Macklin]

Yes indeed, that does the trick.  Thank you!

I would kindly suggest referencing this capability from higher up on that page.  It seems relevant for the section discussing that sequences associated with a table will be created/destroyed with the table, but this is the key piece of information that makes it clear *how* to achieve this association.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/zwz1grNHNio/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.