Re: [sqlalchemy] StaleDataError/ObjectDeletedError
Thanks, this helps some to narrow it down. Trying to zoom in: - why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before? - when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data? - if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? - Is there (in principle) a problem with: + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed) + setting an attribute of that object with another object that was queried from the database + committing the first object to the database? Cheers, Lars On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote: On Jan 31, 2014, at 8:11 PM, lars van gemerden la...@rational-it.comjavascript: wrote: this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing. Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case). - could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)? sure - this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible? absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is relevant here -- 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] StaleDataError/ObjectDeletedError
Oh, on more question: might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above? CL On Saturday, February 1, 2014 1:25:27 PM UTC+1, lars van gemerden wrote: Thanks, this helps some to narrow it down. Trying to zoom in: - why would sqla try to UPDATE (instead of INSERT) a row in the database, when the row/object was never committed before? - when you flush an object to the database and then close the session that flushed (no commit), what happens to the flushed data? - if an object is in a session and it has_identity, why would accessing obj.id (id is the primary key) fail (see above)? - Is there (in principle) a problem with: + having an object of a mapped class which was never committed (but maybe was added to a session and flushed, after which the session was closed) + setting an attribute of that object with another object that was queried from the database + committing the first object to the database? Cheers, Lars On Saturday, February 1, 2014 3:42:09 AM UTC+1, Michael Bayer wrote: On Jan 31, 2014, at 8:11 PM, lars van gemerden la...@rational-it.com wrote: this means an object was meant to be UPDATEed via the ORM, however the row which is the target of the UPDATE is missing. Either the primary key of this row changed somehow, or the row was deleted, *or* the row is not visible to your transaction (this seems to be your case). - could the error also occur when the object was never committed to the database (which seems to be the case; the commit where the error occurs should be the first time the Company object is committed to the database)? sure - this seems to suggest that it is possible that a row is in the database, but that it is not visible to a transaction; is that possible? absolutely, the link http://en.wikipedia.org/wiki/Transaction_isolation is relevant here -- 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] group_by and lazy=False relationship problem
On 01/31/2014 10:56 PM, Michael Bayer wrote:
On Jan 31, 2014, at 2:49 PM, Robert Tasarz robert.tas...@gmail.com wrote:
Hi,
I'm almost sure this is a bug, but maybe I'm missing something obvious.
I've tested it with Python 3.3, SQLAlchemy 0.9.1, PostgreSQL 9.3 and
reduced the issue to the following code:
Here's most relevant part of the exception:
Traceback (most recent call last):
File
/srv/websites/sika/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py,
line 867, in _execute_context
context)
File
/srv/websites/sika/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py,
line 388, in do_execute
cursor.execute(statement, parameters)
psycopg2.ProgrammingError: column a_1.id must appear in the GROUP BY
clause or be used in an aggregate function
LINE 1: SELECT p.id AS p_id, sum(i.value) AS sum1, a_1.id AS a_1_id,…
When joined eager loading is used, if LIMIT is also applied as you have in
that [:10], SQLAlchemy wraps the query to be limited inside of a subquery, so
that the LEFT OUTER JOIN for the eager loading can safely load all related
rows without being subject to the LIMIT. This wrapping doesn’t occur when
GROUP BY is used - GROUP BY is not usually used in conjunction with loads of
a full entity, as this is typically inefficient - it is usually used with a
query that is only loading individual columns, and then if the query overall
is to return entities, a JOIN against the GROUP BY as a subquery is used (see
http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx for a
description of this).
Yeah, I understand the process behind, as despite using SQLAlchemy
for quite some time already, I'm still mostly thinking in SQL
first. This time I've been building query in interactive shell and
in such cases I'm usually using something like [:4] to roughly see
if a query that SA produces does what I'm expecting. So it surprised
me that when I've finally got what I needed calling .all() on it
thrown an exception. That's why I've started digging through the
internet, but found nothing relevant, so posted here.
Regarding article you've linked. That's all nice and true, but when
I'm using ORM I'm expecting some performance penalty sometimes in
exchange for short and clean syntax abstracting away inner details.
This time for my particular case it is around 30%-40% which gives
~60ms longer execution time, so I can live with it. Now let's
compare code snippets based on my example classes from the first
post (assuming no eager load and no exception thrown).
Original:
sumq = func.sum(I.value).label('sum1')
result = sess.query(P,
sumq).outerjoin(P.items).group_by(P).order_by(sumq).all()
Optimized:
subq = db.query(I._elem,
func.sum(I.value).label('sum1')).group_by(I._elem).subquery()
result = sess.query(P,
subq.c.sum1).outerjoin(subq).order_by(subq.c.sum1).all()
As you can see the second case is quite noticeably longer and more
complicated, moreover it is leaking abstraction as it needs to use
I._elem which is an implementation detail how relations are built in
SQL (can it be rewritten without it?). And it is after all really
simple query. In comparison, the two queries from linked article are
similar in size and the second (preferred) one is IMO more clear
what developer wants to achieve - of course mostly because plain SQL
is much more verbose in the first place. And I'm writing it to show
that IMO, unlike in SQL, in SA first use case has some advantages in
not performance critical scenarios.
So in that sense, you’re getting that subquery behavior out of the box due to
the LIMIT, but with plain all(), this isn’t applied. you can call
from_self() to produce the self-wrapping effect:
sess.query(P,sumq).outerjoin(P.items).group_by(P).order_by(sumq).from_self().all()
Thanks, thats the method I've been looking for :). I even thought
about something like
sess.query(sess.query(P,)[...].subquery()).all() but thought
it's too ugly and certainly something cleaner should be possible.
Don't know how I've missed .from_self() when scanning through all
methods of the query object for the dozen time.
perhaps the presence of GROUP BY should be added to the list of things that
cause the automatic wrapping with joined eager loading to occur, though the
current behavior has been this way for nearly 8 years and nobody’s asked for
it before.
I guess it should, after all a bug that unveils itself once every
eight years is still a bug ;). Should I file a bug report?
regards,
Robert Tasarz
--
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] StaleDataError/ObjectDeletedError
Ok, Michael, That helped a lot, what i have done is (for future reference/others); 1) turned of autoflush on all sessions, 2) shortened the lifespan of session to a minimum, 3) removed the if object_session(obj): session.merge(obj) option in the Session() function, This seems to have solved the problem for now (needs more testing)! Thanks for the link to the talk as well. Cheerio, Lars On Saturday, February 1, 2014 4:34:10 PM UTC+1, Michael Bayer wrote: On Feb 1, 2014, at 9:01 AM, lars van gemerden la...@rational-it.comjavascript: wrote: Oh, on more question: might there be anything inherently wrong with the scoped_session approach that i showed in the code snippets above? the code which illustrates the @contextmanager and the “def Session()” looks error-prone and entirely awkward, and the rationale for such a context manager isn’t apparent. It appears to be mixing the intent of wishing to share random rows between multiple sessions (a bad idea) while at the same time trying to conceal the details of how a delicate operation like that is performed (it guesses whether add() or merge() should be used, etc). It also seems to mix the concerns of dealing with object mechanics and session creation at the same time which are typically two different concerns, not to mention that it has a complex system of committing or not committing using flags which makes it unsurprising that you’re seeing non-existent rows show up in other transactions. So yeah, if it were me, I’d definitely try to approach whatever the problem is it’s trying to solve in a different way, one which preferably sticks to the patterns outlined in the ORM tutorial as much as possible (e.g. one session at a time, load/manipulate objects, commit(), throw everything away). Those points at which an application actually uses two sessions at once, or transfers objects between them, should be very isolated cases with very explicit mechanics and clear rationale why this operation is needed in this specific case (where typical cases are: sending objects into worker threads or processes, moving objects in and out of caching layers, or running two transactions simultaneously so that one can commit and the other roll back, such as a transaction writing to an application history table).The app wouldn’t have a generic “here’s one of those places we need to use two sessions with a specific target object to pull out of one of them” use case such that a context manager is needed, there should be extremely few places where that kind of thing goes on. The kind of issue you’re hitting is exactly the one I talk about in detail in my talk, “The SQLAlchemy Session in Depth”: http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/ Page 18 of the PDF illustrates a slide “Lack of Behavioral Constraints Creates Confusion” illustrating an anti-pattern similar to the one I think we’re seeing here. -- 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] Using SQ with Django models
Hello, I have a database in PostgreSQL that is built and updated using Django 1.2 and I'd like to access it with SA. Are there any quick pointers you could offer to get me started on the best way to do that (e.g. duplicate Django's models.py in SA, use reflection, etc.)? The tables have a *lot* of fields. Thanks, Michael -- 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] Automap and naming of relationship attributes
On Feb 1, 2014, at 11:50 AM, Adrian Robert adrian.b.rob...@gmail.com wrote: Hi, I'm new to sqlalchemy though I've used other ORMs (e.g. Hibernate) before, and I'm trying to use the new automap feature. However it seems to be using the foreign table name rather than a suffixed version of the column name when naming relationships. Is there a reason for doing it this way? this is only a default. All the naming schemes are configurable as documented at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#overriding-naming-schemes . In this case you’d be doing the name_for_scalar_relationship callable: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#sqlalchemy.ext.automap.name_for_scalar_relationship If the naming needs to be done this way for general consistency with how things are done elsewhere in sqlalchemy, SQLAlchemy avoids automatic naming schemes like the plague, and there are very few places they are present. This extension is clearly one of them as it is necessary, but it’s entirely open ended. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Re: Using SQ with Django models
Check out this project: https://pypi.python.org/pypi/sqlacodegen it's a replacement to sqlautocode the packages are designed to inspect your database and create sqlalchemy models ( in python code) for you. you'll have to edit/audit the generated code -- but it will save A LOT of time. -- 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] from_statement, TextAsFrom and stored procedures
Hi SQLAlchemy,
Our data team wants us to use a (SQL Server 2008 R2) stored procedure to
perform our major query, which is all well and good, except it's preventing
SQLAlchemy's type processing from being applied. This is on SQLAlchemy
0.9.1, using pyodbc and FreeTDS.
For example, say we are trying to map this class (using Flask-SQLAlchemy):
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
random_time = db.Column(UTCDateTime)
def __init__(self, username, email):
self.username = username
self.email = email
self.random_time = datetime.now()
Using this (trivial) user-defined type:
class UTCDateTime(db.TypeDecorator):
impl = db.DateTime
def process_result_value(self, value, dialect):
print AWOGA
return value
Create the table and populate it with some values:
db.create_all()
db.session.add(User('alice', 'al...@gmail.com'))
db.session.add(User('bob', 'b...@gmail.com'))
db.session.commit()
users = db.session.query(User).all()
Two AWOOGAs are output, as expected.
Then, create a stored procedure like this:
CREATE PROCEDURE GetUser AS
SELECT
*
FROM user
GO
And query into User objects using the procedure:
db.session.add(User('charlie', 'char...@gmail.com'))
db.session.commit()
text = db.text('exec getuser')
users = db.session.query(User).from_statement(text).all()
The resulting User objects look reasonable, *but no AWGAs*, and the
strings are all bytestrings.
After looking at the docs more closely, this isn't very surprising: text()
does warn about a lack of type processing, and suggests using
text().columns() to provide a mapping (in lieu of the now-deprecated
typemap kwarg to text()). This creates a TextAsFrom object, which adds some
extra superpowers to text() including a .c attribute. Problem is,
from_statement() doesn't like it:
db.session.commit()
typemap = {'id': db.Integer, 'username': db.String, 'email': db.String,
'random_time': UTCDateTime}
taf = text.columns(**typemap)
users = db.session.query(User).from_statement(taf).all()
This results in a stack trace:
AttributeErrorTraceback (most recent call last)
ipython-input-20-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __iter__(self)
2386
2387 def __iter__(self):
- 2388 context = self._compile_context()
2389 context.statement.use_labels = True
2390 if self._autoflush and not self._populate_existing:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in _compile_context(self, labels)
2732
2733 def _compile_context(self, labels=True):
- 2734 context = QueryContext(self)
2735
2736 if context.statement is not None:
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __init__(self, query)
3478 if query._statement is not None:
3479 if isinstance(query._statement, expression.SelectBase)
and \
- 3480 not query._statement.use_labels:
3481 self.statement = query._statement.apply_labels()
3482 else:
AttributeError: 'TextAsFrom' object has no attribute 'use_labels'
Looks like TextAsFrom isn't quite select-like enough for from_statement().
I tried tacking on a taf.use_labels = True before running the query, but
just got another error:
NoSuchColumnError Traceback (most recent call last)
ipython-input-23-c694595d6ec1 in module()
1 users = db.session.query(User).from_statement(taf).all()
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2278
2279
- 2280 return list(self)
2281
2282 @_generative(_no_clauseelement_condition)
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in instances(query, cursor, context)
70 process[0](row, rows)
71 elif single_entity:
--- 72 rows = [process[0](row, None) for row in fetch]
73 else:
74 rows = [util.KeyedTuple([proc(row, None) for proc in
process],
/home/badmin/.virtualenvs/bridge-it/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.pyc
in _instance(row, result)
358 identitykey = (
359 identity_class,
-- 360 tuple([row[column]
