Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
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 -- 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/-/yhFVlHz44FUJ. 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.
[sqlalchemy] Mapper compilation errors in multi threaded web application using dynamic mapping to selects
Hello,
is there a problem in mapping classes to selects ([1]) /within a
function/? We are running into mapper errors reading
InvalidRequestError: One or more mappers failed to compile.
Exception was probably suppressed within a hasattr() call. Message was:
One or more mappers failed to compile. Exception was probably
suppressed within a hasattr() call. Message was: 'Mapper' object has no
attribute '_props'
when doing so and I could not find out why yet.
Here is our setup: we run a WSGI web application using Apache 2.2.14 in
MPM worker mode (a multi threading server configuration, see [2]),
Python 2.6.6, Werkzeug 0.5.1, SQLAlchemy 0.5.8 and SQLite 3 databases.
Standard tables are mapped as usual, ORM and expression language access
work well.
To provide views with variable parameters, additional dynamic
mappings are established on request, which means by a function. Here is
a simplified example:
def get_view(param):
class DBG(object): pass
dbg = select([records.c.id],
records.c.checksum==param).alias('dbg')
mapper(DBG, dbg)
return (DBG, dbg)
The function provides the select object for use in expressions and the
class for ORM use, but the error continues to show up even if the
results are never used, as in
def some_function():
class DBG(object): pass
dbg = select([records.c.id]).alias('dbg')
mapper(DBG, dbg)
return something_else
The mapper is a wrapped version of SQLAlchemies standard wrapper. It
extends the given class, calls sqlalchemy.orm.mapper() and supplies the
result of that function call, a Mapper object. I suppose it can be
treated to be sqlalchemy.orm.mapper here for simplicity.
Now this works well in a (single threaded) local environment, but on
the web server the application can become unstable when the function is
invoked answering a request. Then, in /some/ subsequent request, the
error shown above can arise, which looks as if something is passed
through from one request to the other. It can take many requests until
this happens but once the error occurred more and more threads are
infected quickly (in a stress test scenario).
The differences I see between our standard mappings and those in this
function are that standard mappers are applied once, while the function
maps multiple times. The second difference is the use of selects instead
of tables. Do you see any problems here?
Is there a known problem with this setup, or where could I continue to
search to find the reason of this problem? Is there a better practice to
be followed?
(I searched for problems in session handling but it seems to me the
application follows the related instructions, using a scoped session and
calling session.remove() at the end of request handling.)
Thanks in advance!
Jochen
References:
[1]
http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html#mapping-a-class-against-arbitrary-selects
[2] http://httpd.apache.org/docs/2.0/mod/worker.html
--
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.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
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 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.
Re: [sqlalchemy] Mapper compilation errors in multi threaded web application using dynamic mapping to selects
On May 9, 2012, at 8:10 PM, Jochen Stenzel wrote: Hello, is there a problem in mapping classes to selects ([1]) /within a function/? with multiple threads, where the mappers initialization may first proceed as the product of a thread running, yes. you'd want to upgrade to 0.7 for the best versions of these fixes, or at least 0.6. If you must stay on 0.5, make sure all modules are fully imported, then run compile_mappers() before starting any threads. \ -- 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.
Re: [sqlalchemy] Mapper compilation errors in multi threaded web application using dynamic mapping to selects
On Wed, May 9, 2012 at 4:11 PM, Michael Bayer mike...@zzzcomputing.com wrote: Hello, is there a problem in mapping classes to selects ([1]) /within a function/? with multiple threads, where the mappers initialization may first proceed as the product of a thread running, yes. you'd want to upgrade to 0.7 for the best versions of these fixes, or at least 0.6. If you must stay on 0.5, make sure all modules are fully imported, then run compile_mappers() before starting any threads. Sweet. Didn't know that one. -- 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.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
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/-/I6OriBExVeAJ. 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.
Re: [sqlalchemy] How to insert a new row into a Sybase database table which has an IDENTITY primary key column?
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 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. 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.
