Re: [sqlalchemy] AssertionError When Removing Children For Association Object
you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections Can you help me understand why a cascade rule is needed? I have read the documentation you linked to but still have trouble grasping why it is needed. I understand it would make sense if I deleted a page, that it should cascade on delete to page_to_user because now the FK page_id is no longer valid - the page doesn't exist, so it cannot possibly be mapped anywhere. What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. The only thing being deleted is the mapping itself. In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. Maybe it's purely a SQLAlchemy thing and how it's designed for some reason? I would just like to understand better for future development. Thanks in advance. -Russ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
On Jan 8, 2014, at 8:55 PM, limodou limo...@gmail.com wrote: Yes, I saw the code about 0.8.X and 0.9.1, the None convertion are the same. But difference between them is in AND process. So this inconsistent that you mean it's a bug in 0.8? it’s a bug in 0.8, yes. I think raise exception maybe better, so that it'll let user to know what wrong with the condition. Otherwise some code like condition None can run in 0.8.X very well, but in 0.9 it'll only return nothing without any error thrown at all. It will break the old code. well we can’t change 0.8 to raise, so raising an exception in 0.9 wouldn’t solve much as far as cross-compatibility. I don’t think this pattern is that common in any case. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 9, 2014, at 10:02 AM, Russell Holloway russ.d.hollo...@gmail.com wrote: you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections Can you help me understand why a cascade rule is needed? I have read the documentation you linked to but still have trouble grasping why it is needed. I understand it would make sense if I deleted a page, that it should cascade on delete to page_to_user because now the FK page_id is no longer valid - the page doesn't exist, so it cannot possibly be mapped anywhere. OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. “delete-orphan” means when an item is removed from a collection, in this case a PageToUser object, it is marked as deleted. In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. OK you need to show what “works fine” is - how the Page.user_relationships collection can be empty on a particular Page object, yet there are PageToUser objects in the database which refer to that Page (or if you think the PageToUser row still exists, but doesn’t point to any Page, show me how that looks). What’s in the database? signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? I would expect it to delete entries, resulting in no rows for that page_id DELETE FROM Page_To_User WHERE page_id = ? Perhaps it tries this instead? UPDATE Page_To_User SET page_id = NULL WHERE page_id = ? What I'm having trouble understanding is what is cascading from what to what when deleting from this page.user_relationships collection. It doesn't cascade from page, because I am not deleting any pages, and it doesn't cascade from users, because no users are deleted. “delete-orphan” means when an item is removed from a collection, in this case a PageToUser object, it is marked as deleted. So by default does page.user_relationships call the above UPDATE call and set to null or something? In the database table definition, there doesn't have to be any cascade settings at all for the constraints and it will still work fine. OK you need to show what “works fine” is - how the Page.user_relationships collection can be empty on a particular Page object, yet there are PageToUser objects in the database which refer to that Page (or if you think the PageToUser row still exists, but doesn’t point to any Page, show me how that looks). What’s in the database? In my mind, Page.user_relationship is a collection of PageToUser objects, so by emptying that collection, all those objects are deleted from Page_To_User. It doesn't have anything to do with the Page object itself though - only the associations in PageToUser. The following table definitions work without the CASCADE being used on FK at all (it may prevent deletions of Page or User rows due to FK constraints, but not in this example since we don't ever delete those rows). CREATE TABLE Page ( int page_id not null auto increment, varchar(255) title, primary key (page_id) ) Engine=InnoDB; CREATE TABLE User ( int user_id not null auto increment, varchar(255) name, primary key (user_id) ) Engine=InnoDB; CREATE TABLE Page_To_User ( int page_id not null, int user_id not null, int relationship_id not null, primary key (page_id, user_id, relationship_id), foreign key (page_id) references Page(page_id), foreign key (user_id) references User(user_id) ) Engine=InnoDB; Above, there isn't any ON DELETE CASCADE specified for any of the foreign keys. My understanding is if there were, foreign key (page_id) references Page(page_id) ON DELETE CASCADE then, if I delete row with that page_id from Page, then it will cascade from Page to Page_To_User and delete the appropriate row. If ON DELETE CASCADE is missing, it will complain if I try to delete that Page row, because there are FK constraints. But issuing a simple ' DELETE FROM Page_To_User WHERE page_id = ? ' shouldn't require any cascading, at least in SQL. Maybe the cascade keyword in SQLAlchemy does not exactly mean cascade on a table definition? -Russ -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Multiple inheritance issue
Hello guys,
I'm building a web service to provide data to our SDK and I'm facing a
wired problem. I have created 4 different models for my needs, a base class
that is called *Unit*, two derivatives from the base class *VideoUnit*,
*QuestionUnit*, and the last class is a derivative of both *VideoUnit* and
*QuestionUnit*, that is called, with lot of fantasy, *VideoQuestionUnit*.
Everything was working fine till the point I was trying to serialize this
data to be transmitted with JSON.
I successfully gather JSON from *VideoUnit* and *QuestionUnit *(thanks to
plaeshttp://stackoverflow.com/questions/7102754/jsonify-a-sqlalchemy-result-set-in-flask)
but now I can't figure out why when I try to make the same thing with
*VideoQuestioUnit
*the program crash telling me: *KeyError: 'questions'*
I can imagine that there is something wrong when *VideoQuestionUnit* try to
access the *questions* data declared in *QuestionUnit*(also my dog get
that I know), but I don't understand what's wrong with that.
Did someone have an idea of what I'm doing wrong?
*CODE:*
class Unit(db.Model):
id = db.Column(db.Integer, unique = True, primary_key = True)
name = db.Column(db.String(128), index = True, unique = False)
type = db.Column(db.SmallInteger)
brand_id = db.Column(db.Integer, db.ForeignKey('brand.id'))
@property
def serialize(self):
print Serialize on superclass
pass
class VideoUnit(Unit):
uri = db.Column(db.String(256), index = False, unique = False)
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri
}
class QuestionUnit(Unit):
questions = db.relationship('Question', backref = 'QuestionUnit', lazy
= 'dynamic')
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
class VideoQuestionUnit(VideoUnit, QuestionUnit):
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
Thanks in advance,
Enrico
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Anybody have twophase/zope.sqlalchemy/MySQL working?
Thank you for the reply! Sorry for the delayed response. (Holidays.)
On Mon, Dec 23, 2013 at 11:52:25PM -0800, Laurence Rowe wrote:
On Thursday, 12 December 2013 16:30:59 UTC-8, Jeff Dairiki wrote:
Do you understand why the datamanager is finding the SessionTransaction
and using that directly? (At least I think that's what it's doing ---
I haven't sussed this out completely.) I'm referring to the line from
SessionDataManager.__init__:
self.tx = session.transaction._iterate_parents()[-1]
This is to handle the case of a session being a nested transaction at the
time the ``zope.sqlalchemy`` datamanager joins the ``transaction``
transaction
and then later the session manager calls .prepare(), .commit()
and/or .rollback() on self.tx, *if* self.tx is not None.
The thing is, for me, if the session has only been used for read
operation,
self.tx seems to be None. So the datamanager never commits anything.
I don't understand (yet) why the data manager doesn't just call
.prepare() and .commit() directly on the sqlalchemy session instance.
The zope.sqlalchemy datamanager will rollback the transaction when it
detects that no work is done. In that case self.tx is set to None during
SessionTransaction.commit and during the two phase commit there is nothing
to do.
The sequence in which the datamanager methods are called is found in
Transaction._commitResources:
https://github.com/zopefoundation/transaction/blob/1.4.1/transaction/_transaction.py#L382
Dunno. It doesn't seem like it should be MySQL specific thing, but maybe
postgres and others are more forgiving of a two-phase XA BEGIN being
terminated by a regular one-phase ROLLBACK?
Anyhow, I'll keep poking when I find a moment.
(And from a later message in this thread)
Okay, so this was not a complete solution. It does cause the
datamanager to commit the the sessions when the transaction is terminated
by transaction.commit(), but neither setting the
initial state to STATUS_CHANGED, nor calling mark_changed() is enough
to get the datamanager to rollback the session if the transaction
is ended with transaction.abort().
Looking at the datamanager logic again, I don't think self.tx can ever be
None in abort() (at least not normally) but closing the session will close
the underlying transaction on the connection, which issues the rollback:
https://github.com/zzzeek/sqlalchemy/blob/rel_0_9_0b1/lib/sqlalchemy/engine/base.py#L1151
Okay, I've traced things out a bit more.
If the session state is not STATUS_INVALIDATED (aka STATUS_CHANGED),
SessionDataManager.commit() does a self._finish('no work'). That is
where self.tx gets set to None (this --- correctly --- then
causes .tpc_vote() and .tpc_finish() to be no-ops.)
So here's the crux of the biscuit: in two-phase-commit mode (at least
with MySQL) the sqlalchemy session (or session transaction) must be either
committed or explicitly rolled back before it is closed.
SessionDataManager.commit() does not do a rollback.
Example code:
import sqlalchemy as sa
engine = sa.create_engine('mysql://guest@furry/test',
echo='debug',
echo_pool='debug')
Sess = sa.orm.sessionmaker(bind=engine, twophase=True)
sess = Sess()
sess.query(sa.null()).scalar()
#sess.rollback()
sess.close()
Edited log output:
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 checked out from pool
INFO:sqlalchemy.engine.base.Engine:BEGIN TWOPHASE (implicit)
INFO:sqlalchemy.engine.base.Engine:XA BEGIN %s
INFO:sqlalchemy.engine.base.Engine:('_sa_a38de3b7234d5fe8ad55d2bb13ec195c',)
INFO:sqlalchemy.engine.base.Engine:SELECT NULL AS anon_1
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection _mysql.connection open to
'furry' at 29a3370 rollback-on-return
INFO:sqlalchemy.pool.QueuePool:Invalidate connection _mysql.connection
open to 'furry' at 29a3370 (reason: OperationalError:(1399, 'XAER_RMFAIL: The
command cannot be executed when global transaction is in the ACTIVE state'))
DEBUG:sqlalchemy.pool.QueuePool:Closing connection _mysql.connection open
to 'furry' at 29a3370
Here, no attempt is made to terminate the two-phase (XA) transaction
until the connection is returned to the pool, at which point a
plain one-phase 'ROLLBACK' is issued. MySQL does not like this,
thus the XAER_RMFAIL error.
Uncommenting the 'sess.rollback()' in the above example results in an
'XA END and 'XA ROLLBACK' being emitted before the connection is
returned to the pool, properly ending the two-phase (XA) transaction.
This eliminates the XAER_RMFAIL error, and results in proper recycling
of the pooled connection.
In zope.sqlalchemy.datamanger, if I change SessionDataManager.commit()
from
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 9, 2014, at 10:31 AM, Russell Holloway russ.d.hollo...@gmail.com wrote: OK so, you have: Page.user_relationships - collection of PageToUser PageToUser - single User then, you are saying : some_page.user_relationships = [] session.flush() What SQL would you expect this to produce? After a flush, what would the rows in your page_to_user table look like? I would expect it to delete entries, resulting in no rows for that page_id DELETE FROM Page_To_User WHERE page_id = ? Perhaps it tries this instead? UPDATE Page_To_User SET page_id = NULL WHERE page_id = ? You are correct in both cases.So if you follow the instructions for delete-orphan cascade as previously mentioned here: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade the rows will be deleted instead. Perhaps you’re being thrown off by the fact that an ORM level cascade is set on the *opposite* side as a FOREIGN KEY cascade in SQL, but that is how it works. The Page.user_relationships collection is what indicates the handling of PageToUser objects. If you want to see the origin of the “CASCADE” settings, they come from Hibernate: http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/example-parentchild.html . signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Multiple inheritance issue
On Jan 9, 2014, at 11:32 AM, Enrico Bottani bei...@mac.com wrote:
Hello guys,
I'm building a web service to provide data to our SDK and I'm facing a wired
problem. I have created 4 different models for my needs, a base class that is
called Unit, two derivatives from the base class VideoUnit, QuestionUnit, and
the last class is a derivative of both VideoUnit and QuestionUnit, that is
called, with lot of fantasy, VideoQuestionUnit.
Everything was working fine till the point I was trying to serialize this
data to be transmitted with JSON.
I successfully gather JSON from VideoUnit and QuestionUnit (thanks to plaes)
but now I can't figure out why when I try to make the same thing with
VideoQuestioUnit the program crash telling me: KeyError: 'questions'
I can imagine that there is something wrong when VideoQuestionUnit try to
access the questions data declared in QuestionUnit(also my dog get that I
know), but I don't understand what's wrong with that.
Did someone have an idea of what I'm doing wrong?
first of all, SQLAlchemy’s inheritance mapping system has no direct support for
multiple inheritance at all. True multi-table multiple inheritance in SQL is a
highly esoteric and complex pattern and I doubt SQLAlchemy is ever going to go
there, at least fully; just getting single hierarchy inheritance to work fully
has taken many years and was enormously complicated. While I see here that at
least VideoQuestionUnit doesn’t have a table of its own, still the fact that it
is subclassing two other joined-inh subclasses isn’t something the ORM has any
idea what to do with. Loading a VideoQuestionUnit means it would need to join
to both of the inherited tables, like a polymorphic load does, but there’s no
mechanism to produce one object deriving from more than one mapped class. I’m
surprised the mapping doesn’t just fail immediately.
secondly, well actually I’m not sure there’s a “secondly” here :). I guess
the way you can load and persist VideoQuestionUnit objects is by mapping it to
a JOIN of all three tables at once - and you’d also need to probably add
“concrete=True” to the mapping so that it doesn’t attempt to use any of the
superclass mappings for persistence or querying.Not something I’ve tried
though it might be workable.
CODE:
class Unit(db.Model):
id = db.Column(db.Integer, unique = True, primary_key = True)
name = db.Column(db.String(128), index = True, unique = False)
type = db.Column(db.SmallInteger)
brand_id = db.Column(db.Integer, db.ForeignKey('brand.id'))
@property
def serialize(self):
print Serialize on superclass
pass
class VideoUnit(Unit):
uri = db.Column(db.String(256), index = False, unique = False)
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri
}
class QuestionUnit(Unit):
questions = db.relationship('Question', backref = 'QuestionUnit', lazy =
'dynamic')
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
class VideoQuestionUnit(VideoUnit, QuestionUnit):
@property
def serialize(self):
return {
'ID': self.id,
'Name' : self.name,
'Link' : self.uri,
'Questions' : self.serialize_questions
}
@property
def serialize_questions(self):
return [item.serialize for item in self.questions]
Thanks in advance,
Enrico
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Integrity errors in foreign key with nullable=False in a large hierarchy
The following is a stripped down example of my app, that does NOT show the
problem:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
Base = declarative_base()
class Release(Base):
__tablename__ = 'releases'
id = Column(Integer, primary_key=True)
version = Column(String, nullable=False)
def __init__(self, version):
self.version = version
def __repr__(self):
return Release(version={}).format(self.version)
class URL(Base):
__tablename__ = 'urls'
id = Column(Integer, primary_key=True)
release_id = Column(Integer, ForeignKey('releases.id'), nullable=False)
url = Column(String, nullable=False)
release = relationship(Release,
cascade=all, delete-orphan,
single_parent=True,
backref=backref('urls', order_by=url))
def __repr__(self):
return URL(url={}).format(self.url)
def new_release(version, urls):
r = Release(version)
l = []
for url in urls:
u = URL()
u.url = url
l.append(u)
r.urls = l
return r
if __name__ == '__main__':
db = create_engine('sqlite://')
Base.metadata.create_all(db)
Session = sessionmaker(db)
s = Session()
s.merge(new_release('1.0', ['http://myapp.net/1.0']))
s.commit()
The real app is basically just a lot more complex (releases have 4 child
lists like urls, my example adds multiple objects in each list. The app
fails with an integrity error because a url has a null request_id. In
actual fact, the hierarchy all gets built up properly in the end, but it
appears that something is trying to save the children before all of the
links are set up.
If I remove nullable=False from the foreign keys, it works fine.
I think one of the error tracebacks (sorry, I lost them in other output)
mentioned autoflush - could the ORM be trying to flush bits of the
hierarchy before it's complete? Is there a better fix than removing the
nullable=False constraints?
Thanks for any help,
Paul
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Integrity errors in foreign key with nullable=False in a large hierarchy
On Jan 9, 2014, at 4:02 PM, Paul Moore p.f.mo...@gmail.com wrote: The real app is basically just a lot more complex (releases have 4 child lists like urls, my example adds multiple objects in each list. The app fails with an integrity error because a url has a null request_id. In actual fact, the hierarchy all gets built up properly in the end, but it appears that something is trying to save the children before all of the links are set up. If I remove nullable=False from the foreign keys, it works fine. I think one of the error tracebacks (sorry, I lost them in other output) mentioned autoflush - could the ORM be trying to flush bits of the hierarchy before it's complete? Is there a better fix than removing the nullable=False constraints? absolutely, which is why the “no_autoflush” contextmanager exists: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=no_autoflush#sqlalchemy.orm.session.Session.no_autoflush the stack trace you’re getting will give you clues to where this context manager should go. You’ll see the word “autoflush” in the stack trace, and from there you can trace up to see the specific operation in your app that is triggering it. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Integrity errors in foreign key with nullable=False in a large hierarchy
On Thursday, 9 January 2014 21:27:06 UTC, Michael Bayer wrote: I think one of the error tracebacks (sorry, I lost them in other output) mentioned autoflush - could the ORM be trying to flush bits of the hierarchy before it's complete? Is there a better fix than removing the nullable=False constraints? absolutely, which is why the “no_autoflush” contextmanager exists: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=no_autoflush#sqlalchemy.orm.session.Session.no_autoflush the stack trace you’re getting will give you clues to where this context manager should go. You’ll see the word “autoflush” in the stack trace, and from there you can trace up to see the specific operation in your app that is triggering it. Thanks. That's exactly what I wanted :-) Paul -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SQLAlchemy not executing query
Hi, I am using SQLAlchemy version 0.7.6 with pyodbc to connect to MSSQL 2012. Currently I am using SQLAlchemy only for its connection pooling etc. So, at the moment I only use the engine.execute function to execute string queries. Weirdly, the following query seems to have no effect at all: SET NOCOUNT ON; SET ROWCOUNT 1 WHILE 1 = 1 BEGIN DELETE from MyTable where MyDate = '2030' IF @@rowcount 1 BREAK; END SET ROWCOUNT 0; SET NOCOUNT OFF; Running the above query using pyodbc directly, works. But with SQLAlchemy it has no effect. There is no error thrown, just a silent failure to execute. I enabled ODBC tracing and I found this: python -c imp 85bc-7d4c EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO) HDBC0x0057B770 HWND0x WCHAR * 0x07FEF8CB8F08 [ -3] **\ 0 SWORD -3 WCHAR * 0x07FEF8CB8F08 Invalid buffer length! [-3] SWORD -3 SWORD * 0x UWORD0 SQL_DRIVER_NOPROMPT However, the above was also present when I ran the query through pyodbc only. So, this probably indicates a more general problem with the set up, rather than something particular with SQLAlchemy. Any idea what might be causing this? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Why ORM objects in a session expire immediately after a failure in flush?
Hi, folks, I observed that it seems all ORM objects in a session will expire immediately if there is a failure in session.flush(). I was wondering what's the rationale behind this behavior. Following is an artificial web application code to illustrate the idea: def PUT(): # session and engine are all created with default parameter user = session.query(UserProfile).first() # deliberately tries to insert a Login record which has the same primary key as an existing one such that the later session.flush() will fail try: duplicated_login_record = Login(id=0, username=username:weikai) session.add(duplicated_login_record) session.flush() except IntegrityError: session.rollback() print user.id ... By turning on logging, I noticed the print user.id line always result in a new SELECT being issued to database, which is an indicator that somehow user object got expired after the failure in session.flush(). For some other good reason which I don't want to elaborate here, we really don't want to have an extra SELECT going to DB in the processing of one HTTP request. Therefore, I was just wondering why is this behavior and whether it can be turned off. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy not executing query
The statement is likely being invoked but is in a transaction that isn’t getting committed (assuming you’re using commit() with pyodbc). SQLAlchemy has an “autocommit” feature that by default looks for SQL strings that indicate a COMMIT should occur. So in this case you should make sure there’s an explicit transaction or explicit autocommit. In 0.7 you’re probably best off like this: conn = engine.connect() trans = conn.begin() conn.execute(“your sql”) trans.commit() or use the “autocommit” option: conn = engine.connect() conn.execution_options(autocommit=True).execute(“your sql”) On Jan 9, 2014, at 6:39 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, I am using SQLAlchemy version 0.7.6 with pyodbc to connect to MSSQL 2012. Currently I am using SQLAlchemy only for its connection pooling etc. So, at the moment I only use the engine.execute function to execute string queries. Weirdly, the following query seems to have no effect at all: SET NOCOUNT ON; SET ROWCOUNT 1 WHILE 1 = 1 BEGIN DELETE from MyTable where MyDate = '2030' IF @@rowcount 1 BREAK; END SET ROWCOUNT 0; SET NOCOUNT OFF; Running the above query using pyodbc directly, works. But with SQLAlchemy it has no effect. There is no error thrown, just a silent failure to execute. I enabled ODBC tracing and I found this: python -c imp 85bc-7d4c EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO) HDBC0x0057B770 HWND0x WCHAR * 0x07FEF8CB8F08 [ -3] **\ 0 SWORD -3 WCHAR * 0x07FEF8CB8F08 Invalid buffer length! [-3] SWORD -3 SWORD * 0x UWORD0 SQL_DRIVER_NOPROMPT However, the above was also present when I ran the query through pyodbc only. So, this probably indicates a more general problem with the set up, rather than something particular with SQLAlchemy. Any idea what might be causing this? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Why ORM objects in a session expire immediately after a failure in flush?
On Jan 9, 2014, at 7:17 PM, Weikai Xie xiewei...@gmail.com wrote: Hi, folks, I observed that it seems all ORM objects in a session will expire immediately if there is a failure in session.flush(). I was wondering what's the rationale behind this behavior. Following is an artificial web application code to illustrate the idea: def PUT(): # session and engine are all created with default parameter user = session.query(UserProfile).first() # deliberately tries to insert a Login record which has the same primary key as an existing one such that the later session.flush() will fail try: duplicated_login_record = Login(id=0, username=username:weikai) session.add(duplicated_login_record) session.flush() except IntegrityError: session.rollback() print user.id ... By turning on logging, I noticed the print user.id line always result in a new SELECT being issued to database, which is an indicator that somehow user object got expired after the failure in session.flush(). For some other good reason which I don't want to elaborate here, we really don't want to have an extra SELECT going to DB in the processing of one HTTP request. Therefore, I was just wondering why is this behavior and whether it can be turned off. The short answer is that this behavior can’t be disabled across the board, unless you are using SAVEPOINTs, which you probably should here. The flush() process does a rollback() immediately on the inner transaction when an exception occurs, and once the transaction is rolled back all data is expired; as the objects are proxies for the state of the database, and the transaction has been rolled back, the state of the objects is invalid. I do notice that you’re catching an IntegrityError.The typical pattern when writing code that wants to catch these and then continue is to run the individual set of questionable operations within a SAVEPOINT, that is a begin_nested()/commit() block. Recent versions of SQLAlchemy have the behavior such that when a flush() exception rolls back the SAVEPOINT, objects that were not modified within the SAVEPOINT are not expired; only those objects that changed within the save point’s scope do. There’s also an option called _enable_transaction_accounting which if set to False will disable *all* rollback/commit accounting, basically reverts to pre-0.5 behavior. Using this flag, you’re giving up a lot of the Session’s functionality and as this mode is not really supported I’m not sure what other useful features you might be giving up. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
