Determining postgres sequence name for a table

[Dusty Phillips]

Hi there,

I'm accessing a postgres database and would like to use the
session.execute(Sequence('sequence_name')) syntax to get the next id
for a table without actually saving the table. This all works fine if
I know the sequence_name, but I was hoping to write generic code. Is
it possible, using sqlalchemy, to discover the name of the sequence on
a particular table. Basically, I'm looking for the equivalent of the
postgres expression

select pg_get_serial_sequence('table_name', 'column_name');

The best I've come up with so far is pretty ugly!:

rs = engine.execute("select nextval(pg_get_serial_sequence('" +
self.orm_table._sa_class_manager.mapper.local_table.name + "',
'id'))")

new_id = rs.fetchone()['nextval']

Is there a more alchemized way of getting the next value, or barring
that, is there a way I can get the table from the orm object a bit
more elegantly?

Thanks,
Dusty

[Michael Bayer]

table reflection does get a value for sequence defaults in PG, and SQLA
then knows how to execute the sequence. there is an issue specifically
when the sequence name has been changed in that PG no longer provides
consistent access to the sequence name (theres a trac ticket for that
issue), but it works for the typical use case.