sqlalchemy - Unique Sequencial Number to column -

i need create sequence in generic case not using sequence class.

usn = column(integer, nullable = false, default=nextusn, server_onupdate=nextusn) 

, funcion nextusn need generate func.max(table.usn) value of rows in model.

i try using

class nextusn(expression.functionelement):     type = numeric()     name = 'nextusn'  @compiles(nextusn) def default_nextusn(element, compiler, **kw):     return select(func.max(element.table.c.usn)).first()[0] + 1 

but in context element not know element.table. exist way resolve this?

this little tricky, these reasons:

1. your select max() return null if table empty; should use coalesce produce default "seed" value. see below.

2. the whole approach of inserting rows select max entirely not safe concurrent use - need make sure 1 insert statement @ time invokes on table or may constraint violations (you should have constraint of kind on column).

3. from sqlalchemy perspective, need custom element aware of actual column element. can achieve either assigning "nextusn()" function column after fact, or below i'll show more sophisticated approach using events.

4. i don't understand you're going "server_onupdate=nextusn". "server_onupdate" in sqlalchemy doesn't run sql you, placeholder if example created trigger; "select max(id) table" thing insert pattern, i'm not sure mean happening here on update.

5. the @compiles extension needs return string, running select() there through compiler.process(). see below.

example:

from sqlalchemy import column, integer, create_engine, select, func, string sqlalchemy.ext.declarative import declarative_base sqlalchemy.sql.expression import columnelement sqlalchemy.schema import columndefault sqlalchemy.ext.compiler import compiles sqlalchemy import event  class nextusn_default(columndefault):     "container nextusn() element."     def __init__(self):         super(nextusn_default, self).__init__(none)  @event.listens_for(nextusn_default, "after_parent_attach") def set_nextusn_parent(default_element, parent_column):     """listen when nextusn_default() associated column,     assign nextusn().      """     assert isinstance(parent_column, column)     default_element.arg = nextusn(parent_column)   class nextusn(columnelement):     """represent "select max(col) + 1 table".     """     def __init__(self, column):         self.column = column  @compiles(nextusn) def compile_nextusn(element, compiler, **kw):     return compiler.process(                 select([                     func.coalesce(func.max(element.column), 0) + 1                 ]).as_scalar()             )  base = declarative_base()  class a(base):     __tablename__ = 'a'      id = column(integer, default=nextusn_default(), primary_key=true)     data = column(string)  e = create_engine("sqlite://", echo=true)  base.metadata.create_all(e)  # pre-execute default know pk value # result.inserted_primary_key available e.execute(a.__table__.insert(), data='single row')  # run default expression inline within insert e.execute(a.__table__.insert(), [{"data": "multirow1"}, {"data": "multirow2"}])  # run default expression inline within insert, # result.inserted_primary_key not available e.execute(a.__table__.insert(inline=true), data='single inline row')