[sqlalchemy] Re: Constructing where-clauses dynamically
but what happens if i say:
q.select(or_(User.c.orders.items.item_name == 'item#4',
User.c.orders.items.item_name == 'item #5'))
if we naively convert c.orders.items.item_name=='item #4' into
user.user_id=orders.user_id and orders.order_id=items.order_id and
items.item_name='item #4, then the other clause for item #5
generates into the same thing and you get an inefficient query. i
wonder also if some expressions above dont work correctly if the join
conditions are repeated like that.
its still better to say:
q.select(or_(Item.c.item_name == 'item#4', Item.c.item_name == 'item
#5'), from_obj=[c.orders.items])
isnt it ? (User.c.orders.items would be a synonym for query.join_via
('orders', 'items'))
On Nov 9, 2006, at 12:38 AM, Michael Bayer wrote:
On Nov 8, 2006, at 10:00 PM, Daniel Miller wrote:
q = session.query(User)
c = getcols(User)
q.select(
(c.addresses.street == 'some address')
(c.orders.items.item_name == 'item #4')
)
ohh, wow. heh. i had this discomfort with adding relationships
to c, but then you just wrapped up the whole mess of join_to/
join_via into one consistent syntax there, didnt you. youll notice
in the docs for join_to/join_via theyre marked with alpha
API (meaning I have been antsy with them anyway)so this syntax
is compelling.
you have to pipe in more often Dan !
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] orphan issue
Hi all,
I've run into a bit of an issue with session.flush()'s orphan
tracking. Although I know for certain that my objects have been run
through session.save_or_update(), when I actually call session.flush,
i run into this:
---
File
/usr/lib/python2.4/site-packages/sqlalchemy-svn/lib/sqlalchemy/orm/mapper.py,
line 226, in _is_orphan
raise exceptions.FlushError(instance %s is an unsaved, pending
instance and is an orphan (is not attached to any parent '%s' instance
via that classes' '%s' attribute) % (obj, klass.__name__, key))
sqlalchemy.exceptions.FlushError: instance __main__.Address object at
0xb7cb6a2c is an unsaved, pending instance and is an orphan (is not
attached to any parent 'Business' instance via that classes' 'address'
attribute)
---
now, I know it's attached to a session, and it's not an orphan. I've
created a test case and attached it that can perhaps better explain,
but in effect, I have multiple objects that may or may not have an
address in one of their relationships. It seems like the _is_orphan
method is not checking all objects in session.dirty or session.new to
see if the object that *could* fulfill a relationship is actually
meant to fulfill that particular relationship.
any help would be appreciated!
Pete
--
All guilt is relative, loyalty counts, and never let your conscience
be your guide.
- Lucas Buck, American Gothic
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
# define the tables
addresses = Table('addresses', metadata,
Column('address_id', Integer, primary_key=True, key='id'),
Column('addr1', String(30), key='address1'),
Column('addr2', String(30), key='address2'),
Column('city', String(20), key='city'),
Column('state', String(2), key='state'),
Column('zip', String(10), key='zip'),
Column('county', String(30), key='county')
)
homes = Table('homes', metadata,
Column('home_id', Integer, primary_key=True, key=id),
Column('description', String(128), key=description),
Column('address_id', Integer, ForeignKey('addresses.id'), nullable=False, key=addressId),
)
businesses = Table('businesses', metadata,
Column('business_id', Integer, primary_key=True, key=id),
Column('description', String(128), key=description),
Column('address_id', Integer, ForeignKey('addresses.id'), nullable=False, key=addressId),
)
people = Table('people', metadata,
Column('person_id', Integer, primary_key=True, key=id),
Column('name', String(64), key=name),
Column('home_id', Integer, ForeignKey('homes.id'), nullable=False, key=homeId),
Column('business_id', Integer, ForeignKey('businesses.id'), nullable=False, key=businessId),
)
# base object classes
class Address(object): pass
class Person(object): pass
class Business(object): pass
class Home(object): pass
# define the mappers
Address.mapper = mapper(Address, addresses)
Home.mapper = mapper(Home, homes, properties=dict(
address = relation(Address, lazy=False, private=True,
primaryjoin=homes.c.addressId==addresses.c.id),
)
)
Business.mapper = mapper(Business, businesses, properties=dict(
address = relation(Address, lazy=False, private=True,
primaryjoin=businesses.c.addressId==addresses.c.id),
)
)
Person.mapper = mapper(Person, people, properties=dict(
home = relation(Home, lazy=False, private=True,
primaryjoin=people.c.homeId==homes.c.id),
business = relation(Business, lazy=False, private=True,
primaryjoin=people.c.businessId==businesses.c.id),
))
def test():
metadata.create_all(connectable=engine)
session = create_session(bind_to=engine)
# create an address
address = Address()
address.addr1 = 999 Something Lane
address.city = Nowheresville
address.state = NA
address.zip = 9
# now, we'll make a business
bus = Business()
bus.description = I make some money!
# now, a home
home = Home()
home.description = I live here!
# now, a person
person = Person()
person.name = Jack Jackson
# we'll make the home have an address
home.address = address
person.home = home
person.business = bus
for obj in [address, bus, home, person]:
session.save_or_update(obj)
# on flush, this will raise an orphan error
session.flush()
if __name__ == __main__:
test()
[sqlalchemy] Re: Clases with __slots__ attribute
On 11/7/06, juanvalino [EMAIL PROTECTED] wrote: Hi, I've detected that when a class has __slots__ attribute, a mapper cannot be build because the sqlalchemy tries to create a magic attribute and fails: File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, line 672, in init_attr AttributeError: 'MyClass' object has no attribute '_MyClass__sa_attr_state' I think you'll have better luck making a class w/o __slots__ than changing something this deep in the core of SA. (If you must have __slots__ for some reason, you can always subclass it to get a slots-less version.) -- Jonathan Ellis http://spyced.blogspot.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: orphan issue
Im going to run this, and before running it im going to say, just for kicks, that its not an SA bug (i havent looked at it yet). because i think orphan-detection works pretty well. But if it is an SA bug, then i think its time for me to do a release tonight or tomorrow of 0.3.1, since we had a few flush-related issues already fixed in the trunk. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: orphan issue
oh and i just saw your patch. OK, i see what youre getting at. the orphan idea was built to support the notion of the object being attached to a parent of the same type. ill have to think about if i want it to work the way youre proposing, i.e. the instance can be attached to any number of types of parents. im not sure if that really works, because the idea of orphan is intended to be a data-integrity feature. i want to see what hibernate says about it (although its likely that hibernate just doesnt botherthats frequently their approach). one way that you could make your test work right now (actually i should test this) is by using inheritance, i.e. defining a common parent class of Home and Business and defining a mapper against that. although that would mean we're defining an abstract base mapper...im not sure how feasable that is...OK..let me look at this a little. also the patch fails the unit tests that specifically test the orphan function. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: orphan issue
this is an enhancement, but i think its OK so its in changeset 2089. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Problem with count in rev 2089
Prior to 2089, the following worked fine:
session.query(ItemKeyword).count()
where ItemKeyword has a compound primary key that is defined in its
mapper as follows:
mapper(ItemKeyword, itemkeyword_table, primary_key =
[itemkeyword_table.c.item_uuid, itemkeyword_table.c.keyword_uuid],
properties={'keyword': relation(Keyword, lazy=False, backref='itemkeywords')})
With 2089, I get the following error:
session.query(ItemKeyword).count()
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/orm/query.py, line 270, in count
s = sql.select([sql.func.count(list(self.table.primary_key)[0])],
whereclause, from_obj=from_obj, **kwargs)
IndexError: list index out of range
--
Platform: error occurs on both Ubuntu Linux and Windows XP; database is sqlite.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Constructing where-clauses dynamically
Michael Bayer wrote:
but what happens if i say:
q.select(or_(User.c.orders.items.item_name == 'item#4',
User.c.orders.items.item_name == 'item #5'))
if we naively convert c.orders.items.item_name=='item #4' into
user.user_id=orders.user_id and orders.order_id=items.order_id and
items.item_name='item #4, then the other clause for item #5
generates into the same thing and you get an inefficient query. i
wonder also if some expressions above dont work correctly if the join
conditions are repeated like that.
Can you give an example of the SQL (including the joins) that would be
generated by your statement above?
its still better to say:
q.select(or_(Item.c.item_name == 'item#4', Item.c.item_name == 'item
#5'), from_obj=[c.orders.items])
isnt it ? (User.c.orders.items would be a synonym for query.join_via
('orders', 'items'))
Right. It should be possible (although I'm not sure how simple) to combine
conditions using the rules for combining logical expressions (i.e. commutative,
transitive, etc.). For example:
(A == B AND C == D) OR (A == B AND C == E)
can be reduced to
(A == B) AND (C == D OR C == E)
So what we need is a way to take a group of expressions and reduce them to
their simplest form before generating the SQL. However, don't most main-stream
databases do this kind of optimization anyway? MySQL does
(http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html).
Having said all that, it may be simpler to use join_to/join_via in some cases
and maybe that's a good reason to keep those functions around. However, I think
this new syntax would still be very valuable in many cases.
FWIW, an equivalent but slightly more concise version of your query above would
be this:
q.select(user.c.orders.items.item_name.in_(item#4, item #5))
~ Daniel
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
