Re: [sqlalchemy] complex in clause
Michael Bayer ha scritto: search the docs for tuple_. wonderful! thank you Gla -- Glauco Uri Prometeia SpA Via G. Marconi, 43 - 40122 Bologna Via Gonzaga, 7 - 20123 Milano Via Tirso, 26 - 00198 Roma Italia e-mail : glauco@prometeia.it phone : +39 051 6480911 --- Il contenuto e gli allegati di questo messaggio sono strettamente confidenziali, e ne sono vietati la diffusione, la riproduzione e l'uso non autorizzato. Il suo contenuto non costituisce impegno da parte della Società salvo accordo scritto tra quest'ultima ed il destinatario. Qualora il presente messaggio Le fosse pervenuto per errore, La preghiamo di comunicare immediatamente al mittente l'errata ricezione e di distruggere quanto ricevuto (compresi i file allegati) senza farne copia. Qualsivoglia utilizzo non autorizzato del contenuto di questo messaggio costituisce violazione dell'obbligo di non rivelare il contenuto della corrispondenza tra altri soggetti, salvo più grave illecito, ed espone il responsabile alle relative conseguenze. This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Use a foreign key mapping to get data from the other table using Python and SQLAlchemy.
Az wrote:
[SNIP]
The following code maps these classes to respective database tables.
# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
customers_table = Table('customers', metadata,
Column('uid', Integer, primary_key=True),
Column('name', String),
Column('email', String)
)
orders_table = Table('orders', metadata,
Column('item_id', Integer, primary_key=True),
Column('item_name', String),
Column('customer', Integer, ForeignKey('customers.uid'))
)
metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Orders, orders_table)
Now if I do something like:
for order in session.query(Order):
print order
I can get a list of orders in this form:
Item ID 1001: MX4000 Laser Mouse, has been ordered by customer no.
12
=
What I want to do is find out customer 12's name and email address
(which is why I used the ForeignKey into the Customer table). How
would I go about it?
=
You need to add a relationship between the two classes. This is
documented at
http://www.sqlalchemy.org/docs/ormtutorial.html#building-a-relationship.
The documentation is using the declarative form. If you want to continue
to use the distinct table definitions followed by mapper definitions, it
would look something like this:
mapper(Orders, orders_table, properties={
'customer_object': relationship(Customer, backref='orders')
})
This will add a 'customer_object' property to the Orders class which
returns the corresponding Customer object. The backref='orders'
parameter means that the Customer object will also get an 'orders'
property which will be a list of all orders owned by the Customer.
You might find it more convenient if your existing 'customer' column was
actually called something like 'customer_id', then you could call your
relationship property 'customer' instead. If you didn't want to rename
the actual column in the database, you can still ask SQLAlchemy to use a
different name for the column, as demonstrated in
http://www.sqlalchemy.org/docs/mappers.html#customizing-column-propertie
s
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] mysql vs sqlite for testing
Hi All, We currently run unit tests against sqlite in memory but deploy against MySQL. http://stackoverflow.com/questions/2716847/sqlalchemy-sqlite-for-testing-and-postgresql-for-development-how-to-port ...suggests this is a bad idea. I'm inclined to agree, but... ...running our unit tests against MySQL is an order of magnitude or so slower than against sqlite. This is probably due to the tables being dropped and created for each and ever test. How do people get around this? What's best practice in this area? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Python sqlanydb module (Sybase ASA) as a driver for sqlalchemy?
Hi, For those of us locked into commercial databases, how hard is it to add new engine to alchemy? I ask because Sybase has released an DBAPI 2.0 spec native driver for their SQL Anywhere product versions 10 and 11, also referred to as Sybase ASA. If the python driver is solid, do the differences come down to SQL dialects intricacies, etc? I know the Sybase SQL fairly well and would be more than happy to contribute to this endeavor with a little guidance. Thank you! Aaron -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: Roundin' up the children!
On May 26, 2010, at 8:01 PM, ObjectEvolution wrote:
Thanks for the input Michael. I think the polymorphism is messing
things up...just a hunch. Your suggestion didn't work but this ended
up working:
'children': relation(Category,
primaryjoin=and_(TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
TABLES.BASE.c.deleted==False),
backref=backref('parent',
remote_side=(TABLES.CATEGORY.c.id)),
),
Does that make sense? I was excited that it worked at first but then I
wasn't quite sure how it worked. How does it recognize TABLES.BASE?
if the mapper is against the join of base-category, then both tables are
present in the FROM clause using a join.
I'm thinking that it might be best to have a mapping instead of what
we have now given our polymorphism. Thoughts on that?
not sure what this means. I was going to also suggest having a second mapper
against Category that filters out the deleted rows, is that what you mean ?
On May 26, 3:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 26, 2010, at 4:56 PM, ObjectEvolution wrote:
Hi,
I've got the following tables in my app (only showing applicable
columns here) storing categories for my app:
Base
- id (int) PK
- deleted (int) - 0/1 as a value
Category
- id (int) PK/FK - refers to Base.id
- parent_id (int) FK - self-referential to Category.id
I then have a Category object, which inherits from Base. All's good.
What I'm trying to do is when I get my Category object I only get
children which aren't deleted=1. My original property in my mapper was
this:
'children': relation(Category,
primaryjoin=TABLES.CATEGORY.c.id==
TABLES.CATEGORY.c.parent_id,
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
Which works fine but gets everything. So I changed it to this:
'children': relation(Category, secondary=TABLES.BASE,
primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
TABLES.BASE.c.deleted==False),
foreign_keys=[TABLES.CATEGORY.c.id],
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
the way secondary works is:
parent - primaryjoin- secondary - secondaryjoin - child
So primaryjoin has to be in terms of category and base, as does
secondaryjoin. Also you don't use remote_side with secondary.
foreign_keys are also usually implicit from your Table metadata and its
rare these are needed (unless an error message asks for them, which often
indicates something else is the actual issue).
what you have here really does not appear to be a many-to-many relationship,
its one-to-many/many-to-one. So if you want the relationship to add a
where criterion for the base.deleted, you likely just want to use and_()
all within the primaryjoin.and_(category.id==category.parent_id ,
base.deleted==False, base.id==category.id).
and I get nothing. Not a single object. Is my issue:
1. Foreign key related?
2. Join related?
3. Developer related?
Any help here is appreciated.
Thanks!
Jon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://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 sqlalch...@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 sqlalch...@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] mysql vs sqlite for testing
On May 27, 2010, at 10:00 AM, Chris Withers wrote: Hi All, We currently run unit tests against sqlite in memory but deploy against MySQL. http://stackoverflow.com/questions/2716847/sqlalchemy-sqlite-for-testing-and-postgresql-for-development-how-to-port ...suggests this is a bad idea. I'm inclined to agree, but... ...running our unit tests against MySQL is an order of magnitude or so slower than against sqlite. This is probably due to the tables being dropped and created for each and ever test. How do people get around this? What's best practice in this area? your test suite ideally wouldn't be tearing down and building up tables many times.For an application where the testing is against a fixed set of tables (i.e. not at all like SQLA's own unit tests), you would run all your tests in transactions that get rolled back when the test is complete. I use setup/teardowns like this for this purpose (assume scoped_session, which yes you should probably use all the time so that the session is accessed by a single reference): def setup_for_rollback(): Session.remove() sess = Session() c = sess.bind.connect() global transaction transaction = c.begin() sess.bind = c def teardown_for_rollback(): transaction.rollback() Session.remove() above, transaction is the real transaction. All begin/commits inside don't actually commit anything. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Python sqlanydb module (Sybase ASA) as a driver for sqlalchemy?
theres a driver for adaptive server anywhere 9 in SQLA 0.5. in 0.6, we removed this driver since it hasn't been tested for a few years and replaced with one that is tested against Sybase ASE (which is the more important Sybase we'd like to support). So in this case, assuming you want to go against 0.6, you'd probably take the 0.6 version, and modify it accordingly using hints from the 0.5 version. I don't get the impression that ASA and ASE are compatible with each other (though if thats not the case, then this whole thing becomes that much easier/better...the DBAPI situation for ASE is not terrific). Also you can start your dialect as an outside library, see http://stackoverflow.com/questions/1674841/how-do-i-write-an-external-dialect-for-sqlalchemy for info on how to make it into a plugin. On May 27, 2010, at 1:16 AM, abostick wrote: Hi, For those of us locked into commercial databases, how hard is it to add new engine to alchemy? I ask because Sybase has released an DBAPI 2.0 spec native driver for their SQL Anywhere product versions 10 and 11, also referred to as Sybase ASA. If the python driver is solid, do the differences come down to SQL dialects intricacies, etc? I know the Sybase SQL fairly well and would be more than happy to contribute to this endeavor with a little guidance. Thank you! Aaron -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] SA-Lockmode-oracle
On May 26, 2010, at 9:16 AM, dhanil anupurath wrote: DatabaseError: (DatabaseError) ORA-00907: missing right parenthesis OK, the syntax error here is fixed in ra84fef18507e . But the bad news is Oracle really can't handle FOR UPDATE with an ORDER BY or with double-nested subqueries in any case, you now get http://ora-02014.ora-code.com/ so its essentially a moot issue. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Acting on creation of model instances
On May 26, 2:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: its in the identity map after the flush succeeds, which is well before after_commit() is called. That sounds reasonable, but I have debug output from after_attach, before_flush, after_flush, before_commit and after_commit, all of which show a session.identity_map without the newly added Story instance. In fact, the only time I see a Story is as the 'instance' argument to after_attach. But it's still doing the INSERT into the stories table. Here's my debug output: http://pastie.textmate.org/private/ixa4sjopmf8nowblec1ra Why would I be seeing that behaviour? Is it indicative of me having set something up wrongly elsewhere? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: Acting on creation of model instances
On May 27, 2010, at 11:55 AM, Dan Ellis wrote: On May 26, 2:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: its in the identity map after the flush succeeds, which is well before after_commit() is called. That sounds reasonable, but I have debug output from after_attach, before_flush, after_flush, before_commit and after_commit, all of which show a session.identity_map without the newly added Story instance. In fact, the only time I see a Story is as the 'instance' argument to after_attach. But it's still doing the INSERT into the stories table. Here's my debug output: http://pastie.textmate.org/private/ixa4sjopmf8nowblec1ra Why would I be seeing that behaviour? Is it indicative of me having set something up wrongly elsewhere? if you can create a small test case for me illustrating a Foo object being added to the session, being flushed, and the after_commit() hook showing that it is not present in the identity map, I can work from that to explain why its happening. One thought is that the Session is weak referencing - if your Story (here Foo) object has lost all references, it will be garbage collected once flushed. So it depends on how you're checking for it. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Roundin' up the children!
Ah, I see now, that makes sense.
I did mean a second mapper but when I wrote that I didn't realize I
could actually do that. After reading the docs, I know now ;-)
Thanks again for your help Michaelgreat stuff you've got here.
Cheers,
Jon
On May 27, 7:51 am, Michael Bayer mike...@zzzcomputing.com wrote:
On May 26, 2010, at 8:01 PM, ObjectEvolution wrote:
Thanks for the input Michael. I think the polymorphism is messing
things up...just a hunch. Your suggestion didn't work but this ended
up working:
'children': relation(Category,
primaryjoin=and_(TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
TABLES.BASE.c.deleted==False),
backref=backref('parent',
remote_side=(TABLES.CATEGORY.c.id)),
),
Does that make sense? I was excited that it worked at first but then I
wasn't quite sure how it worked. How does it recognize TABLES.BASE?
if the mapper is against the join of base-category, then both tables are
present in the FROM clause using a join.
I'm thinking that it might be best to have a mapping instead of what
we have now given our polymorphism. Thoughts on that?
not sure what this means. I was going to also suggest having a second mapper
against Category that filters out the deleted rows, is that what you mean ?
On May 26, 3:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 26, 2010, at 4:56 PM, ObjectEvolution wrote:
Hi,
I've got the following tables in my app (only showing applicable
columns here) storing categories for my app:
Base
- id (int) PK
- deleted (int) - 0/1 as a value
Category
- id (int) PK/FK - refers to Base.id
- parent_id (int) FK - self-referential to Category.id
I then have a Category object, which inherits from Base. All's good.
What I'm trying to do is when I get my Category object I only get
children which aren't deleted=1. My original property in my mapper was
this:
'children': relation(Category,
primaryjoin=TABLES.CATEGORY.c.id==
TABLES.CATEGORY.c.parent_id,
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
Which works fine but gets everything. So I changed it to this:
'children': relation(Category, secondary=TABLES.BASE,
primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
TABLES.BASE.c.deleted==False),
foreign_keys=[TABLES.CATEGORY.c.id],
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
the way secondary works is:
parent - primaryjoin- secondary - secondaryjoin - child
So primaryjoin has to be in terms of category and base, as does
secondaryjoin. Also you don't use remote_side with secondary.
foreign_keys are also usually implicit from your Table metadata and its
rare these are needed (unless an error message asks for them, which often
indicates something else is the actual issue).
what you have here really does not appear to be a many-to-many
relationship, its one-to-many/many-to-one. So if you want the
relationship to add a where criterion for the base.deleted, you likely
just want to use and_() all within the primaryjoin.
and_(category.id==category.parent_id , base.deleted==False,
base.id==category.id).
and I get nothing. Not a single object. Is my issue:
1. Foreign key related?
2. Join related?
3. Developer related?
Any help here is appreciated.
Thanks!
Jon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://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 sqlalch...@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 each begin_nested() call, a corresponding rollback() or commit() must be issued.
The docs state For each begin_nested() call, a corresponding rollback() or commit() must be issued. In PostgreSql, according to my understanding, if there is ever a database exception, a rollback must be issued. This means a main reason to issue a SAVEPOINT is as a hedge against an error. As database transactions go, I want this entire thing to be a single transaction, so now I don't know how to continue... For example, DBSession.begin_nested() #savepoint DBSession.add(obj) try: DBSession.flush() except IntegrityError: DBSession.rollback() else: # now what? I do not want to commit, i have much # more work to do than this which should be part of # this transaction, but if I don't commit now, # i need to issue 2 commits later()?? Is releasing the savepoint a choice instead of rolling() or commit()? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] For each begin_nested() call, a corresponding rollback() or commit() must be issued.
On May 27, 2010, at 5:12 PM, Kent wrote: The docs state For each begin_nested() call, a corresponding rollback() or commit() must be issued. In PostgreSql, according to my understanding, if there is ever a database exception, a rollback must be issued. This means a main reason to issue a SAVEPOINT is as a hedge against an error. As database transactions go, I want this entire thing to be a single transaction, so now I don't know how to continue... For example, DBSession.begin_nested() #savepoint DBSession.add(obj) try: DBSession.flush() except IntegrityError: DBSession.rollback() else: # now what? I do not want to commit, i have much # more work to do than this which should be part of # this transaction, but if I don't commit now, # i need to issue 2 commits later()?? Is releasing the savepoint a choice instead of rolling() or commit()? commit() releases the savepoint, if thats whats going on contextually. It doesnt actually commit the outer transaction if you've last called begin_nested(). your block should be like: session.begin_nested() try: ... session.flush() session.commit() except: session.rollback() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] For each begin_nested() call, a corresponding rollback() or commit() must be issued.
Thank you, as always. I failed to recognize I'm using the TurboGears foundation, which uses zope transaction: Is there a way to still accomplish this? DBSession.begin_nested() sqlalchemy.orm.session.SessionTransaction object at 0xe9d5150 DBSession.commit() Traceback (most recent call last): File console, line 1, in module File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py, line 129, in do return getattr(self.registry(), name)(*args, **kwargs) File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 655, in commit self.transaction.commit() File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 368, in commit self._prepare_impl() File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 344, in _prepare_impl ext.before_commit(self.session) File /home/rarch/tg2env/lib/python2.6/site-packages/zope.sqlalchemy-0.4-py2.6.egg/zope/sqlalchemy/datamanager.py, line 201, in before_commit assert zope_transaction.get().status == 'Committing', Transaction must be committed using the transaction manager AssertionError: Transaction must be committed using the transaction manager On 5/27/2010 6:39 PM, Michael Bayer wrote: On May 27, 2010, at 5:12 PM, Kent wrote: The docs state For each begin_nested() call, a corresponding rollback() or commit() must be issued. In PostgreSql, according to my understanding, if there is ever a database exception, a rollback must be issued. This means a main reason to issue a SAVEPOINT is as a hedge against an error. As database transactions go, I want this entire thing to be a single transaction, so now I don't know how to continue... For example, DBSession.begin_nested() #savepoint DBSession.add(obj) try: DBSession.flush() except IntegrityError: DBSession.rollback() else: # now what? I do not want to commit, i have much # more work to do than this which should be part of # this transaction, but if I don't commit now, # i need to issue 2 commits later()?? Is releasing the savepoint a choice instead of rolling() or commit()? commit() releases the savepoint, if thats whats going on contextually. It doesnt actually commit the outer transaction if you've last called begin_nested(). your block should be like: session.begin_nested() try: ... session.flush() session.commit() except: session.rollback() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] For each begin_nested() call, a corresponding rollback() or commit() must be issued.
heh well thats the TG thing.I've never used it before or studied it, but I the impression I usually get when others talk about it is that their model of autocommit is a bit off. On May 27, 2010, at 6:50 PM, Kent Bower wrote: Thank you, as always. I failed to recognize I'm using the TurboGears foundation, which uses zope transaction: Is there a way to still accomplish this? DBSession.begin_nested() sqlalchemy.orm.session.SessionTransaction object at 0xe9d5150 DBSession.commit() Traceback (most recent call last): File console, line 1, in module File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py, line 129, in do return getattr(self.registry(), name)(*args, **kwargs) File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 655, in commit self.transaction.commit() File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 368, in commit self._prepare_impl() File /home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.0.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 344, in _prepare_impl ext.before_commit(self.session) File /home/rarch/tg2env/lib/python2.6/site-packages/zope.sqlalchemy-0.4-py2.6.egg/zope/sqlalchemy/datamanager.py, line 201, in before_commit assert zope_transaction.get().status == 'Committing', Transaction must be committed using the transaction manager AssertionError: Transaction must be committed using the transaction manager On 5/27/2010 6:39 PM, Michael Bayer wrote: On May 27, 2010, at 5:12 PM, Kent wrote: The docs state For each begin_nested() call, a corresponding rollback() or commit() must be issued. In PostgreSql, according to my understanding, if there is ever a database exception, a rollback must be issued. This means a main reason to issue a SAVEPOINT is as a hedge against an error. As database transactions go, I want this entire thing to be a single transaction, so now I don't know how to continue... For example, DBSession.begin_nested() #savepoint DBSession.add(obj) try: DBSession.flush() except IntegrityError: DBSession.rollback() else: # now what? I do not want to commit, i have much # more work to do than this which should be part of # this transaction, but if I don't commit now, # i need to issue 2 commits later()?? Is releasing the savepoint a choice instead of rolling() or commit()? commit() releases the savepoint, if thats whats going on contextually. It doesnt actually commit the outer transaction if you've last called begin_nested(). your block should be like: session.begin_nested() try: ... session.flush() session.commit() except: session.rollback() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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: Acting on creation of model instances
Thanks for your help. I'm not exactly sure what happened, so I'll have to carefully look over this area again, but part of it was definitely to do with needing the distinct session. Here's the minimal example I extracted: http://pastie.textmate.org/private/lpgkq7gkaypmgkphknr2w Frustratingly, the identity map problem disappeared when I created the minimal example, and after getting the distinct session stuff to work and putting that back in the original code, all seems well. A question, though: on line 11, should I be replacing self.new or appending to it? That is, when the final flush is done by commit(), does session.new still contain everything that was newly added, regardless of manual flush()es that happened before, or does session.new only contain unflushed items? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
