Sequence doesn't inherit schema from metadata

[Alistair Watson]

The code below emits:

CREATE SEQUENCE test_sequence

when according to the documentation it should get it's default schema value from the metadata and it should emit:

CREATE SEQUENCE test_schema.test_sequence

It's not really an issue if you are connecting as the user/schema you want to own the sequence - unfortunately I need to create all tables and database objects as a different user. Providing the schema parameter to each sequence directly does solve the issue.

from sqlalchemy import create_engine, MetaData, Column, Integer,Sequence
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('oracle+cx_oracle://test_schema:Welcome1@localhost:1521/xe', echo=True)
metadata = MetaData(schema='test_schema')
Base = declarative_base(metadata=metadata)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, Sequence(name="test_sequence"), primary_key=True)
    data = Column(Integer)

Base.metadata.drop_all(engine, checkfirst = True)    
Base.metadata.create_all(engine)

[Mike Bayer]

when according to the documentation it should get it's default schema value from the metadata and it should emit:

That's if you specify the MetaData to the Sequence:

http://docs.sqlalchemy.org/en/latest/core/defaults.html?highlight=sequence%20metadata#sqlalchemy.schema.Sequence.params.metadata

if there's docs that imply this step isn't necessary then please point that out.

See https://bitbucket.org/zzzeek/sqlalchemy/issues/3951/postgresql-sequence-not-created-by#comment-35624987 for why this is the way it is.

[Mike Bayer]

Duplicate of #3951.

[Mike Bayer]

I'm looking through the Sequence docs and I can say that theres a lot of sucky crap in there at the moment, let me clean some of it up.

[Alistair Watson]

Ah Ok, I see the problem. I had read from the metadata argument section:

• Note that when a Sequence is applied to a Column, the Sequence is automatically associated with the MetaData object of that column’s parent Table, when that association is made.

and from the schema argument section:

• The rules for selecting the schema name when a MetaData is also present are the same as that of Table.schema.

and put 2 + 2 together to make 5. I assumed (wrongly) that the default metadata associated with the table would be passed down to the Sequence. I see now that there are other use cases that require the ability to associate the sequence with Metadatas other than those of the columns parent table. I'll have a look to see how I can pass this in. Thanks again for your help. This is an awesome library and I can see how much work has gone into it!

[Alistair Watson]

Just seen the updated documentation - much clearer now, thank you.

[Mike Bayer]

yeah you were right that "associated with metadata" language was from before there was a "schema" argument or the Sequence.metadata option at all, very out of date. the docs now just say, "please set the metadata" because there usually isn't a reason not to.