On Mar 2, 2011, at 5:32 PM, Michael Bayer wrote: > > 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.
and, I'd add we can make it actually work the way you thought it did, by letting you say myobject.id = sequence.nextval(), we will see if we can get that into 0.7.0. > > > > >> >> >> >> >> >> 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. > -- 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.
