On Mar 2, 2011, at 5:13 PM, Michael Bayer wrote: > Sequence() is a DDL construct, not a SQL construct, so you don't embed it > into an INSERT statement directly, nor can you assign it to an attribute on a > mapped object (as would appear to be the case here). It is a marker applied > to a Column() object to note the column's generator for default values. > > The usage of Sequence in table metadata is described at: > > http://www.sqlalchemy.org/docs/core/schema.html#defining-sequences > > When using the ORM in particular, the Sequence needs to be used in this way > so that SQLAlchemy has a means of acquiring the newly generated identifier. > On Firebird, when the Table metadata has a Sequence associated, the gen_id() > function is embedded inline and the new identifier is acquired via RETURNING. > This applies to all insert() constructs generated against the Table object, > not just those used by the ORM. > > Another option is to explicitly execute the sequence object ahead of time - a > Sequence can be passed to the execute() method of any > engine/connection/Session to invoke its next id. This is not the best > pattern in conjunction with inserts: > > myobject.id = Session.execute(my_sequence_object)
continuing, not the best pattern because, its two separate executions instead of one and is more verbose in any case. > > > > > > On Mar 2, 2011, at 3:10 PM, bigt wrote: > >> my Firebird database has some Sequence values defined as >> >> CREATE SEQUENCE S_org; >> >> with an appropriate trigger to set a value. >> >> RECREATE TRIGGER TBI_org FOR org >> ACTIVE BEFORE INSERT POSITION 0 >> AS BEGIN >> IF (NEW.Id IS NULL) THEN >> NEW.Id = NEXT VALUE FOR S_org; >> END^ >> >> >> Using reflection in sqlalchemy i get an error when trying to store an >> entry in the ORG table. >> >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >> line 1392, in flush >> self._flush(objects) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/session.py", >> line 1473, in _flush >> flush_context.execute() >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >> unitofwork.py", line 302, in execute >> rec.execute(self) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/ >> unitofwork.py", line 446, in execute >> uow >> File "/usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", >> line 1884, in _save_obj >> execute(statement, params) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1191, in execute >> params) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1271, in _execute_clauseelement >> return self.__execute_context(context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1302, in __execute_context >> context.parameters[0], context=context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1401, in _cursor_execute >> context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1394, in _cursor_execute >> context) >> File "/usr/lib/python2.7/site-packages/sqlalchemy/dialects/firebird/ >> base.py", line 680, in do_execute >> cursor.execute(statement, parameters or []) >> InterfaceError: (InterfaceError) (0L, "Error while attempting to >> convert object of type <class 'sqlalchemy.schema.Sequence'> to >> database-internal numeric type for storage in field [name not known at >> this stage of query execution]. The invalid input object is: >> Sequence(u's_org', start=1, increment=1, optional=False)") 'INSERT >> INTO org (id, name, contact, dept, vatnr, country, city, street, >> building, postcode, phone, fax, email) VALUES >> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' (Sequence(u's_org', start=1, >> increment=1, optional=False), u'xyz', None, None, None, u'CH', None, >> None, None, None, None, None, None) >> >> >> It appears that Sqlalchemy does not process correctly the SEQUENCE >> keyword in recent versions of Firebird >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
