[sqlalchemy] Re: Secondary tables and deleting
On Jun 7, 12:42 am, Michael Bayer mike...@zzzcomputing.com wrote: Below is a short test, can you figure out what you might be doing differently ? [snip] Michael, thanks very much. The database I'm looking at is my test database for the application I'm developing and it gets used and abused quite often and it's entirely possible that some idiot programmer (me) messed things up and if that's the case I apologise for wasting your time. However, I will adapt the code from your last post to my schema and see what happens. If, as I suspect, everything will work fine I'll do a drop all and recreate the database and keep an eye on it to see if this problem reoccurs, cheers. -- 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] mapping stored procedures results to an object
Hi All, Are there any example knocking about of mapping the results of a MySQL stored procedure to an object? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] .count()'ing with limiting query.
Hi everybody,
After switching from SQLAlchemy 0.6.5 to 0.7.0 we have observed a
problem with such an example code:
(copy at http://ideone.com/WB36Y)
==
# Fails with Python-2.7.1 and SQLAlchemy-0.7.0
#
import sqlalchemy
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class LimitingQuery(sqlalchemy.orm.query.Query):
def get(self, ident):
# override get() so that the flag is always checked in the
# DB as opposed to pulling from the identity map. - this is
optional.
return sqlalchemy.orm.query.Query.get(self.populate_existing(),
ident)
def __iter__(self):
return sqlalchemy.orm.query.Query.__iter__(self.private())
def private(self):
crit = (self._mapper_zero().class_.isDeleted == False)
return self.enable_assertions(False).filter(crit)
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo = False)
session = sqlalchemy.orm.scoped_session(
sqlalchemy.orm.sessionmaker(
bind = engine,
autocommit = False,
query_cls = LimitingQuery
)
)
Base.metadata.bind = engine
class Employee(Base):
__tablename__ = employees
Id = sqlalchemy.Column( sqlalchemy.types.Integer, primary_key=True,
autoincrement=True)
isDeleted = sqlalchemy.Column( sqlalchemy.types.Boolean(), default =
False)
name = sqlalchemy.Column(sqlalchemy.types.String(128))
managerId = sqlalchemy.Column(sqlalchemy.types.Integer,
sqlalchemy.ForeignKey(Id))
manager = sqlalchemy.orm.relation(
'Employee',
backref = sqlalchemy.orm.backref('subordinates', lazy =
'dynamic'),
primaryjoin = managerId == Id,
remote_side = Id
)
Base.metadata.create_all()
SteveBallmer = Employee(name = 'Steve Ballmer')
CraigMundie = Employee(name = 'Craig Mundie')
BillGates = Employee(name = 'Bill Gates')
session.add_all((SteveBallmer, CraigMundie, BillGates))
BillGates.subordinates = [SteveBallmer, CraigMundie]
# this line:
print BillGates.subordinates.count()
# raises:
# Traceback (most recent call last):
# File count_problem.py, line 60, in module
# print BillGates.subordinates.count()
# File
/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/dynamic.py,
line 251, in count
# return self._clone(sess).count()
# File
/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py,
line 2123, in count
# return self.from_self(col).scalar()
# File
/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py,
line 1775, in scalar
# ret = self.one()
# File
/home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py,
line 1744, in one
# ret = list(self)
# File count_problem.py, line 17, in __iter__
# return sqlalchemy.orm.query.Query.__iter__(self.private())
# File count_problem.py, line 20, in private
# crit = (self._mapper_zero().class_.isDeleted == False)
# AttributeError: 'NoneType' object has no attribute 'class_'
==
As far as I could debug it the problem starts when count() creates a new
query with the old one as a subquery (but I could very well be wrong).
A similar recipe can be found here:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery
Could you give me a hint about a proper way to do this?
regards,
Filip Zyzniewski
Tefnet
--
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: porting GeoAlchemy to 0.7
Here's the code I've come up with:
class GeometryDDL(object):
try:
from sqlalchemy import event
except ImportError:
# SQLAlchemy 0.6
use_event = False
columns_attribute = '_columns'
else:
# SQLALchemy 0.7
use_event = True
columns_attribute = 'columns'
def __init__(self, table):
if self.use_event:
event.listen(table, 'before_create', self.before_create)
event.listen(table, 'before_drop', self.before_drop)
event.listen(table, 'after_create', self.after_create)
event.listen(table, 'after_drop', self.after_drop)
else:
for e in ('before-create', 'after-create',
'before-drop', 'after-drop'):
table.ddl_listeners[e].append(self)
self._stack = []
def __call__(self, event, table, bind):
spatial_dialect = DialectManager.get_spatial_dialect(bind.dialect)
if event in ('before-create', 'before-drop'):
Remove geometry column from column list
(table._columns), so that it
does not show up in the create statement (create table tab (..)).
Afterwards (on event 'after-create') restore the column
list from self._stack.
regular_cols = [c for c in table.c if not
isinstance(c.type, Geometry)]
gis_cols = set(table.c).difference(regular_cols)
self._stack.append(table.c)
setattr(table, self.columns_attribute,
expression.ColumnCollection(*regular_cols))
if event == 'before-drop':
for c in gis_cols:
spatial_dialect.handle_ddl_before_drop(bind, table, c)
elif event == 'after-create':
setattr(table, self.columns_attribute, self._stack.pop())
for c in table.c:
if isinstance(c.type, Geometry):
spatial_dialect.handle_ddl_after_create(bind, table, c)
elif event == 'after-drop':
setattr(table, self.columns_attribute, self._stack.pop())
def before_create(self, target, connection, **kw):
self('before-create', target, connection)
def before_drop(self, target, connection, **kw):
self('before-drop', target, connection)
def after_create(self, target, connection, **kw):
self('after-create', target, connection)
def after_drop(self, target, connection, **kw):
self('after-drop', target, connection)
Thanks,
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.com
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mapping stored procedures results to an object
not that I'm aware of but its something I consider often. the closest right now is if the SP is callable as in func.foo(), you can map to func.foo(). http://www.sqlalchemy.org/trac/wiki/07Migration#Non-Table-derivedconstructscanbemapped but this considers the function to be like a view, which SPs generally are not. the big question on SPs is what does the usage pattern look like...Query() is a question here. I suppose if you did session.query(MyClass).params(x, y, z) and x, y, z go into func.foo(), there you go. It would probably work. On Jun 7, 2011, at 5:30 AM, Chris Withers wrote: Hi All, Are there any example knocking about of mapping the results of a MySQL stored procedure to an object? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Concurrent upsert problem with session.merge()
On Jun 7, 2011, at 7:17 AM, Vlad K. wrote: Hi all! I have a daily stats table with the date as primary key. Currently the date is string in the MMDD format, but I'll probably port it to a Date type for easier statistical analysis. Other cols are various counters that get incremented in various situations. My problem is that I need to write code that either updates if row with that date exists, or inserts as new. I know SQLAlchemy does that by itself (session.merge()), but the problem here is that you can't lock a row that does not exist yet, and with concurrency in mind, what will happen if two parallel processes happen to insert same date row at the same time. One solution would be to catch Integrity error and simply try again (with second attempt finding the pkey and thus updating instead of insert). But since I'm new to SQLAlchemy, I don't know if there is any better method. The DB backend is Postgres. This is a common PostgreSQL question which can be addressed with rules or triggers or just retrying the transaction, as you suggest. However, the easiest solution is to lock the table for the duration of the transaction which may be option to you if the transactions are short-lived and not performance-critical. Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] AttributeError (_inserted_primary_key) when retrieving ids of 'bulk insert'
On Jun 7, 2011, at 6:03 AM, Felix Wolfsteller wrote:
Hi,
I try to insert a big number of of rows to a table and want to receive the
ids
that were assigned to these (new) rows.
If i insert a single row, it works as expected, but with multiple rows I run
into an error:
AttributeError: 'DefaultExecutionContext' object has no
attribute '_inserted_primary_key' .
I use SQLAlchemy 0.6.3-3 (Debian Squeeze) with an sqlite database.
Pseudo code follows.
Is there another obvious way to retrieve the ids?
This is a limitation of the DBAPI that none of the various methods we use to
get last inserted id are available with an executemany(), i.e. .lastrowid,
result sets for RETURNING, etc.So you can either establish the PK
identities beforehand, use individual executions, or in some cases use a
heuristic to guess (i.e. 20 new rows would be Ids 43 through 63 type of thing,
just watch concurrency with that approach).
Enjoy,
--felix
#
# Setup Session etc.
#
data_table = Table('data', metadata,
Column('id', Integer, primary_key=True),
Column('stuff', String, nullable=False) )
insert_dicts = []
for x in range(10):
insert_dicts.append(dict(stuff=str(x)))
stmt = data_table.insert(bind=Session.bind)
res = stmt.execute(insert_dicts)
# Correct:
print res.rowcount
print r.inserted_primary_key()
# Raises:
#AttributeError: 'DefaultExecutionContext' object has no
#attribute '_inserted_primary_key'
# Initial trial with r.last_inserted_ids()
#SADeprecationWarning: Use inserted_primary_key
#Error - type 'exceptions.AttributeError': 'DefaultExecutionContext' object
#has no attribute '_inserted_primary_key'
--
Felix Wolfsteller | ++49 541 335083-783 | http://www.intevation.de/
PGP Key: 39DE0100
Intevation GmbH, Neuer Graben 17, 49074 Osnabrück | AG Osnabrück, HR B 18998
Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] .count()'ing with limiting query.
On Jun 7, 2011, at 8:57 AM, Filip Zyzniewski - Tefnet wrote: # this line: print BillGates.subordinates.count() # raises: # Traceback (most recent call last): # File count_problem.py, line 60, in module # print BillGates.subordinates.count() # File /home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/dynamic.py, line 251, in count # return self._clone(sess).count() # File /home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py, line 2123, in count # return self.from_self(col).scalar() # File /home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py, line 1775, in scalar # ret = self.one() # File /home/filip/tefnet/teferp/workspace/tefobjects/lib/python2.7/site-packages/SQLAlchemy-0.7.0-py2.7.egg/sqlalchemy/orm/query.py, line 1744, in one # ret = list(self) # File count_problem.py, line 17, in __iter__ # return sqlalchemy.orm.query.Query.__iter__(self.private()) # File count_problem.py, line 20, in private # crit = (self._mapper_zero().class_.isDeleted == False) # AttributeError: 'NoneType' object has no attribute 'class_' == As far as I could debug it the problem starts when count() creates a new query with the old one as a subquery (but I could very well be wrong). Well the recipe actually has a lot of use cases missing, if you said query(A, B) for example it only applies the public thing to A. If it were me I'd be calling the private() function explicitly as needed, I think the recipe is kind of a hack. So I can illustrate the extra hooks to get count() to work as expected, which is expect None for _mapper_zero(), apply the public() when from_self() is called: class LimitingQuery(Query): def get(self, ident): # override get() so that the flag is always checked in the # DB as opposed to pulling from the identity map. - this is optional. return Query.get(self.populate_existing(), ident) def __iter__(self): return Query.__iter__(self.private()) def from_self(self, *ent): return Query.from_self(self.private(), *ent) def private(self): mzero = self._mapper_zero() if mzero is not None: crit = mzero.class_.public == True return self.enable_assertions(False).filter(crit) else: return self I'll update the wiki. -- 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] get_history not returning as expected
Hello,
using Python 2.6 and SA 0.6.6. Please see the example below. I want to
get the History of a relationship attribute, but whatever I pass to the
passive argument, I never get the deleted version of the object,
only the PASSIVE_NO_RESULT symbol (if I understand correctly, I would
not need to pass anything). Where is the error in my reasoning with this?
Cheers
Sebastian
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.attributes import get_history
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Base.metadata.bind = engine
Session = sessionmaker(bind = engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key = True)
name = Column(String)
def __init__(self, name):
self.name = name
def __repr__(self):
return User('%s') % (self.name)
class Address(Base):
__tablename__ = addresses
id = Column(Integer, primary_key = True)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref = 'addresses')
street = Column(String)
def __init__(self, street):
self.street = street
def __repr__(self):
return Address('%s') % (self.street)
Base.metadata.create_all(engine)
session = Session()
u = User(joe)
a = Address(Downing Street)
u.addresses.append(a)
session.add(u)
session.commit()
u2 = User(jack)
a.user = u2
print get_history(a, user)
#([User('jack')], (), [symbol 'PASSIVE_NO_RESULT])
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] get_history not returning as expected
On Jun 7, 2011, at 1:23 PM, Sebastian Elsner wrote:
Hello,
using Python 2.6 and SA 0.6.6. Please see the example below. I want to get
the History of a relationship attribute, but whatever I pass to the passive
argument, I never get the deleted version of the object, only the
PASSIVE_NO_RESULT symbol (if I understand correctly, I would not need to pass
anything). Where is the error in my reasoning with this?
The short answer is set active_history=True on the relationship().
Its a subtlety in how the tracking works. History works by moving the previous
value of an attribute aside and establishing the new one as current. In your
test, a.user is expired, the __dict__ is empty - a similar condition as when
the object is first loaded. History tracking for many-to-one, for the purposes
of the unit of work, doesn't need to know the previous value. So for the
prevailing use case, that people want to be able to set a.user = new user
without an entirely needless SELECT occurring, the default is that no SELECT is
emitted.
If you were to load the attribute beforehand:
u2 = User(jack)
a.user
a.user = u2
print get_history(a, user)
you'd see it.
Otherwise setting active_history=True will load it when it changes, you'd just
see that SELECT statement occurring if the value weren't loaded already.
There's no option right now to load the old value only on the history grab.
It would add a lot of complexity and there hasn't been a need for it.
Cheers
Sebastian
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.orm.attributes import get_history
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Base.metadata.bind = engine
Session = sessionmaker(bind = engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key = True)
name = Column(String)
def __init__(self, name):
self.name = name
def __repr__(self):
return User('%s') % (self.name)
class Address(Base):
__tablename__ = addresses
id = Column(Integer, primary_key = True)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref = 'addresses')
street = Column(String)
def __init__(self, street):
self.street = street
def __repr__(self):
return Address('%s') % (self.street)
Base.metadata.create_all(engine)
session = Session()
u = User(joe)
a = Address(Downing Street)
u.addresses.append(a)
session.add(u)
session.commit()
u2 = User(jack)
a.user = u2
print get_history(a, user)
#([User('jack')], (), [symbol 'PASSIVE_NO_RESULT])
--
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.
--
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] sqlalchemy filter by count problem
I have a select with a count on it. Count row is taken from another related table so I can see how many items a user has. I want to filter the results to select only those with items more than 5 let's say. Sqlalchemy generates 2 queries for this. Placing a 'having' filter gives an error but looking at the generated sql's the first query is ok and working, only the second one gives an error. Can any1 say if there's a way in forcing sqlqlchemy to only generate 1 query? generated sql's: SELECT (SELECT count(`items`.id) AS count_1 FROM `items` WHERE `items`.user_id = user.id) AS `itemsCount`, user.id AS user_id FROM user HAVING itemsCount5 SELECT count(1) AS count_1 FROM user HAVING itemsCount5 and the error: OperationalError: (OperationalError) (1054, Unknown column 'itemsCount' in 'having clause') 'SELECT count(1) AS count_1 \nFROM user \nHAVING itemsCount5' () -- 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] Limiting query with custom order_by and eagerload
I ran into a problem with a rather unique query on SA 0.6.5 (reproducible on 0.7.1). The query eagerloads a collection, undefers a scalar-select column, orders on the latter, and applies a limit/ offset. To order by a scalar-select column without executing the subquery again, I have to apply a custom label on the select and use that in the order_by. The problem is that, when SA nests the query because of the limit/eagerload combo, it tacks on the order_by columns again, which fails since the custom label isn't valid by itself. Here is a self-contained script to illustrate the problem: http://dl.dropbox.com/u/45702/deferred.py The problem, on SA 0.6.5, came down to line 2390 of sqlalchemy.orm.query.Query._compile_context. Is this a bug or is there a better way to do the above? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] sqlalchemy filter by count problem
Not sure how you get 2 queries, but this seems like it gives right answer.
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
items = relationship('Item')
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(User.id))
q1 = session.query(User.id, func.count(Item.id)).\
join('items').\
group_by(User.id).having(func.count(Item.id)5)
Check out the documentation for Query.join for alternatives on the join
parameter
http://www.sqlalchemy.org/docs/06/orm/query.html#sqlalchemy.orm.query.Query.join
for example: if the items relationship isn't declared
.join((Item,User.id==Item.user_id))
generates the same SQL
--
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.
Re: [sqlalchemy] Limiting query with custom order_by and eagerload
you can do it manually as the combination of automatic joins and ordering by
subqueries seems to get tripped up (its a bug, but not sure of the nature of it)
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).limit(50).from_self().outerjoin(Book.purchases).all()
On Jun 7, 2011, at 7:12 PM, Yoann Roman wrote:
I ran into a problem with a rather unique query on SA 0.6.5
(reproducible on 0.7.1). The query eagerloads a collection, undefers a
scalar-select column, orders on the latter, and applies a limit/
offset. To order by a scalar-select column without executing the
subquery again, I have to apply a custom label on the select and use
that in the order_by. The problem is that, when SA nests the query
because of the limit/eagerload combo, it tacks on the order_by columns
again, which fails since the custom label isn't valid by itself.
Here is a self-contained script to illustrate the problem:
http://dl.dropbox.com/u/45702/deferred.py
The problem, on SA 0.6.5, came down to line 2390 of
sqlalchemy.orm.query.Query._compile_context.
Is this a bug or is there a better way to do the above?
Thanks.
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Limiting query with custom order_by and eagerload
sorry, either:
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).order_by(calculated_books_num_purchased).\
limit(50).from_self().outerjoin(Book.purchases).all()
or
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).order_by(Book.num_purchased).\
limit(50).from_self().outerjoin(Book.purchases).all()
the latter is more guaranteed to work correctly though may be less efficient
depending on backend.
On Jun 8, 2011, at 12:47 AM, Michael Bayer wrote:
you can do it manually as the combination of automatic joins and ordering by
subqueries seems to get tripped up (its a bug, but not sure of the nature of
it)
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).limit(50).from_self().outerjoin(Book.purchases).all()
On Jun 7, 2011, at 7:12 PM, Yoann Roman wrote:
I ran into a problem with a rather unique query on SA 0.6.5
(reproducible on 0.7.1). The query eagerloads a collection, undefers a
scalar-select column, orders on the latter, and applies a limit/
offset. To order by a scalar-select column without executing the
subquery again, I have to apply a custom label on the select and use
that in the order_by. The problem is that, when SA nests the query
because of the limit/eagerload combo, it tacks on the order_by columns
again, which fails since the custom label isn't valid by itself.
Here is a self-contained script to illustrate the problem:
http://dl.dropbox.com/u/45702/deferred.py
The problem, on SA 0.6.5, came down to line 2390 of
sqlalchemy.orm.query.Query._compile_context.
Is this a bug or is there a better way to do the above?
Thanks.
--
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.
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Limiting query with custom order_by and eagerload
this is ticket 2188:
http://www.sqlalchemy.org/trac/ticket/2188
The issue involves excessive digging into the column expressions of the order
by, when it attempts to place those columns into the nested query. This step
is necessary for many tests where the columns need to be SELECTed on the inside
so that they can be ordered on the outside.
A patch against 0.7 is attached. Once tests are assembled I'll patch it to
both 0.6 and 0.7.
Thanks for the clear test case !
On Jun 8, 2011, at 12:50 AM, Michael Bayer wrote:
sorry, either:
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).order_by(calculated_books_num_purchased).\
limit(50).from_self().outerjoin(Book.purchases).all()
or
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).order_by(Book.num_purchased).\
limit(50).from_self().outerjoin(Book.purchases).all()
the latter is more guaranteed to work correctly though may be less efficient
depending on backend.
On Jun 8, 2011, at 12:47 AM, Michael Bayer wrote:
you can do it manually as the combination of automatic joins and ordering by
subqueries seems to get tripped up (its a bug, but not sure of the nature of
it)
results = session.query(Book).options(
orm.undefer(Book.num_purchased),
orm.contains_eager('purchases')
).limit(50).from_self().outerjoin(Book.purchases).all()
On Jun 7, 2011, at 7:12 PM, Yoann Roman wrote:
I ran into a problem with a rather unique query on SA 0.6.5
(reproducible on 0.7.1). The query eagerloads a collection, undefers a
scalar-select column, orders on the latter, and applies a limit/
offset. To order by a scalar-select column without executing the
subquery again, I have to apply a custom label on the select and use
that in the order_by. The problem is that, when SA nests the query
because of the limit/eagerload combo, it tacks on the order_by columns
again, which fails since the custom label isn't valid by itself.
Here is a self-contained script to illustrate the problem:
http://dl.dropbox.com/u/45702/deferred.py
The problem, on SA 0.6.5, came down to line 2390 of
sqlalchemy.orm.query.Query._compile_context.
Is this a bug or is there a better way to do the above?
Thanks.
--
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.
--
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.
--
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.
2188.patch
Description: Binary data
--
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.
