Catherine Devlin <[email protected]> wrote:
> I want* to get the current values of all the sequences in a database, or at
> least the ones that are being used to autoincrement primary keys.
>
> Can I find those with SQLAlchemy? I haven't been able to find them in
> metadata after ``metadata.reflect()`` yet, or with ``reflection.Inspector``.
> ``metadata._sequences`` is empty even though schemas definitely exist in the
> database (and I shouldn't rely on an underscore-preceded attribute anyway).
> The closest I've been able to find is::
>
> str(meta.tables['mytable'].c.id.server_default.arg)
> "nextval('mytable_id_seq'::regclass)"
>
> ... but trying to scrape my sequence names out of strings like that feels
> dangerous.
>
> * - Reason: For DDLGenerator, I want to be able to dump SQLAlchemy statements
> that duplicate a small database in SQLAlchemy terms. In this case, for
> py.test fixtures - so you can work out your test database in live SQL, or
> with a tool like rdbms_subsetter, and then generate a .py file that fits
> nicely into your unit testing environment. So far so good, except I'm
> inserting rows with their primary key values, but nothing is updating the
> values of the sequences associated with them, so the next attempted insert
> fails with a primary key violation.
Hi Catherine -
reflection of sequences in full is a TODO.
Right now there is only limited functionality for sequences, mostly some
dialects have a “has_sequence(somename)” method, and that’s about it. The
MSSQL and Sybase dialects have some ability to get at the current value +
increment but it’s not very well exposed.
That said, the only database that associates sequences with tables/columns in
the database catalogs itself is Postgresql, so the job of locating sequences
used to “autoincrement primary keys”, if you don’t have their names already,
would only be possible on Postgresql and then only in conjunction with columns
that explicitly use SERIAL. If you do have their names, then yes we still
lack an inspector.get_sequence(name) method. It’s a relatively
straightforward effort that can be contributed, however. The queries for
getting at SERIAL sequences need to be against the pg catalogs, there’s some
code which deals with this in Alembic if you look in alembic/ddl/postgresql.py.
Current issue here:
https://bitbucket.org/zzzeek/sqlalchemy/issue/2056/get_sequence_names
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
