[sqlalchemy] Invalid Request Error
Hi All, I am using sqlalchemy 0.4.4 +mysql 4.1 + TG 1.0.4b3. Some time i am getting the following error : == InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction and should be closed, File c:\python25\lib\site-packages\cherrypy-2.2.1-py2.5.egg\cherrypy \_cphttptools.py, line 105, in _run self.main() File c:\python25\lib\site-packages\cherrypy-2.2.1-py2.5.egg\cherrypy \_cphttptools.py, line 254, in main body = page_handler(*virtual_path, **self.params) File string, line 3, in doCrossDomainCommunication File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\controllers.py, line 344, in expose *args, **kw) File string, line 5, in run_with_transaction File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\database.py, line 403, in sa_rwt req.sa_transaction = make_sa_transaction(session) File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\database.py, line 455, in make_sa_transaction session.begin() File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\scoping.py, line 98, in do return getattr(self.registry(), name)(*args, **kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 489, in begin self.transaction = self.transaction._begin(**kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 171, in _begin self._assert_is_active() File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 159, in _assert_is_active raise exceptions.InvalidRequestError(The transaction is inactive due to a rollback in a subtransaction and should be closed) InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction and should be closed == Can any one help meto slove this problem. Thanks and Regards Reetesh Nigam --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 6, 3:43 am, Ants Aasma ants.aa...@gmail.com wrote:
On May 6, 9:07 am, Vic vctr...@gmail.com wrote:
I'm looking for a way to have my DB replicated in REAL TIME to be used
in case I lose my primary copy.
I saw that the two phase commit exist but I'm not sure if that is the
correct option. I have the feeling that it would be abusing a
mechanism purposed for correlating to separate DBs and not creating
replications.
The preferred way to replicate databases is to use ready-made database
replication tools. For postgres the easiest way to do synchronous
replication is currently pgpool-II. With mysql I'm not so sure,
MySQL has a mechanism for a database to read the log from a master
database and replay the commands. This provides a loose coupling with
near real-time backup of the data. Should the backup server stop or
lose contact, the primary server is unaffected. When the backup
server regains contact, it restarts the log processing from the point
where it left off.
there
is the NDB cluster, but it has its own issues, possibly you can find
similar replication middleware for it.
If you must do the replication inside your application, then my advice
is to do it by subclassing sqlalchemy.engine.{Engine,Connection} to
handle distributing requests to multiple backends and managing
transactions across them. You still need 2 phase commits to achieve
consistency, and the corresponding separate transaction management
that goes with it (to rollback/commit prepared transactions in case of
crashes). Also to avoid inconsitencies you have to get sequence values
from one database, and cannot use volatile functions for inserts,
updates. Also, if you are doing this to get high availability, then
you need figure out, how to bring a replica up online. Don't expect
this to be anything near simple or transparent if you want to have any
kind of concurrency for write queries.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Invalid Request Error
rollback() your Session when an exception is raised. On May 7, 2009, at 3:29 AM, reetesh nigam wrote: Hi All, I am using sqlalchemy 0.4.4 +mysql 4.1 + TG 1.0.4b3. Some time i am getting the following error : = = = = = = = = = = = = == InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction and should be closed, File c:\python25\lib\site-packages\cherrypy-2.2.1-py2.5.egg\cherrypy \_cphttptools.py, line 105, in _run self.main() File c:\python25\lib\site-packages\cherrypy-2.2.1-py2.5.egg\cherrypy \_cphttptools.py, line 254, in main body = page_handler(*virtual_path, **self.params) File string, line 3, in doCrossDomainCommunication File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\controllers.py, line 344, in expose *args, **kw) File string, line 5, in run_with_transaction File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\database.py, line 403, in sa_rwt req.sa_transaction = make_sa_transaction(session) File c:\python25\lib\site-packages\TurboGears-1.0.4b3-py2.5.egg \turbogears\database.py, line 455, in make_sa_transaction session.begin() File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\scoping.py, line 98, in do return getattr(self.registry(), name)(*args, **kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 489, in begin self.transaction = self.transaction._begin(**kwargs) File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 171, in _begin self._assert_is_active() File c:\python25\lib\site-packages\SQLAlchemy-0.4.4-py2.5.egg \sqlalchemy\orm\session.py, line 159, in _assert_is_active raise exceptions.InvalidRequestError(The transaction is inactive due to a rollback in a subtransaction and should be closed) InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction and should be closed = = = = = = = = = = = = == Can any one help meto slove this problem. Thanks and Regards Reetesh Nigam --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] ForeignKey schema and Table schema
Currently schema requires that you provide a schema for each table
that is not in the primary metadata.
Example:
main_engine = sa.create_engine('mysql://user:p...@localhost/main')
other_engine = sa.create_engine('mysql://user:p...@localhost/other')
main_metadata = MetaData()
other_metadata = MetaData()
MainBase = declarative_base(metadata=main_metadata)
OtherBase = declarative_base(metadata=other_metadata)
class MainUser(MainBase):
__tablename__ = 'User'
__table_args__ = {schema : 'main'}
id = sa.Column(Integer, primary_key=True, autoincrement=False)
name = sa.Column(String(16))
class OtherUser(OtherBase):
__tablename__ = 'User'
__table_args__ = {schema : 'other'}
id = sa.Column(Integer, primary_key=True, autoincrement=False)
name = sa.Column(String(16))
This has a severe problem having to hard set the schema when that
value should be part of configuration. Instead the table should assume
the schema of the engine that it's metadata is bound to and
automatically appear in queries where there is a table from a
different schema than the one the query is being ran through.
In the mean time I'm just setting the schema on all the tables after
they are all defined and loaded:
for item in main_metadata.sorted_tables:
item.schema = 'main'
for item in other_metadata.sorted_tables:
item.schema = 'other'
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKey schema and Table schema
Aculeus wrote: This has a severe problem having to hard set the schema when that value should be part of configuration. Instead the table should assume the schema of the engine that it's metadata is bound to and automatically appear in queries where there is a table from a different schema than the one the query is being ran through. if your engine() connects using a certain schema as the default schema, then no explicit schema argument is necessary for tables that are accessed by that engine within that schema. schema is only used when accessing a non-default schema from a single engine. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing existing object with a changed copy
Marcin Krol wrote: if you're looking for state between requests you can use an HTTP session for that *SMACK* forehead... Right.. Mental block.. and lightweight objects are great for those since they are easily serializable and use minimal space. Im a little confused, did you originally intend to persist state in the database between requests ? Well sort of - I wanted to store the 'throwaway' object in db, I didn't think of using http session for that purpose. you said you didn't want to call commit ? I don't want to call commit on original object until user presses Save. It sounds like your user is doing the editing in a JS-powered app in the browser. Would it work for you to leave the under edit version in the browser until the user presses Save (in session cookies and/or DOM objects)? For one thing, this will reduce the HTTP traffic, since there's only the one load of the original data, and one save request. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Querying with ORM - 2 foreign keys
I have a data model that includes a table related to 2 other tables. Here is a slimmed down version of the class declarations. Note that Rvalue is related to both Rparam and Rqmt. There is no problem populating the database appending to the appropriate relation properties. class Spec(Base): __tablename__ = 'Spec' SpecID = Column(Integer, primary_key=True) Spec_Name = Column(String) class Rparam(Base): __tablename__ = 'rp' RparamID = Column(Integer, primary_key=True) Rparam_SpecID = Column(Integer, ForeignKey(Spec.SpecID)) Rparam_Name = Column(String) Spec = relation(Spec, backref='Rparam') class Rqmt(Base): __tablename__ = 'rq' RqmtID = Column(Integer, primary_key=True) Rqmt_SpecID = Column(Integer, ForeignKey(Spec.SpecID)) Rqmt_Name = Column(String) Spec = relation(Spec, backref='Rqmt') class Rvalue(Base): __tablename__ = 'rv' RvalueID = Column(Integer, primary_key=True) Rvalue_RqmtID = Column(Integer, ForeignKey(Rqmt.RqmtID)) Rvalue_RparamID = Column(Integer, ForeignKey(Rparam.RparamID)) Rvalue_Name = Column(String) Rqmt = relation(Rqmt, backref='Rvalue') Rparam = relation(Rparam, backref='Rvalue') I can join the tables and query the structure with this query. Since Rvalue has two foreign keys, SA requires that I specify the join condition. That makes sense because I don't see how SA could determine my intent. q = session.query(Spec.SpecID,Spec.Spec_Name, Rqmt.RqmtID,Rqmt.Rqmt_Name, Rparam.RparamID,Rparam.Rparam_Name, Rvalue.RvalueID,Rvalue.Rvalue_Name).join(Rqmt,Rparam, (Rvalue,and_(Rparam.RparamID==Rvalue.Rvalue_RparamID,Rqmt.RqmtID==Rvalue.Rvalue_RqmtID)) ) Question: Can I express the join to table Rvalue in terms of the relation attributes defined on the classes instead of needing a SQL construct that essentially is restating the foreign key relationships? The documentation for query.join() has some examples using relation attributes, but I can't figure out the syntax when 2 foreign keys are present. http://www.sqlalchemy.org/docs/05/reference/orm/query.html?highlight=#sqlalchemy.orm.query.Query.join A full example can be found at http://pastebin.com/m120d486a -- Mike Conley --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: last_inserted_ids and ORM
Ahh yes, I use autoflush almost exclusively and forget that you can separate the flush() and commit(). -- Mike Conley On Tue, May 5, 2009 at 6:31 PM, Michael Bayer mike...@zzzcomputing.comwrote: Mike Conley wrote: Does the idea of last_inserted_ids exist for ORM? I do session.add(someobj) session.commit() and then want the id of the newly inserted object. I can reference someobj.id but this generates a select call to the database get the id before you call commit(). the same thing would apply with SQL expressions, as the cursor is gone when the transaction is closed. you can call session.flush() to force a flush. alternatively, set expire_on_commit=False on your Session. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Querying with ORM - 2 foreign keys
Mike Conley wrote: Question: Can I express the join to table Rvalue in terms of the relation attributes defined on the classes instead of needing a SQL construct that essentially is restating the foreign key relationships? The documentation for query.join() has some examples using relation attributes, but I can't figure out the syntax when 2 foreign keys are present. http://www.sqlalchemy.org/docs/05/reference/orm/query.html?highlight=#sqlalchemy.orm.query.Query.join the syntax is the same regardless of how many foreign keys are present. whatever the primaryjoin condition of the relation() is, is what will be expressed. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Querying with ORM - 2 foreign keys
Here is what I did first q = session.query(Spec.SpecID,Spec.Spec_Name, Rqmt.RqmtID,Rqmt.Rqmt_Name, Rparam.RparamID,Rparam.Rparam_Name, Rvalue.RvalueID,Rvalue.Rvalue_Name).\ join(Rqmt,Rparam,Rvalue) SA complains about more than one foreign key constraint as expected Then I tried many variations of q = session.query(Spec.SpecID,Spec.Spec_Name, Rqmt.RqmtID,Rqmt.Rqmt_Name, Rparam.RparamID,Rparam.Rparam_Name, Rvalue.RvalueID,Rvalue.Rvalue_Name).\ join(Rqmt,Rparam, (Rvalue, )) I want to be able to use the class attributes that define the relation Rvalue to Rqmt and Rvalue to Rparam, but don't know what use in place of the ?. If I put a tuple in for the ??? (Rvalue,(Rqmt.Rvalue,Rparam.Rvalue)) I get an error File C:\Python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy\sql\expression.py, line 2579, in _from_objects self.left._from_objects + \ AttributeError: 'tuple' object has no attribute '_from_objects' If I separate the join into 2 pieces, (Rvalue, Rqmt.Rvalue),(Rvalue, Rparam.Rvalue) I get a cross join and bad results. The only correct solution I have is to use a SQL expression (Rvalue, and_(Rparam.RparamID==Rvalue.Rvalue_RparamID, Rqmt.RqmtID==Rvalue.Rvalue_RqmtID)) If possible, I want to take advantage of the class mapped attributes for the relations somewhat like the query.join() documentation describes. The problem is how to express a single join when it depends on 2 foreign keys to 2 different targets. Am I missing something, or do I always have to use a SQL expression in this case? -- Mike Conley On Thu, May 7, 2009 at 1:34 PM, Michael Bayer mike...@zzzcomputing.comwrote: Mike Conley wrote: Question: Can I express the join to table Rvalue in terms of the relation attributes defined on the classes instead of needing a SQL construct that essentially is restating the foreign key relationships? The documentation for query.join() has some examples using relation attributes, but I can't figure out the syntax when 2 foreign keys are present. http://www.sqlalchemy.org/docs/05/reference/orm/query.html?highlight=#sqlalchemy.orm.query.Query.join the syntax is the same regardless of how many foreign keys are present. whatever the primaryjoin condition of the relation() is, is what will be expressed. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Querying with ORM - 2 foreign keys
Mike Conley wrote: Here is what I did first q = session.query(Spec.SpecID,Spec.Spec_Name, Rqmt.RqmtID,Rqmt.Rqmt_Name, Rparam.RparamID,Rparam.Rparam_Name, Rvalue.RvalueID,Rvalue.Rvalue_Name).\ join(Rqmt,Rparam,Rvalue) SA complains about more than one foreign key constraint as expected its query.join((dest, onclause)). so query.join((Rparam, Rvalue)) . more examples at http://www.sqlalchemy.org/docs/05/reference/orm/query.html?highlight=join#sqlalchemy.orm.query.Query.join . --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] FOR UPDATE or UPDLOCK
Hello, I have a transaction that involves a SELECT and subsequent UPDATE. It is operating against MSSQL. I need to make sure that the row locks so that other processes may not access it until I have completed my update, or that they at least fail when trying to UPDATE after the first transaction commits. I think that either FOR UPDATE or UPDLOCK would work, but I can't find a way to make either of them work. In the mmsql.py file I find this code: def for_update_clause(self, select): # FOR UPDATE is only allowed on DECLARE CURSOR which SQLAlchemy doesn't use return '' This leads me to believe that FOR UPDATE will not work. I've also tried this s = select(table.c, table.c.field0, [text((UPDLOCK))]) conn.execute(s) Rather than producing SELECT * FROM table (UPDLOCK) where field 0 it instead produces SELECT * FROM table, (UPDLOCK) where field 0 That little comman throws the whole thing off. Can anyone suggest a way for me to accomplish what I'm trying to do in sqlalchemy. Thanks in advance, Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On May 6, 7:56 am, Ed Singleton singleto...@gmail.com wrote:
If it helps, I have finally got my system working, now using FreeTDS
0.82, SQLAlchemy 0.5.3, pymssql, Python 2.5, (all on Mac Leopard) and
SQL Server 2005 (on an WinXP vm).
With this setup, your test passes without any problems.
I also tried it out using pyodbc 2.1.5 and the test failed with this
traceback:
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Must declare the scalar variable @u#. (137)
(SQLExecDirectW)') u'SELECT user_name() as user_name;' []
This is a result of passing unicode statements directly to FreeTDS
which doesn't work.
If you change the test case to indicate that supports_unicode and
supports_unicode_statements = False, then it runs just fine with the
fix. Without the fix it fails as well, which indicates to me the
issue is in FreeTDS.
I'd like to commit this but I want to have a discussion with Mike
Bayer first to be sure he's okay with it.
Michael
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
