Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?

[Michael Bayer]

the TRADE table needs the "id" column to be an IDENTITY column, otherwise you have to populate Trade.id with an explicit value.

background on IDENTITY: http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc00170.1260/html/iqapg/iqapg491.htm

Also its very likely you want to be using case insensitive table names, which means all lower case - "trade", "instrument", etc.   Then you won't need that "quote=False" option.   The docstrings for "quote" at docs.sqlalchemy.org/en/rel_0_7/core/schema.html describe what it means to be "case sensitive" vs. "case insensitive".

On May 9, 2012, at 7:31 AM, Anthony Kong wrote:

I did not realisd the mailing list is in google groups after I posted my question on SO:

 

http://stackoverflow.com/questions/10509016/sqlalchemy-how-to-insert-a-new-row-into-a-sybase-database-table-which-has-an-id

 

Hopefully more sqlalchemy experts hang out here. I will reproduce my question here:

 

 

= Question: ==============================================

 

I have defined this class: class Trade(Base):      __tablename__ = 'TRADE'      __table_args__ = {'quote':False}        id = Column(Integer, name='id_trade', primary_key=True)      idImnt = Column(Integer, ForeignKey('INSTRUMENT.id_imnt'), name='id_imnt')  ...  When I tried to commit a new Trade instance back into database, I got this error:     Instance <Trade at 0x954fd90> has a NULL identity key.  If this is   an auto-generated value, check that the database table allows generation of   new primary key values, and that the mapped Column object is configured to   expect these generated values.  Ensure also that this flush() is not   occurring at an inappropriate time, such as within a load() event.  I think I missed something in the id column definition. What is the solution?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/0cYedubox3YJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Anthony Kong]

Hi, Micheal,

Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: 

id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True)

In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table.

However, it still throws the same exception in python land. And it follows by a ROLLBACK

Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY?

Cheers

[Michael Bayer]

On May 9, 2012, at 4:46 PM, Anthony Kong wrote:

Hi, Micheal,

Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: 

id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True)

that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly.  if you are dealing with an existing database, that may not be the case.

In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table.

that's also fine but is there a value for the primary key column ? or is it NULL?

Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY?

the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself.

[Anthony Kong]

1) Yes, I am working with existing table

2) The id column is not included in the SQL, which is the expected behaviour if we want the server to generate value for the IDENTITY column in sybase/ms-sql

3) ok

Any tracing option I can use to see how SQLA deals with the 'id' column?

[Anthony Kong]

I think I have found the root cause of the problem.

 

I am using a sybase database server of version 15 ("Adaptive Server Enterprise/15.0.3/EBF 17163")

 

But for various reasons we are still using sybase ODBC driver 12.5.

 

If I switch to 15.5, it just works as expected.

 

A follow-up question:

 

Says I must stay with driver 12.5, what are the possible workaround for this issue?

 

I think there are at least two possible solutions:

 

A) Use direct query to insert and retrieve @@identity back form the server

 

B) 'hack' the self.dispatch in session to retrieve @@identity and assign it to the 'id' field in flush() event

 

Do they sound plausible? Are there any pointers/resource on how to achieve the above?

 

 

 

Cheers

[Michael Bayer]

On May 10, 2012, at 3:39 AM, Anthony Kong wrote:

I think I have found the root cause of the problem.

 

I am using a sybase database server of version 15 ("Adaptive Server Enterprise/15.0.3/EBF 17163")

 

But for various reasons we are still using sybase ODBC driver 12.5.

 

If I switch to 15.5, it just works as expected.

 

A follow-up question:

 

Says I must stay with driver 12.5, what are the possible workaround for this issue?

 

I think there are at least two possible solutions:

 

A) Use direct query to insert and retrieve @@identity back form the server

 

B) 'hack' the self.dispatch in session to retrieve @@identity and assign it to the 'id' field in flush() event

 

Do they sound plausible? Are there any pointers/resource on how to achieve the above?

not sure what the issue with 12.5 is, SQLAlchemy issues @@identity after the INSERT in the same way.  If it doesn't work with sqlalchemy, it won't work without it, either, unless the driver has some other workaround which can be implemented.

The call to @@identity happens at a very particular place and can't be re-implemented on the outside.

 

 

 

Cheers

On Thursday, May 10, 2012 7:43:43 AM UTC+10, Anthony Kong wrote:

1) Yes, I am working with existing table

2) The id column is not included in the SQL, which is the expected behaviour if we want the server to generate value for the IDENTITY column in sybase/ms-sql

3) ok

Any tracing option I can use to see how SQLA deals with the 'id' column?

 

On Thursday, May 10, 2012 5:09:59 AM UTC+10, Michael Bayer wrote:

On May 9, 2012, at 4:46 PM, Anthony Kong wrote:

Hi, Micheal,

Thanks for the reply. I have added autoincrement to the Column definition of 'id', like so: 

id = Column(Integer, name='id_trade', primary_key=True, autoincrement=True)

that's fine, but the table has to be created that way in the database also, that is, using the IDENTITY keyword explicitly.  if you are dealing with an existing database, that may not be the case.

In the log I can see the id column is not included in the INSERT statement. I have taken that SQL statement out and run it, and a row can be successfully created in the table.

that's also fine but is there a value for the primary key column ? or is it NULL?

Do I need to do anything related to reload? I suspect may be the row cannot be inserted after all but sqlalchemy does not retrieve @@IDENTITY?

the work SQLA does with @@IDENTITY may not be present in the log output of SQLAlchemy itself.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/GzOcXxqMSk0J.