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)
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.
