[sqlalchemy] 0.8.x - can't get correlation to work with nested subquery in column property
I am getting stuck trying to upgrade to 0.8.x, as I can't get correlation
to work with nested subquery in column property. Here's a slightly absurd
example for illustration:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Phone(Base):
__tablename__ = 'phones'
id = Column(Integer, primary_key=True)
number = Column(String(20))
contact_id = Column(Integer, ForeignKey('contacts.id'))
contact = relationship('Contact', backref='phones')
class Contact(Base):
__tablename__ = 'contacts'
id = Column(Integer, primary_key=True)
name = Column(String(200))
t = select(
[Phone.number.label('value')],
Phone.contact_id == Contact.id,
).correlate(Contact.__table__).alias('t')
Contact.phone_numbers = column_property(
select(
[func.string_agg(t.c.value, ', ')],
).as_scalar().label('phone_numbers'),
deferred=True,
)
session = Session()
print session.query(Contact).order_by(Contact.phone_numbers)
0.7.x:
SELECT contacts.id AS contacts_id, contacts.name AS contacts_name
FROM contacts ORDER BY (SELECT string_agg(t.value, :string_agg_2) AS
string_agg_1
FROM (SELECT phones.number AS value
FROM phones
WHERE phones.contact_id = contacts.id) AS t)
0.8.x:
SELECT contacts.id AS contacts_id, contacts.name AS contacts_name
FROM contacts ORDER BY (SELECT string_agg(t.value, :string_agg_2) AS
string_agg_1
FROM (SELECT phones.number AS value
FROM phones, contacts
WHERE phones.contact_id = contacts.id) AS t)
I tried `correlate_except` but it doesn't work in this case either. Perhaps
a side effect from ticket:2668?
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Changing a value linked with a one-to-one relationship
Hello Michael
Sorry for the late reply.
So, below is my full stack trace:
Traceback (most recent call last):
File
/Users/foobar/Developpement/nursery_project/applications/nurserydb/utils_scripts/test2.py,
line 64, in module
plant.taxon = taxon_new # triggers an IntegrityError
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 303, in __set__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 804, in set
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 824, in fire_replace_event
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 1131, in emit_backref_from_scalar_set_event
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 638, in append
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 788, in set
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/attributes.py,
line 613, in get
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py,
line 524, in _load_for_state
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/strategies.py,
line 585, in _emit_lazyload
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py, line
2104, in all
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/query.py, line
2215, in __iter__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1138, in _autoflush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1817, in flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1935, in _flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/langhelpers.py,
line 58, in __exit__
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/session.py, line
1899, in _flush
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py,
line 372, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/unitofwork.py,
line 525, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py,
line 58, in save_obj
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/orm/persistence.py,
line 491, in _emit_update_statements
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
662, in execute
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
761, in _execute_clauseelement
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
874, in _execute_context
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
1024, in _handle_dbapi_exception
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/util/compat.py, line
163, in raise_from_cause
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/base.py, line
867, in _execute_context
File build/bdist.macosx-10.6-x86_64/egg/sqlalchemy/engine/default.py,
line 324, in do_execute
sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
taxon_id violates not-null constraint
'UPDATE botany.plant SET taxon_id=%(taxon_id)s WHERE botany.plant.id =
%(botany_plant_id)s' {'taxon_id': None, 'botany_plant_id': -2147483643}
I guess it correspond to what you mentioned.
I have tried to use what you told :
with session.no_autoflush:
plant.taxon = taxon_new
...and it works fine.
But the thing is that I don't need it in case I comment out the line :
taxon_old = session.query(Taxon).get(-2147483634)
and the test:
print plant.taxon is taxon_old # True
It seems that the problems occurs because the taxon_old is already loaded
in the identity map so the taxon.plant of the already loaded taxon_old must
be changed to reflect the change made by the below line:
plant.taxon = taxon_new # triggers an IntegrityError
This is totally understandable.
However, I get confused by SQLAlchemy trying to set taxon_id to NULL or
even worse, by SQLAlchemy trying to delete the plant if I configure a
cascade='all, delete-orphan' on the backref side of the relationship. I
understand that it might not be easy for SQLAlchemy to understand what I
am trying to do, but the fact that I end up with two different results
depending on whether or not taxon_old is previously loaded is confusing.
Anyway, SQLAlchemy is still very impressive and never ceases to amaze me
every day I discover new features. Thank you for that.
Le mardi 4 juin 2013 16:38:30 UTC+2, Etienne Rouxel a écrit :
Hello
I would like to change a value in a one-to-one relationship but I cannot
because of some actions that SQLAlchemy try to do, and I don't know why.
Here is my simplified code :
# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
_taxon_table = Table('taxon', Base.metadata,
Column('id', Integer, primary_key=True),
schema='botany'
)
_foliagetype_table = Table('foliagetype', Base.metadata,
Re: [sqlalchemy] [Q][SA 0.7.9] yield_per() vs. joinedload() and subqueryload()
Hello. I have one more question about my approach to WindowedRangeQuery: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Suppose I have a complex query (without options() / order_by()) to obtain the objects I want. I use this query to calculate window intervals. I don't have to use it again to fetch the objects, because I already have their id intervals. Am I right? Thank you, Ladislav Lenart On 4.6.2013 19:15, Ladislav Lenart wrote: On 4.6.2013 18:49, Michael Bayer wrote: On Jun 4, 2013, at 12:28 PM, Ladislav Lenart lenart...@volny.cz wrote: Absolutely, you should do whatever you have to in order to get the range you want, in fact the recipe even says this... Ok. What I want to do is basically the following: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Can this strategy work? there's not a publicly supported feature to reset the options right now so you'd probably need to apply them after you get your window ranging query. You can probably remove the effect of eager loaders if you were to just assign a blank _attributes dictionary to the new query though (just no long term guarantees). other than that, I use the windowing concept extensively and it works very well. Ok, I will try it. Thank you very much for your invaluable insights, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][SA 0.7.9] yield_per() vs. joinedload() and subqueryload()
Hello. I've tried an experiment to verify that yield_per() with subqueryload() behaves as badly as you described, but according to my practical observation, it issues ONE subqueryload() and everything behaves as I would expect. It emits two SELECTs, one to fetch the objects and the second one to fetch the related data and that's about it, see below. So back to square one, what is wrong with it? Base = declarative_base() class Contact(Base): # Just a sketch, I hope you get the picture. id, name, phones = relationship(Phone) # many class Phone(Base): # Just a sketch, I hope you get the picture. id, number, contact = relationship(Contact) # one # Setup engine with echo set to True. phones = ['123456789', '987654321', '555777999'] for i in range(1, 11): c = Contact(name=u' '.join([u'Contact', unicode(i)])) session.add(c) session.add_all(Phone(contact=c, number=e) for e in phones) session.flush() session.expunge_all() q = session.query(Contact).options(subqueryload(Contact.phones)) for each in q.yield_per(2): print each.last_name for e in each.phones: print e The output is like this: SA info about all the inserts after session.flush(). SA info about select for contacts. SA info about select for their phones. Contact 10 123456789 987654321 555777999 Contact 9 123456789 987654321 555777999 Contact 8 123456789 987654321 555777999 Contact 7 123456789 987654321 555777999 Contact 6 123456789 987654321 555777999 Contact 5 123456789 987654321 555777999 Contact 4 123456789 987654321 555777999 Contact 3 123456789 987654321 555777999 Contact 2 123456789 987654321 555777999 Contact 1 123456789 987654321 555777999 Thank you, Ladislav Lenart On 5.6.2013 11:26, Ladislav Lenart wrote: Hello. I have one more question about my approach to WindowedRangeQuery: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Suppose I have a complex query (without options() / order_by()) to obtain the objects I want. I use this query to calculate window intervals. I don't have to use it again to fetch the objects, because I already have their id intervals. Am I right? Thank you, Ladislav Lenart On 4.6.2013 19:15, Ladislav Lenart wrote: On 4.6.2013 18:49, Michael Bayer wrote: On Jun 4, 2013, at 12:28 PM, Ladislav Lenart lenart...@volny.cz wrote: Absolutely, you should do whatever you have to in order to get the range you want, in fact the recipe even says this... Ok. What I want to do is basically the following: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Can this strategy work? there's not a publicly supported feature to reset the options right now so you'd probably need to apply them after you get your window ranging query. You can probably remove the effect of eager loaders if you were to just assign a blank _attributes dictionary to the new query though (just no long term guarantees). other than that, I use the windowing concept extensively and it works very well. Ok, I will try it. Thank you very much for your invaluable insights, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Specifying foreign_keys and remote_side in self-referential many-to-many relationship
Hi, I have two tables User and Following, and User has two relationships followings and followers. It’s so typical, and next I want is readonly relationship to union of followings and followers. I tried like: friends = relationship( 'User', collection_class=set, primaryjoin='''or_( and_(foreign(User.id) == Following.follower_id, remote(User.id) == Following.followee_id), and_(foreign(User.id) == Following.followee_id, remote(User.id) == Following.follower_id) )''', viewonly=True ) but it seems to no work because foreign_keys and remote_side are the same columns (User.id). The error message is: sqlalchemy.exc.ArgumentError: Can't determine relationship direction for relationship 'User.friends' - foreign key columns within the join condition are present in both the parent and the child's mapped tables. Ensure that only those columns referring to a parent column are marked as foreign, either via the foreign() annotation or via the foreign_keys argument. Basically I want to find how to specify foreign_keys and remote_side in such self-referential many-to-many relationships, but it’s okay if there’re any other ways to implement the same thing. I just need to use such relationship in query expressions e.g. query.filter(User.friends.any(…)), query.options(joinedload(User.friends)) and instance properties e.g. map(make_json, user.friends). (If I implement it using @property decorator it won’t work with query expressions.) I read these two chapters in the docs: http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#self-referential-many-to-many-relationship http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#creating-custom-foreign-conditions Are there any other resources to get more hints about it? Thanks, Hong Minhee -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Specifying foreign_keys and remote_side in self-referential many-to-many relationship
It seems I found the most close solution: http://stackoverflow.com/a/9119764/383405 I haven’t been aware that secondary can take select() query as well. Might there be another way to achieve the same by any chance? On Jun 5, 2013, at 9:27 PM, Hong Minhee min...@dahlia.kr wrote: Hi, I have two tables User and Following, and User has two relationships followings and followers. It’s so typical, and next I want is readonly relationship to union of followings and followers. I tried like: friends = relationship( 'User', collection_class=set, primaryjoin='''or_( and_(foreign(User.id) == Following.follower_id, remote(User.id) == Following.followee_id), and_(foreign(User.id) == Following.followee_id, remote(User.id) == Following.follower_id) )''', viewonly=True ) but it seems to no work because foreign_keys and remote_side are the same columns (User.id). The error message is: sqlalchemy.exc.ArgumentError: Can't determine relationship direction for relationship 'User.friends' - foreign key columns within the join condition are present in both the parent and the child's mapped tables. Ensure that only those columns referring to a parent column are marked as foreign, either via the foreign() annotation or via the foreign_keys argument. Basically I want to find how to specify foreign_keys and remote_side in such self-referential many-to-many relationships, but it’s okay if there’re any other ways to implement the same thing. I just need to use such relationship in query expressions e.g. query.filter(User.friends.any(…)), query.options(joinedload(User.friends)) and instance properties e.g. map(make_json, user.friends). (If I implement it using @property decorator it won’t work with query expressions.) I read these two chapters in the docs: http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#self-referential-many-to-many-relationship http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#creating-custom-foreign-conditions Are there any other resources to get more hints about it? Thanks, Hong Minhee -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][SA 0.7.9] yield_per() vs. joinedload() and subqueryload()
Hello. One more note. I've just tried the below experiment with joinedload() instead of subqueryload() and that does NOT work just as you expected. One contact is returned several times and the first occurrences have incomplete phones. However my experiments with subqueryload() suggest that it works just fine with yield_per(). Can you elaborate on that one please? Thank you, Ladislav Lenart On 5.6.2013 14:03, Ladislav Lenart wrote: Hello. I've tried an experiment to verify that yield_per() with subqueryload() behaves as badly as you described, but according to my practical observation, it issues ONE subqueryload() and everything behaves as I would expect. It emits two SELECTs, one to fetch the objects and the second one to fetch the related data and that's about it, see below. So back to square one, what is wrong with it? Base = declarative_base() class Contact(Base): # Just a sketch, I hope you get the picture. id, name, phones = relationship(Phone) # many class Phone(Base): # Just a sketch, I hope you get the picture. id, number, contact = relationship(Contact) # one # Setup engine with echo set to True. phones = ['123456789', '987654321', '555777999'] for i in range(1, 11): c = Contact(name=u' '.join([u'Contact', unicode(i)])) session.add(c) session.add_all(Phone(contact=c, number=e) for e in phones) session.flush() session.expunge_all() q = session.query(Contact).options(subqueryload(Contact.phones)) for each in q.yield_per(2): print each.last_name for e in each.phones: print e The output is like this: SA info about all the inserts after session.flush(). SA info about select for contacts. SA info about select for their phones. Contact 10 123456789 987654321 555777999 Contact 9 123456789 987654321 555777999 Contact 8 123456789 987654321 555777999 Contact 7 123456789 987654321 555777999 Contact 6 123456789 987654321 555777999 Contact 5 123456789 987654321 555777999 Contact 4 123456789 987654321 555777999 Contact 3 123456789 987654321 555777999 Contact 2 123456789 987654321 555777999 Contact 1 123456789 987654321 555777999 Thank you, Ladislav Lenart On 5.6.2013 11:26, Ladislav Lenart wrote: Hello. I have one more question about my approach to WindowedRangeQuery: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Suppose I have a complex query (without options() / order_by()) to obtain the objects I want. I use this query to calculate window intervals. I don't have to use it again to fetch the objects, because I already have their id intervals. Am I right? Thank you, Ladislav Lenart On 4.6.2013 19:15, Ladislav Lenart wrote: On 4.6.2013 18:49, Michael Bayer wrote: On Jun 4, 2013, at 12:28 PM, Ladislav Lenart lenart...@volny.cz wrote: Absolutely, you should do whatever you have to in order to get the range you want, in fact the recipe even says this... Ok. What I want to do is basically the following: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Can this strategy work? there's not a publicly supported feature to reset the options right now so you'd probably need to apply them after you get your window ranging query. You can probably remove the effect of eager loaders if you were to just assign a blank _attributes dictionary to the new query though (just no long term guarantees). other than that, I use the windowing concept extensively and it works very well. Ok, I will try it. Thank you very much for your invaluable insights, Ladislav Lenart -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][SA 0.7.9] yield_per() vs. joinedload() and subqueryload()
the Query emitted by subqueryload does not use yield_per. so if your total result set is 1000 rows, and the total rows represented by all the collections is 1, the first time that query is emitted, 1 rows will be fully loaded and processed into memory at once. This would occur typically somewhere in the first few rows of your 50 -row yield_per batch. So the intent of yield_per, which is to conserve memory and upfront loading overhead, would be defeated entirely by this. The subqueryload is emitting once if I recall correctly because I probably at some point have the query result being memoized in the query context to prevent it from being emitted many times in a yield_per scenario. On Jun 5, 2013, at 10:20 AM, Ladislav Lenart lenart...@volny.cz wrote: Hello. One more note. I've just tried the below experiment with joinedload() instead of subqueryload() and that does NOT work just as you expected. One contact is returned several times and the first occurrences have incomplete phones. However my experiments with subqueryload() suggest that it works just fine with yield_per(). Can you elaborate on that one please? Thank you, Ladislav Lenart On 5.6.2013 14:03, Ladislav Lenart wrote: Hello. I've tried an experiment to verify that yield_per() with subqueryload() behaves as badly as you described, but according to my practical observation, it issues ONE subqueryload() and everything behaves as I would expect. It emits two SELECTs, one to fetch the objects and the second one to fetch the related data and that's about it, see below. So back to square one, what is wrong with it? Base = declarative_base() class Contact(Base): # Just a sketch, I hope you get the picture. id, name, phones = relationship(Phone) # many class Phone(Base): # Just a sketch, I hope you get the picture. id, number, contact = relationship(Contact) # one # Setup engine with echo set to True. phones = ['123456789', '987654321', '555777999'] for i in range(1, 11): c = Contact(name=u' '.join([u'Contact', unicode(i)])) session.add(c) session.add_all(Phone(contact=c, number=e) for e in phones) session.flush() session.expunge_all() q = session.query(Contact).options(subqueryload(Contact.phones)) for each in q.yield_per(2): print each.last_name for e in each.phones: print e The output is like this: SA info about all the inserts after session.flush(). SA info about select for contacts. SA info about select for their phones. Contact 10 123456789 987654321 555777999 Contact 9 123456789 987654321 555777999 Contact 8 123456789 987654321 555777999 Contact 7 123456789 987654321 555777999 Contact 6 123456789 987654321 555777999 Contact 5 123456789 987654321 555777999 Contact 4 123456789 987654321 555777999 Contact 3 123456789 987654321 555777999 Contact 2 123456789 987654321 555777999 Contact 1 123456789 987654321 555777999 Thank you, Ladislav Lenart On 5.6.2013 11:26, Ladislav Lenart wrote: Hello. I have one more question about my approach to WindowedRangeQuery: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Suppose I have a complex query (without options() / order_by()) to obtain the objects I want. I use this query to calculate window intervals. I don't have to use it again to fetch the objects, because I already have their id intervals. Am I right? Thank you, Ladislav Lenart On 4.6.2013 19:15, Ladislav Lenart wrote: On 4.6.2013 18:49, Michael Bayer wrote: On Jun 4, 2013, at 12:28 PM, Ladislav Lenart lenart...@volny.cz wrote: Absolutely, you should do whatever you have to in order to get the range you want, in fact the recipe even says this... Ok. What I want to do is basically the following: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Can this strategy work? there's not a publicly supported feature to reset the options right now so you'd probably need to apply them after you get your window ranging query. You can probably remove the effect of eager loaders
Re: [sqlalchemy] Re: Changing a value linked with a one-to-one relationship
On Jun 5, 2013, at 3:52 AM, Etienne Rouxel rouxel.etie...@gmail.com wrote: However, I get confused by SQLAlchemy trying to set taxon_id to NULL or even worse, please keep in mind that this was within an autoflush, in between where the state of your object graph in memory was complete. If the flush were to succeed, upon the next flush the row would be updated again to the correct value. This is why its important that autoflush run within a transaction. by SQLAlchemy trying to delete the plant if I configure a cascade='all, delete-orphan' on the backref side of the relationship. I understand that it might not be easy for SQLAlchemy to understand what I am trying to do, but the fact that I end up with two different results depending on whether or not taxon_old is previously loaded is confusing. one-to-one relationships have problems in this area, and I can also show you versions of your test where a single Taxon gets more than one Plant assigned to it, which would then return more than one row for that relationship (SQLAlchemy emits a warning when this condition is detected).That's really the main concern in this area - SQLAlchemy's load in this case is to detect this condition but it can be defeated. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][SA 0.7.9] yield_per() vs. joinedload() and subqueryload()
Ok, so yield_per() is useless when subqueryload() is used. Thank you, Ladislav Lenart On 5.6.2013 16:27, Michael Bayer wrote: the Query emitted by subqueryload does not use yield_per. so if your total result set is 1000 rows, and the total rows represented by all the collections is 1, the first time that query is emitted, 1 rows will be fully loaded and processed into memory at once. This would occur typically somewhere in the first few rows of your 50 -row yield_per batch. So the intent of yield_per, which is to conserve memory and upfront loading overhead, would be defeated entirely by this. The subqueryload is emitting once if I recall correctly because I probably at some point have the query result being memoized in the query context to prevent it from being emitted many times in a yield_per scenario. On Jun 5, 2013, at 10:20 AM, Ladislav Lenart lenart...@volny.cz wrote: Hello. One more note. I've just tried the below experiment with joinedload() instead of subqueryload() and that does NOT work just as you expected. One contact is returned several times and the first occurrences have incomplete phones. However my experiments with subqueryload() suggest that it works just fine with yield_per(). Can you elaborate on that one please? Thank you, Ladislav Lenart On 5.6.2013 14:03, Ladislav Lenart wrote: Hello. I've tried an experiment to verify that yield_per() with subqueryload() behaves as badly as you described, but according to my practical observation, it issues ONE subqueryload() and everything behaves as I would expect. It emits two SELECTs, one to fetch the objects and the second one to fetch the related data and that's about it, see below. So back to square one, what is wrong with it? Base = declarative_base() class Contact(Base): # Just a sketch, I hope you get the picture. id, name, phones = relationship(Phone) # many class Phone(Base): # Just a sketch, I hope you get the picture. id, number, contact = relationship(Contact) # one # Setup engine with echo set to True. phones = ['123456789', '987654321', '555777999'] for i in range(1, 11): c = Contact(name=u' '.join([u'Contact', unicode(i)])) session.add(c) session.add_all(Phone(contact=c, number=e) for e in phones) session.flush() session.expunge_all() q = session.query(Contact).options(subqueryload(Contact.phones)) for each in q.yield_per(2): print each.last_name for e in each.phones: print e The output is like this: SA info about all the inserts after session.flush(). SA info about select for contacts. SA info about select for their phones. Contact 10 123456789 987654321 555777999 Contact 9 123456789 987654321 555777999 Contact 8 123456789 987654321 555777999 Contact 7 123456789 987654321 555777999 Contact 6 123456789 987654321 555777999 Contact 5 123456789 987654321 555777999 Contact 4 123456789 987654321 555777999 Contact 3 123456789 987654321 555777999 Contact 2 123456789 987654321 555777999 Contact 1 123456789 987654321 555777999 Thank you, Ladislav Lenart On 5.6.2013 11:26, Ladislav Lenart wrote: Hello. I have one more question about my approach to WindowedRangeQuery: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Suppose I have a complex query (without options() / order_by()) to obtain the objects I want. I use this query to calculate window intervals. I don't have to use it again to fetch the objects, because I already have their id intervals. Am I right? Thank you, Ladislav Lenart On 4.6.2013 19:15, Ladislav Lenart wrote: On 4.6.2013 18:49, Michael Bayer wrote: On Jun 4, 2013, at 12:28 PM, Ladislav Lenart lenart...@volny.cz wrote: Absolutely, you should do whatever you have to in order to get the range you want, in fact the recipe even says this... Ok. What I want to do is basically the following: * Suppose a complex query q with join(...), filter(...) and options(...). * I need to create q2 from q such that: * It has no options. * Can I reset the options with q = q.options(None)? * It has select with the over by magic taken from WindowedRangeQuery recipe. * I know I can use with_entities() for this. * I will use q2 to obtain the window ranges. * I will iterate over the window ranges and apply each to the original q and execute it in a loop. Can this strategy work? there's not a publicly supported feature to reset the options right now so you'd probably need
Re: [sqlalchemy] Reliable way to read comments from database schema
On 06/04/2013 11:22 PM, Warwick Prince wrote: On 06/04/2013 10:46 PM, Michael Bayer wrote: There's a long standing ticket to add support for comments, at least at the DDL level. I don't think anyone has looked into what level of support we get from the various backends as far as reflection. So its something the library has room for, but it's an open item for now. The four-year-old ticket is http://www.sqlalchemy.org/trac/ticket/1546. On Jun 4, 2013, at 8:33 PM, Warwick Prince warwi...@mushroomsys.com wrote: Hi Michael I'm toying with the idea of embedding some metadata into the comments on columns and/or the table comment. Is there a way to reliably read the comment from the column definition via reflection across all dialects that would support a comment at a column level? Also, can I read the comment normally attached to the table definition? Thanks Warwick If this metadata will be used just in sqlalchemy (or python libraries up in the stack), you could use the info parameter when creating columns. OK - sounds promising. What actually is the 'info' that it reads/writes? i.e. where is it getting it from/putting it in the DB? (Or does it only reside in the Python code?) It is a dictionary and you can store whatever you want in there (please see http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#sqlalchemy.schema.SchemaItem.info for more info about it). This information is only available on Python code. Also, I'd like to +1 the task to reflect the comments with support for as many dialects as possible :-) We have developed a large platform using SQLA as the ORM, and as part of the maintenance area we have a generic database manager that is intended to allow maintenance, viewing, manual row insertion/deletion etc (Like MySQL Workbench et al) that is 100% database agnostic. Comments are missing at the moment which is a shame. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Including counts in query results
Am 04.06.2013, 04:57 Uhr, schrieb Rob Green robsgr...@gmail.com: Hello, I'm having some trouble getting my SQL query to work in SQLAlchemy. In this example, I want to find out the number of meeting rooms and bathrooms available to a user. The user is part of a userGroup, which is assigned to one building. The building has many meeting rooms and bathrooms. My sql query looks like: select user.id, (select count(*) from meetingRoom where userGroup.buildingCode = meetingRoom.buildingCode), (select count(*) from restroom where userGroup.buildingCode = restroom.buildingCode) from user join userGroup on user.userGroupId = userGroup.userGroupId I've tried using subqueries: meetingRoomCount = session.query(func.count(MeetingRoom.id)).join(UserGroup, MeeingRoom.buildingId == UserGroup.buildingId).subquery() bathroomCount = session.query(func.count(Bathroom.id)).join(UserGroup, Bathroom.buildingId == UserGroup.buildingId).subquery() session.query(User.id, meetingRoomCount, bathroomCount).first() But this returns the total number of meeting rooms and bathrooms in the database, not the ones that are specific to that user. I feel like I'm missing something simple here, anyone have any ideas? I think you might have more success if you explicitly alias your counts. SELECT user.id, mr.total, rr.total FROM user, (select count(*) AS total from meetingRoom where userGroup.buildingCode = meetingRoom.buildingCode) AS mr, (select count(*) AS total from restroom where userGroup.buildingCode = restroom.buildingCode) AS rr from user join userGroup on user.userGroupId = userGroup.userGroupId Charlie -- Charlie Clark Managing Director Clark Consulting Research German Office Kronenstr. 27a Düsseldorf D- 40217 Tel: +49-211-600-3657 Mobile: +49-178-782-6226 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Retrieve Dates as UTC
Was hunting around for how to do this, and couldn't find any examples, but eventually figured it out. Goal is to have all dates coming out of Postgres to be in UTC, regardless of the DBA's timezone configuration. Key piece of code is as follows: import sqlalchemy def set_utc_on_connect(dbapi_con, con_record): c = dbapi_con.cursor() c.execute(SET TIME ZONE UTC) c.close() sqlalchemy.event.listen(sqlalchemy.pool.Pool, 'connect', set_utc_on_connect) Now all connections in the pool will have their timezone set to UTC, so all queries will return UTC for datetime objects with timezone specified. Just posting it here incase someone else needed the info. Cheers, ~Owen -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Retrieve Dates as UTC
we do this (for PG and SQL Server):
class utcnow(expression.FunctionElement):
key = 'utcnow'
type = DateTime()
@compiles(utcnow, 'postgresql')
def _pg_utcnow(element, compiler, **kw):
return TIMEZONE('utc', CURRENT_TIMESTAMP)
@compiles(utcnow, 'mssql')
def _ms_utcnow(element, compiler, **kw):
return GETUTCDATE()
you then use utcnow() as your SQL expression.
just remembered i put it in the docs too:
http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#utc-timestamp-function
On Jun 5, 2013, at 4:51 PM, Owen M owenm...@gmail.com wrote:
Was hunting around for how to do this, and couldn't find any examples, but
eventually figured it out.
Goal is to have all dates coming out of Postgres to be in UTC, regardless of
the DBA's timezone configuration.
Key piece of code is as follows:
import sqlalchemy
def set_utc_on_connect(dbapi_con, con_record):
c = dbapi_con.cursor()
c.execute(SET TIME ZONE UTC)
c.close()
sqlalchemy.event.listen(sqlalchemy.pool.Pool, 'connect', set_utc_on_connect)
Now all connections in the pool will have their timezone set to UTC, so all
queries will return UTC for datetime objects with timezone specified.
Just posting it here incase someone else needed the info.
Cheers,
~Owen
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Connect to SQL Server (with pyodbc) getting error: sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
Hello World!
This is my first foray into python and SQL Alchemy, and I'm spinning my
wheels. I'm running the code below and am able to connect to my DB and
query data without error.
import pyodbc
cnxn = pyodbc.connect('DSN=py_test; Trusted_Connection=Yes')
However, when I try
import sqlalchemy
engine = sqlalchemy.create_engine('mssql+pyodbc://DSN=py_test;
Trusted_Connection=Yes')
result = engine.execute(SELECT * FROM dbo.test_table)
I receive the following error, I am running python 3.3 on 32 bit Windows 7
Enterprise
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 757, in
_do_get
return self._pool.get(wait, self._timeout)
File C:\Python33\lib\site-packages\sqlalchemy\util\queue.py, line 166,
in get
raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py,
line 80, in connect
return dialect.connect(*cargs, **cparams)
File C:\Python33\lib\site-packages\sqlalchemy\engine\default.py, line
285, in connect
return self.dbapi.connect(*cargs, **cparams)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (0)
(SQLDriverConnect)')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File
C:\Users\V114804\workspace\Remetrica_Simulated_ILC_WW_AP_20130520_ELT\Src\Test_DB_Connection.py,
line 11, in module
result = engine.execute(SELECT * FROM dbo.test_table)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line
1613, in execute
connection = self.contextual_connect(close_with_result=True)
File C:\Python33\lib\site-packages\sqlalchemy\engine\base.py, line
1661, in contextual_connect
self.pool.connect(),
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 272, in
connect
return _ConnectionFairy(self).checkout()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 425, in
__init__
rec = self._connection_record = pool._do_get()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 777, in
_do_get
con = self._create_connection()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 225, in
_create_connection
return _ConnectionRecord(self)
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 318, in
__init__
self.connection = self.__connect()
File C:\Python33\lib\site-packages\sqlalchemy\pool.py, line 368, in
__connect
connection = self.__pool._creator()
File C:\Python33\lib\site-packages\sqlalchemy\engine\strategies.py,
line 87, in connect
) from e
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC
Driver Manager] Data source name not found and no default driver specified
(0) (SQLDriverConnect)') None None
I have also tried:
import sqlalchemy
import pyodbc
def connect():
pyodbc.connect('DRIVER={SQL
Server};Server=SDAWWRMSD05;Database=ReMetrica_Results_201207;Trusted_Connection=Yes')
print('Connect Method Created')
created_engine = sqlalchemy.create_engine('mssql://', creator=connect)
created_result = created_engine.execute(SELECT * FROM dbo.test_table)
The definition is called, then the program hangs.
If anyone could please give me some advice on how to get around this that
would be great.
Thank you for your help, please let me know if I can provide any additional
information.
~Victor
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
