[sqlalchemy] Eager loading hates single table inheritance
Here's a list of pathological test cases that confuses the hell out of SA
while trying to eager load more than 1 collections which are mapped to the
same table using single table inheritance. In short, only joinedload*()
appears to work out of all the eager loading methods. This pretty much
means that supplying eager load options to a Query object doesn't mean you
will always get back the same result.
Ideally, I'd like subqueryload*() and contains_eager() to work just like
joinedload*() to prevent a situation where I have to waste bandwidth
loading the same data over and over again or doing MxN queries.
Is there anyway that I can do what I want without rewriting my complicated
query in full SQL expression?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/u-PW089d3McJ.
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.
#! /usr/bin/env python
from sqlalchemy import Column, Integer, Unicode, ForeignKey, Enum, Table, \
create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload, \
joinedload_all, contains_eager, subqueryload, subqueryload_all
Base = declarative_base()
metadata = Base.metadata
class SearchOption(Base):
__tablename__ = searchoption
id = Column(Integer, autoincrement=True, primary_key=True)
parent_id = Column(Integer, ForeignKey(id,
onupdate=CASCADE,
ondelete=CASCADE))
parent = relationship(SearchOption, uselist=False, remote_side=[id],
backref=backref(children))
discriminator = Column(type, Enum(origin, food,
name=searchoptiontype))
__mapper_args__ = {polymorphic_on: discriminator}
displayname = Column(Unicode(64), nullable=False)
class OriginOption(SearchOption):
__mapper_args__ = {polymorphic_identity: origin}
class FoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: food}
product_searchoption_table = Table(product_searchoption, metadata,
Column(product_id,
Integer,
ForeignKey(product.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True),
Column(searchoption_id,
Integer,
ForeignKey(searchoption.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True))
class Product(Base):
__tablename__ = product
id = Column(Integer, autoincrement=True, primary_key=True)
origin = relationship(OriginOption, uselist=False,
secondary=product_searchoption_table)
foods = relationship(FoodOption,
secondary=product_searchoption_table)
if __name__ == __main__:
engine = create_engine(postgresql+psycopg2://tester:tester@localhost/test_eagerload,
echo=True)
Session = sessionmaker(engine)
session = Session()
metadata.create_all(engine)
usa = OriginOption(displayname=uusa)
canada = OriginOption(displayname=ucanada)
apple = FoodOption(displayname=uapple)
orange = FoodOption(displayname=uorange)
banana = FoodOption(displayname=ubanana)
product1 = Product(origin=usa, foods=[apple])
product2 = Product(origin=canada, foods=[orange, banana])
product3 = Product()
session.add(product1)
session.add(product2)
session.add(product3)
session.commit()
session.expunge_all()
# If all the collections to eager load belong to a single table inheritance
# mapping, there's no way to let SA know to optimize this it seems.
p = session.query(Product)\
.options(subqueryload_all(Product.origin, Product.foods))\
.filter(Product.id == 2).one()
assert p.id == 2
assert p.origin.displayname == ucanada # This is only eager loaded by the previous query
assert [f.displayname for f in p.foods] == [u'orange', u'banana'] # This is still lazy loaded
session.expunge_all()
# Now all the collections are eagerly loaded, but extremely inefficient
# because of all the MxN queries
p = session.query(Product)\
Re: [sqlalchemy] Eager loading hates single table inheritance
On 10/1/12 12:19 AM, Michael Bayer wrote: A common theme in SQLAlchemy is that, despite all the flak we get for being complicated, SQLAlchemy is actually very simple. It has a handful of constructs which seek to do exactly the same thing in exactly the same way, as consistently as possible. So we sometimes get requests for SQLAlchemy should figure out XYZ and it's like, well not really, that would be really complicated. Ah ha don't get me wrong but I didn't say SA was complicated, I said my actual queries used in production is complicated. What's I've shown you was just a simplified version of what I've narrowed down. SA sure had a large surface area to get started because it does so much more, but I've grown to love it. This is really the first time I tried to do something complicated like this with SA and I was testing if SA would be smart about this. I can't really live without SA now. you'd use two joinedload() twice for that. So all of the examples where you're distinguishing xyz_all() from xyz(), all the same thing. It's silently ignored now so http://www.sqlalchemy.org/trac/ticket/2370 is added. If I can get to it in 0.7 it will emit a warning, will raise an error in 0.8. Ah, thanks for telling me this. Didn't notice this from the docs. In the subqueryload_all()/subqueryload() example, you'll note the second one with two separate subqueryload() calls does in fact correctly do the subqueryload twice. There's no way SQLA would ever figure out automatically that they're against the same table and then join the two queries together, decisionmaking like would be enormously complicated as well as the mechanics of how to get a single loader to coordinate among two relationships. If you use just one relationship() to SearchOption then provide filtered accessors, then you get exactly the optimization you're looking for. Is it possible to include a special flag to the subqueryload() call to tell SA to optimize for this case? SA already knows about the class hierarchy to be able to distinguish between the classes and how they map to the rows returned, changing the model mapping is not always feasible. In fact, if SA could do this, subqueryload() will be the optimal solution for this use case. joinedload(), even when it works, it's still too wasteful. Using events will work for now, but it's not as obvious. SA already is so smart about things, surely it can be smarter no? :P Ticket 2120 calls for at least an option nested_joins=True, specifying that the more correct/efficient system of nesting joins should be used. This is all 0.8 stuff, as the joined eager loading code would be destabilized by this - if it turns out to be an isolated option it could move to 0.7. Your set of tests here makes me more interested in the issue though so perhaps we'll see if I have time to try some things out. Does it mean that the only way to eager load reliably in this many-to-many single table inheritance use case, depending on the data, is joinedload(innerjoin=True) for uselist=False relationships for now? In my case, I can just use innerjoin=True all the way, but there may be cases where the code has to switch between innerjoin and left join depending on the relationships. Finally the contains_eager version. SQLAlchemy again expects two distinct sets of columns for each relationship, so you must join to the table twice: sa1 = aliased(OriginOption) sa2 = aliased(FoodOption) p = session.query(Product)\ .join(sa1, Product.origin)\ .join(sa2, Product.foods)\ .options(contains_eager(Product.origin, alias=sa1), contains_eager(Product.foods, alias=sa2))\ .filter(Product.id == 2).one() Same theme here, you're hoping SQLAlchemy can figure out something in Python, i.e. that only certain rows being routed to Product.origin/Product.foods should be used for each, but it's not that complicated. It relies upon SQL to present it with the correct data geometry and assumes it is correct. Second-guessing what it gets back from SQL to check, oh is this some special 1% edge case where I might have to filter extra types that I'm not supposed to receive here? wouldn't be efficient or consistent with how everything else works. consistently. Ah thanks for this solution, but again, self joining for each relationship is too inefficient. The point is, as a user, I think the query with a single join already contains all the data needed to reconstruct collections. Plus, I've already told SA that the results will contain the rows that it can populate the collections with, why can't it do that? That's the thought that went through my head. Again, I think if contains_eager() accepts a flag that tells it to optimize for this case, the code to handle this will be isolated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To
Re: [sqlalchemy] Eager loading hates single table inheritance
Ah Thanks! A bit of a hack but certainly works for now. Thanks for helping out. Really appreciate it! Jimmy Yuen Ho Wong On 10/1/12 3:31 AM, Michael Bayer wrote: On Jan 9, 2012, at 11:19 AM, Michael Bayer wrote: The first thing I note here is, if I were doing a model like this, I'd either use two different association tables between Product-Origin and Product-Food, or I'd make one relationship(), and handle the filtering in Python (which is essentially what you're wishing SQLAlchemy did here). The ORM wants to know about your table relationships which here is just A-assoc-B.All of the issues here, some of which I consider to be SQLA bugs anyway, would go away if you did it in that manner, subqueryloading would be efficient, etc. Here's an event that does what you need: from sqlalchemy.orm import attributes from sqlalchemy import event class Product(Base): __tablename__ = product id = Column(Integer, autoincrement=True, primary_key=True) options = relationship(SearchOption, secondary=product_searchoption_table) origin = relationship(OriginOption, uselist=False, secondary=product_searchoption_table) foods = relationship(FoodOption, secondary=product_searchoption_table) @event.listens_for(Product, load) def mything(target, context): if 'options' in target.__dict__: attributes.set_committed_value( target, 'foods', [o for o in target.options if o.discriminator=='food'] ) origin = [o for o in target.options if o.discriminator=='origin'] attributes.set_committed_value( target, 'origin', origin[0] if origin else None ) # only 2 queries for row in session.query(Product).options(subqueryload(Product.options)): print row, row.origin, row.foods if I added an onload event for individual relationship attributes that would make this event a little more targeted. -- 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] order_by with explicit column name messes up subqueryload
Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? Thanks in advance! -- 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: order_by with explicit column name messes up subqueryload
BTW, aliased() and alias() don't work on a label() either. I tried passing the label object straight into the order_by() as well to no avail. I'm all out of ideas. On Jan 9, 3:47 am, Yuen Ho Wong wyue...@gmail.com wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? Thanks in advance! -- 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: order_by with explicit column name messes up subqueryload
Except that LIMIT and OFFSET are present in my query, gnarly isn't it ? :P d = label(distance, some_complicated_geoalchemy_function_call(columns...)) q = session.query(Product, Merchant.location, d)\ .join(Merchant, Product.merchant_id == Merchant.id)\ .filter(Product.numinstock 0)\ .options(subqueryload_all(Product.origin, Product.style, Product.foods, Product.flavors, Product.occasions, Product.moods, Product.varieties)) q = q.order_by(distance).offset(0).limit(20).all() On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote: Hi, I have a rather complicated problem and I was wondering if you guys could help. So I have a query, session.query(Product, Merchant, d), where Product is 1-to-many with Merchant, and d is the distance from some lat long. d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy function calls. Product has a number of collections in which I would like to load using subqueryload_all() as well, and the result is ordered by distance as in order_by(distance), where distance is the name of the label d. My problem is, since I'm supplying the Query object with an explicit order_by() name, when I use subqueryload(), the order_by() name is put into the subquery as is, because SQLAlchemy doesn't know any better with a plain string. If I pass in a column element, SQLAlchemy seems to know not to put an ORDER BY in the subquery. This seems to me like a bug because a subqueryload() always join on the primary key of the previous SELECT, so unless the name is the primary key name, it really shouldn't be put in the subquery. So finally my question, if this is too hard to fix, is there an option somewhere that I can tell SA to ignore the previous order_by() when doing a subqueryload()? If not, and I can't wait for a fix now, is there a way where I can turn my distance label into a ClauseElement so that SA knows not to put into the subquery when loading collections? any chance you can save me some time and attach a complete, succinct .py example here ? subqueryload removes the ORDER BY from the query, provided LIMIT/OFFSET aren't present, unconditionally. It doesn't care that it's a string or not. http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/orm/strategies -- 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: order_by with explicit column name messes up subqueryload
Here are the models:
class Merchant(Base):
__tablename__ = merchant
id = Column(Integer, autoincrement=True, primary_key=True)
location = GeometryColumn(Point, nullable=False)
class SearchOption(Base):
__tablename__ = searchoption
id = Column(Integer, autoincrement=True, primary_key=True)
parent_id = Column(Integer, ForeignKey(id,
onupdate=CASCADE,
ondelete=CASCADE))
parent = relationship(SearchOption, uselist=False,
remote_side=[id],
backref=backref(children))
discriminator = Column(type, Enum(style, origin, price,
food,
flavor, occasion,
variety, mood,
name=searchoptiontype))
__mapper_args__ = {polymorphic_on: discriminator}
displayname = Column(Unicode(64), nullable=False)
class StyleOption(SearchOption):
__mapper_args__ = {polymorphic_identity: style}
class OriginOption(SearchOption):
__mapper_args__ = {polymorphic_identity: origin}
class FoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: food}
class FlavorOption(SearchOption):
__mapper_args__ = {polymorphic_identity: flavor}
class OccasionOption(SearchOption):
__mapper_args__ = {polymorphic_identity: occasion}
class VarietyOption(SearchOption):
__mapper_args__ = {polymorphic_identity: variety}
class MoodOption(SearchOption):
__mapper_args__ = {polymorphic_identity: mood}
product_searchoption_table = Table(product_searchoption, metadata,
Column(product_id,
Integer,
ForeignKey(product.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True),
Column(searchoption_id,
Integer,
ForeignKey(searchoption.id,
onupdate=CASCADE,
ondelete=CASCADE),
primary_key=True))
class Product(Base): # tune full-text search
__tablename__ = product
id = Column(Integer, autoincrement=True, primary_key=True)
origin = relationship(OriginOption, uselist=False,
secondary=product_searchoption_table)
style = relationship(StyleOption, uselist=False,
secondary=product_searchoption_table)
pricerange = relationship(PriceOption, uselist=False,
secondary=product_searchoption_table)
foods = relationship(FoodOption,
secondary=product_searchoption_table)
flavors = relationship(FlavorOption,
secondary=product_searchoption_table)
occasions = relationship(OccasionOption,
secondary=product_searchoption_table)
moods = relationship(MoodOption,
secondary=product_searchoption_table)
varieties = relationship(VarietyOption,
secondary=product_searchoption_table)
merchant_id = Column(Integer, ForeignKey(merchant.id,
onupdate=CASCADE,
ondelete=CASCADE),
nullable=False,)
merchant = relationship(Merchant, uselist=False,
backref=backref(products))
Hope this helps.
On Jan 9, 4:16 am, Yuen Ho Wong wyue...@gmail.com wrote:
Except that LIMIT and OFFSET are present in my query, gnarly isn't
it ? :P
d = label(distance,
some_complicated_geoalchemy_function_call(columns...))
q = session.query(Product, Merchant.location, d)\
.join(Merchant, Product.merchant_id == Merchant.id)\
.filter(Product.numinstock 0)\
.options(subqueryload_all(Product.origin, Product.style,
Product.foods, Product.flavors, Product.occasions, Product.moods,
Product.varieties))
q = q.order_by(distance).offset(0).limit(20).all()
On Jan 9, 3:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 8, 2012, at 2:47 PM, Yuen Ho Wong wrote:
Hi,
I have a rather complicated problem and I was wondering if you guys
could help.
So I have a query, session.query(Product, Merchant, d), where Product
is 1-to-many with Merchant, and d is the distance from some lat long.
d is actually a sqlalchemy.sql.label() of some complicated GeoAlchemy
function calls.
Product has a number of collections in which I would like to load
using subqueryload_all() as well, and the result is ordered by
distance as in order_by(distance), where distance is the name of
the label d.
My problem is, since I'm supplying the Query object with an explicit
order_by() name, when I use subqueryload(), the order_by() name is put
into the subquery as is, because SQLAlchemy doesn't know any better
with a plain string
[sqlalchemy] oursql two phase commit syntax error
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
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: oursql two phase commit syntax error
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the script
works for me, provided I comment out the non-existent session_bindings
variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
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
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 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: oursql two phase commit syntax error
Replace the double quotes around the %s with single quotes seems to
have solved the problem with either default SQL MODE or ANSI_QUOTES
set.
Thanks for the helping!
On Jun 7, 3:25 am, Yuen Ho Wong wyue...@gmail.com wrote:
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the
script works for me, provided I comment out the non-existent
session_bindings variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE
`address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
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
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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options
[sqlalchemy] How to get the instance back from a PropComparator?
So I have this following code: class User(Base): class AgeComparator(PropComparator): def __lt__(self, other): pass def __gt__(self, other): pass def __eq__(self, other): pass def __ne__(self, other): return not (self == other) def __le__(self, other): return self other or self == other def __ge__(self, other): return self other or self == other @comparable_using(AgeComparator) @property def age(self): today = date.today() age = today - (self.date_of_birth or (today + 1)) return age.days / 365 All I want to do is this: user = User(date_of_birth=date.today()) session.add(user) new_borns = session.query(User).filter(User.age == 0).all() The doc for comparable_property() suggests that this is possible, but I'm lost finding my way to call the descriptor bound on this instance. The problem I have is that the ComparableProperty only gave itself and the mapper to the comparator, but the user instance is nowhere to be found inside either ComparableProperty, PropComparator or mapper. I'd appreciate some help here if this is at all possible. The documents on this is a little too sparse IMO. --~--~-~--~~~---~--~~ 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: How to get the instance back from a PropComparator?
Ah ha, so SQL expression operations are all translated directly to their appropriate SQL clauses. This makes sense I guess. Too bad this means I have to implement the same function twice, one in Python and another in SQL. Would be nice if there was some magic to morph custom properties to have some capabilities to operate on the Python side, but I guess that would be much slower. Right now I solved this problem with this new comparator: class AgeComparator(PropComparator): def __clause_element__(self): return (func.datediff(func.curdate(), self.mapper.c.date_of_birth) / 365) def operate(self, op, *args, **kwargs): return op(self.__clause_element__(), *args, **kwargs) Thanks a lot! On Sep 27, 1:14 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 27, 2009, at 12:49 PM, Yuen Ho Wong wrote: So I have this following code: class User(Base): class AgeComparator(PropComparator): def __lt__(self, other): pass def __gt__(self, other): pass def __eq__(self, other): pass def __ne__(self, other): return not (self == other) def __le__(self, other): return self other or self == other def __ge__(self, other): return self other or self == other @comparable_using(AgeComparator) @property def age(self): today = date.today() age = today - (self.date_of_birth or (today + 1)) return age.days / 365 All I want to do is this: user = User(date_of_birth=date.today()) session.add(user) new_borns = session.query(User).filter(User.age == 0).all() The doc for comparable_property() suggests that this is possible, but I'm lost finding my way to call the descriptor bound on this instance. The age(self): function is only called when you actually have an instance, such as: user = sess.query(User).get(10) print age is: , user.age The point of @comparable_using associates the behavior of AgeComparator to the age attribute on the User class, no instance: User.age == 0 User.age == 0 is going to invoke the __eq__() method on the AgeComparator you created. There is no instance within the query() call here. __eq__() needs to return a clause expression of your choosing, which must be expressed in terms of SQL functions, since you're rendering a SQL statement. That's a little tricky here since there's a lot of date arithmetic there, but using hypothetical functions it would look something like: def __eq__(self, other): return func.count_days(func.date_diff(func.now() - mapped_table.c.date_of_birth)) / 365 The comparable_using example should probably include a short PropComparator to give more context. A sample comparator is at: http://www.sqlalchemy.org/docs/05/mappers.html#custom-comparators The problem I have is that the ComparableProperty only gave itself and the mapper to the comparator, but the user instance is nowhere to be found inside either ComparableProperty, PropComparator or mapper. I'd appreciate some help here if this is at all possible. The documents on this is a little too sparse IMO. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
