Re: [sqlalchemy] SQLAlchemy 0.9.1 released
On Jan 8, 2014, at 2:39 AM, limodou limo...@gmail.com wrote: On Wed, Jan 8, 2014 at 3:31 PM, Wichert Akkerman wich...@wiggy.net wrote: On 08 Jan 2014, at 01:26, limodou limo...@gmail.com wrote: But I don't know why make this decision. Because where NULL will get nothing. And in 0.8.X version, I need to combine multiple condition according user input to one condition, so my code just like: cond = None for c in conditions: cond = c cond Why don’t you change the initial value to true() instead of None? If I read the documentation correctly that should work correctly in both SQLAlchemy versions. Even cond='' is correctly also, but I just think NULL is not a valid condition expression in SQL, so I think the old appoach maybe better. “WHERE NULL” is not valid, that’s true; hence neither is select.where(None) anymore, which is what would happen above if “conditions” were empty (and if conditions is guaranteed not empty, you could say “cond = conditions[0]; for c in conditions[1:]:…” ) The change includes that it’s safe to use “true()” explicitly and it will be folded in (i.e. not rendered) when used with “AND”. Some people were doing the above pattern that way anyway, now that way works on all backends. in any case it’s better to use and_(): cond = and_(*conditions) it’s less code and way fewer method calls internally. Also when you have “cond = c cond”, you end up with a structure like a (b (c (d e)))” which eventually will cause a recursion overflow when parsed, if there’s too many conditions. -- I like python! UliPad The Python Editor: http://code.google.com/p/ulipad/ UliWeb simple web framework: https://github.com/limodou/uliweb My Blog: http://my.oschina.net/limodou -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
“WHERE NULL” is not valid, that’s true; hence neither is
select.where(None) anymore, which is what would happen above if
“conditions” were empty (and if conditions is guaranteed not empty, you
could say “cond = conditions[0]; for c in conditions[1:]:…” )
The change includes that it’s safe to use “true()” explicitly and it will
be folded in (i.e. not rendered) when used with “AND”. Some people were
doing the above pattern that way anyway, now that way works on all backends.
in any case it’s better to use and_():
cond = and_(*conditions)
it’s less code and way fewer method calls internally. Also when you have
“cond = c cond”, you end up with a structure like a (b (c (d
e)))” which eventually will cause a recursion overflow when parsed, if
there’s too many conditions.
I think there are two things here:
1. Should None be converted to NULL when deal with condition None or
and_(condition, None)
2. How to combine multiple condition into one condition with and_
And I think the second question should be resolved by application itself,
we just need to obey some good guide, that's ok.
But for the first question, the old style I think None's behavior just like
true(), but in 0.9.x, is not. So this makes the uncompatible process. Here
is a test for 0.9.1:
print and_('id=3', None)
id=3 AND NULL
print and_('id=3', '')
id=3
print and_('id=3', true())
id=3
So empty string is the same as true(), and why empty string can be treated
as true() but None is treated as NULL? Commonly, python will treat None,
empty string are false boolean value, but here sqlalchemy does do like
that obviousely.
--
I like python!
UliPad The Python Editor: http://code.google.com/p/ulipad/
UliWeb simple web framework: https://github.com/limodou/uliweb
My Blog: http://my.oschina.net/limodou
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Using flask-sqlalchemy BaseQuery and Pagination with multiple tables.
Hi I can successfully use pagination with the following - mydata=Article.query.filter(Article.author_id==User.id).filter(User.id==g.user.id).paginate(page, POSTS_PER_PAGE, False) However, I need to fetch columns from multiple tables. In that case how can I modify the code above in order to use pagination? Here is what I need to do - mydata = db.session.query(id,title,Author).from_statement(\ SELECT a.id,a.title,u.author \ FROM article a, user u\ where a.user_id=u.id \ and u.id=:userid)\ .params(userid=g.user.id).all() However, with this , pagination does not work and I get an error - AttributeError: 'Query' object has no attribute 'paginate' Can you please help? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
sorry, this should read:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it as
follows, in which case it works the same in both versions:
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
On Jan 8, 2014, at 11:20 AM, Michael Bayer mike...@zzzcomputing.com wrote:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it as
follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I mentioned
earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
On Jan 8, 2014, at 7:54 AM, limodou limo...@gmail.com wrote:
I think there are two things here:
1. Should None be converted to NULL when deal with condition None or
and_(condition, None)
2. How to combine multiple condition into one condition with and_
And I think the second question should be resolved by application itself, we
just need to obey some good guide, that's ok.
But for the first question, the old style I think None's behavior just like
true(), but in 0.9.x, is not. So this makes the uncompatible process. Here is
a test for 0.9.1:
print and_('id=3', None)
id=3 AND NULL
print and_('id=3', '')
id=3
print and_('id=3', true())
id=3
So empty string is the same as true(), and why empty string can be treated as
true() but None is treated as NULL? Commonly, python will treat None, empty
string are false boolean value, but here sqlalchemy does do like that
obviously.
Here is a sample script using the code you gave. Your code is equally broken
in both 0.8 and 0.9, as if the list of conditions is empty, the same SELECT is
produced on both versions both of which are invalid with “WHERE NULL”:
from sqlalchemy.sql import select, column
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
return select([column('x')]).where(cond)
print my_select([])
0.8.4:
SELECT x
WHERE NULL
0.9.1:
SELECT x
WHERE NULL
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it as
follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I mentioned
earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
or preferably, just say and_(*conditions).
as for interpreting None as NULL, None has always been treated as NULL in a SQL
expression context - it is treated as NULL when used as a WHERE condition by
itself and it is treated as NULL when used in a comparison. 0.8 is
inconsistent that it is not treated as NULL when it happens to be part of an
AND:
from sqlalchemy.sql import select, column, literal
c = column('x')
print select([c]).where(c == 5) # 0.8 / 0.9: SELECT x WHERE x = :x_1
print select([c]).where(c == None) # 0.8 / 0.9: SELECT x WHERE x IS NULL
print select([c]).where(5”) # 0.8 / 0.9: SELECT x WHERE 5
print select([c]).where(None) # 0.8 / 0.9: SELECT x WHERE NULL
print select([c]).where((c == 5) 5”) # 0.8 / 0.9: SELECT x WHERE x = :x_1
AND 5
print select([c]).where((c == 5) None) # 0.8: SELECT x WHERE x = :x_1 #
0.9: SELECT x WHERE x = :x_1 AND NULL
The only thing that might be more appropriate than coercing where(None) and
where(x None) into NULL would be raising an error - because in fact
where(x) and where(expr x) already throws an exception if x is not a
SQL expression, string, or None/True/False (on both):
print select([c]).where(5) # 0.8 / 0.9 - raises exception
print select([c]).where(c 5) # 0.8 / 0.9 - raises exception
None also doesn’t act like true() in 0.8:
print select([c]).where(true()) # 0.8: SELECT x WHERE true
print select([c]).where(None) # 0.8: SELECT x WHERE NULL
so overall, this change is mentioned in the “Migration Guide” exactly because
it is in fact a behavioral change. You can argue it should be listed under
“Core Behavioral Changes” instead of “Behavioral Improvements” and I wouldn’t
have much issue with that, it is just listed under “Improvements” because it
doesn’t change the behavior of code that’s written correctly in the first place.
--
I like python!
UliPad The Python Editor: http://code.google.com/p/ulipad/
UliWeb simple web framework: https://github.com/limodou/uliweb
My Blog: http://my.oschina.net/limodou
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
geez..its 10 degrees here, sorry, just scratch that first case, it has to be
like this to be fully compatible both ways:
def my_select(conditions):
stmt = select([column('x')])
if conditions:
stmt = stmt.where(and_(*conditions))
return stmt
“cond None” was never any kind of publicly documented behavior and it was
inconsistent, sorry.
On Jan 8, 2014, at 11:22 AM, Michael Bayer mike...@zzzcomputing.com wrote:
sorry, this should read:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
On Jan 8, 2014, at 11:20 AM, Michael Bayer mike...@zzzcomputing.com wrote:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I mentioned
earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
a new section has been added as the first “Core Behavioral Change”:
http://sqlalchemy.readthedocs.org/en/rel_0_9/changelog/migration_09.html#none-can-no-longer-be-used-as-a-partial-and-constructor
On Jan 8, 2014, at 11:27 AM, Michael Bayer mike...@zzzcomputing.com wrote:
geez..its 10 degrees here, sorry, just scratch that first case, it has to be
like this to be fully compatible both ways:
def my_select(conditions):
stmt = select([column('x')])
if conditions:
stmt = stmt.where(and_(*conditions))
return stmt
“cond None” was never any kind of publicly documented behavior and it was
inconsistent, sorry.
On Jan 8, 2014, at 11:22 AM, Michael Bayer mike...@zzzcomputing.com wrote:
sorry, this should read:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
On Jan 8, 2014, at 11:20 AM, Michael Bayer mike...@zzzcomputing.com wrote:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I
mentioned earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
Typo:
when condition is non-empty
should be
when conditionS is non-empty
On Wed, Jan 8, 2014 at 1:53 PM, Michael Bayer mike...@zzzcomputing.com wrote:
a new section has been added as the first “Core Behavioral Change”:
http://sqlalchemy.readthedocs.org/en/rel_0_9/changelog/migration_09.html#none-can-no-longer-be-used-as-a-partial-and-constructor
On Jan 8, 2014, at 11:27 AM, Michael Bayer mike...@zzzcomputing.com wrote:
geez..its 10 degrees here, sorry, just scratch that first case, it has to be
like this to be fully compatible both ways:
def my_select(conditions):
stmt = select([column('x')])
if conditions:
stmt = stmt.where(and_(*conditions))
return stmt
“cond None” was never any kind of publicly documented behavior and it was
inconsistent, sorry.
On Jan 8, 2014, at 11:22 AM, Michael Bayer mike...@zzzcomputing.com wrote:
sorry, this should read:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
On Jan 8, 2014, at 11:20 AM, Michael Bayer mike...@zzzcomputing.com wrote:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I mentioned
earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
yeah…plus there’s no need for a “None” check when true() is used. I’m
supposed to be napping right now, this is the problem
On Jan 8, 2014, at 12:01 PM, Claudio Freire klaussfre...@gmail.com wrote:
Typo:
when condition is non-empty
should be
when conditionS is non-empty
On Wed, Jan 8, 2014 at 1:53 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
a new section has been added as the first “Core Behavioral Change”:
http://sqlalchemy.readthedocs.org/en/rel_0_9/changelog/migration_09.html#none-can-no-longer-be-used-as-a-partial-and-constructor
On Jan 8, 2014, at 11:27 AM, Michael Bayer mike...@zzzcomputing.com wrote:
geez..its 10 degrees here, sorry, just scratch that first case, it has to be
like this to be fully compatible both ways:
def my_select(conditions):
stmt = select([column('x')])
if conditions:
stmt = stmt.where(and_(*conditions))
return stmt
“cond None” was never any kind of publicly documented behavior and it was
inconsistent, sorry.
On Jan 8, 2014, at 11:22 AM, Michael Bayer mike...@zzzcomputing.com wrote:
sorry, this should read:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
On Jan 8, 2014, at 11:20 AM, Michael Bayer mike...@zzzcomputing.com wrote:
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I mentioned
earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Using flask-sqlalchemy BaseQuery and Pagination with multiple tables.
On Wed, Jan 8, 2014 at 3:37 PM, Mark S dbs...@gmail.com wrote:
Hi
I can successfully use pagination with the following -
mydata=Article.query.filter(Article.author_id==User.id).filter(User.id==g.user.id).paginate(page,
POSTS_PER_PAGE, False)
However, I need to fetch columns from multiple tables. In that case how can
I modify the code above in order to use pagination?
Here is what I need to do -
mydata = db.session.query(id,title,Author).from_statement(\
SELECT
a.id,a.title,u.author \
FROM article a, user u\
where a.user_id=u.id \
and u.id=:userid)\
.params(userid=g.user.id).all()
However, with this , pagination does not work and I get an error -
AttributeError: 'Query' object has no attribute 'paginate'
Can you please help?
There are a couple of options. One would be to configure your
db.session object to use the Flask-sqlalchemy query class, rather
than the default SQLAlchemy one. The Session class constructor has a
query_cls parameter for this purpose - you'd want to pass
flask_sqlalchemy.BaseQuery. I don't use Flask, so I don't know how
your session is currently being configured.
Another option would be to use the with_entities method of Query to
change the set of columns that are being queried for:
http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.with_entities
so you could write something like Article.query.with_entities('id',
'title', 'Author').from_statement(...)
But I guess what you are really trying to do is to query Articles, but
only to load certain columns. You might be interested in using
load_only instead:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#load-only-cols
which is part of a bigger topic about deferred column loading:
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] AssertionError When Removing Children For Association Object
Hello all, I keep hitting an assertion error, Dependency Rule Tried To Blank Out Primary Key... when trying to remove all children using an association object. My situation seems very similar to https://groups.google.com/forum/#!topic/sqlalchemy/3g4__pFHZTs However, based on Michaels response, it sounds like we must delete both objects, which I don't want to do since it is a many-many relationship. Below is a simple equivalent to my code: Page(Object): page_id = Column(Integer, primary_key = True) title = Column(String) user_relationships = relationship(Page_to_User) User(Object): user_id = Column(Integer, primary_key = True) name = Column(String) Page_to_User(Object): page_id = Column(Integer, ForeignKey(Page.page_id), primary_key = True) user_id = Column(Integer, ForeignKey(User.user_id), primary_key = True) relationship_type = (Integer, ForeignKey(Relationship.type_id), primary_key = True) page = relationship(Page) user = relationship(User) Assuming page1 object has many users tied to it, and I want to unassociate them all... print page1.user_relationships # populated with stuff, works as expected page1.user_relationships = [] session.flush() # error here My understanding is it page1.user_relationships is populated correctly due to the FK set on Page_to_User Association object. Somehow, it's getting the 'tried to blank out' error on the Page_to_User table... In the link above, Michael's write up sounds like the cause is if I try to delete the Page object, which references Page_to_User, which has foreign key to Page. It then tries to set page_id to null on Page_to_Actor due to FK constraints and ultimately fails. However, I'm not trying to delete the Page object here - just the associations to User. The Page stays. The User objects also stay. They just are not linked anymore... Can someone help explain why I still trigger this issue? I can make it go away setting viewonly=True on the user_relationships relationship() call, but I don't want it view only - I want to be able to update and work with those objects as usual. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] AssertionError When Removing Children For Association Object
On Jan 8, 2014, at 5:41 PM, Russell Holloway russ.d.hollo...@gmail.com wrote: Hello all, I keep hitting an assertion error, Dependency Rule Tried To Blank Out Primary Key... when trying to remove all children using an association object. My situation seems very similar to https://groups.google.com/forum/#!topic/sqlalchemy/3g4__pFHZTs However, based on Michaels response, it sounds like we must delete both objects, which I don't want to do since it is a many-many relationship. Below is a simple equivalent to my code: Page(Object): page_id = Column(Integer, primary_key = True) title = Column(String) user_relationships = relationship(Page_to_User) User(Object): user_id = Column(Integer, primary_key = True) name = Column(String) Page_to_User(Object): page_id = Column(Integer, ForeignKey(Page.page_id), primary_key = True) user_id = Column(Integer, ForeignKey(User.user_id), primary_key = True) relationship_type = (Integer, ForeignKey(Relationship.type_id), primary_key = True) page = relationship(Page) user = relationship(User) you need to put a cascade rule on Page.user_relationships, such that when you remove a Page_to_User from the collection, it’s marked as deleted, instead of SQLAlchemy setting the page_id foreign key to NULL, which is invalid here b.c. that column is part of the primary key (and hence the error). Page_to_User can’t exist in the database without being referred to by a Page object since the primary key would be NULL. the delete-orphan cascade is introduced at: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade and some more information at: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#deleting-from-collections -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Inverse the mapping in a composite association proxy
Hi, all. I've been trying to modify the example of a composite association
proxy
(http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html#composite-association-proxies)
to fit my needs.
In the documentation example, there is a User object, a Keyword object, and
a UserKeyword association object that stores a 'special_key' for each of a
user's keywords. In the provided example, the result is a collection of
dictionaries where the 'special_key' is the key and the 'keyword' is the
value. I'm trying to inverse that mapping.
In my particular use case (which I've simplified so as to make it as clear
as possible...I hope), I have a User object (a student), a Course object
(an academic course), and a UserCourse association object that stores each
user's grade for each course. My goal is to be able to set a student's
grade something like this:
user.course['math'] = 100
This is what I've come up with, but it (obviously) isn't working yet.
from sqlalchemy import Column, Integer, Text, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
# Columns
id = Column(Integer, primary_key=True)
name = Column(Text)
# Relations
courses = association_proxy(
'user_courses',
'course',
creator=lambda k, v: UserCourse(course=k, grade=v)
)
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
# Columns
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
# Composite association proxies linking users and preferences
class UserCourse(Base):
__tablename__ = 'user_courses'
# Columns
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
# Relations
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('grade'),
cascade='all, delete-orphan'
)
)
c = relationship('Course')
course = association_proxy('c', 'title')
I'd really appreciate anyone's help here, even if it's just showing me how
to modify the example in the documentation.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Inverse the mapping in a composite association proxy
OK well to do it exactly the way the example does it, each time we create a
UserCourse, it will also create a Course. That’s pretty simple, we use two
association proxies, one for User.courses and the other for UserCourse.course,
mappings are like this:
class User(Base):
__tablename__ = 'users'
# Columns
id = Column(Integer, primary_key=True)
name = Column(Text)
# Relations
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
# Columns
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
# Composite association proxies linking users and preferences
class UserCourse(Base):
__tablename__ = 'user_courses'
# Columns
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
# Relations
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
course_title = association_proxy(course, title”) # will create a new
Course object when course_title is set
def __init__(self, course_title, grade):
self.course_title = course_title
self.grade = grade
the other way that’s maybe a little more “real world” is that if two different
UserCourse objects are for “math”, we’d want only one Course object with
“math”. There’s a few ways to go about making those unique Course objects -
one common one is the “unique object” recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject .
A variant on that which I’ve been using lately doesn’t rely upon any kind of
global session and instead uses events.In this approach, we modify the
above so that UserCourse.course_title temporarily points to a plain string,
then when attached to a Session looks up and/or creates the unique Course
object, looks like this:
from sqlalchemy import event
# same User and Course...
# Composite association proxies linking users and preferences
class UserCourse(Base):
__tablename__ = 'user_courses'
# Columns
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
# Relations
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title # temporary, will turn into a
# Course when we attach to a Session
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by(
title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
with either of these, a simple test run is like:
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
s = Session(e)
user = User(name='u1')
s.add(user)
s.commit()
user.courses['math'] = 100
s.commit()
assert user.courses['math'] == 100
On Jan 8, 2014, at 6:32 PM, Brian Findlay brian.m.find...@gmail.com wrote:
Hi, all. I've been trying to modify the example of a composite association
proxy
(http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html#composite-association-proxies)
to fit my needs.
In the documentation example, there is a User object, a Keyword object, and a
UserKeyword association object that stores a 'special_key' for each of a
user's keywords. In the provided example, the result is a collection of
dictionaries where the 'special_key' is the key and the 'keyword' is the
value. I'm trying to inverse that mapping.
In my particular use case (which I've simplified so as to make it as clear as
possible...I hope), I have a User object (a student), a Course object (an
academic
Re: [sqlalchemy] Inverse the mapping in a composite association proxy
Well, if it isn't the man himself. Mike, you're awesome -- thanks for the hand-holding. Thanks for reading into my use case and providing the second example. Also, thanks for the thorough documentation (on SQLAlchemy and Mako). This would be infinitely more difficult without it. On another note (Mako-related), I'm sure I'm not the only one who would enjoy a follow-up to Better Form Generation with Mako and Pylons (http://techspot.zzzeek.org/2008/07/01/better-form-generation-with-mako-and-pylons/) for Pyramid users...grin. On Wednesday, January 8, 2014 7:44:00 PM UTC-5, Michael Bayer wrote: OK well to do it exactly the way the example does it, each time we create a UserCourse, it will also create a Course. That’s pretty simple, we use two association proxies, one for User.courses and the other for UserCourse.course, mappings are like this: -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.9.1 released
On Thu, Jan 9, 2014 at 12:20 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jan 8, 2014, at 7:54 AM, limodou limo...@gmail.com wrote:
I think there are two things here:
1. Should None be converted to NULL when deal with condition None or
and_(condition, None)
2. How to combine multiple condition into one condition with and_
And I think the second question should be resolved by application itself,
we just need to obey some good guide, that's ok.
But for the first question, the old style I think None's behavior just
like true(), but in 0.9.x, is not. So this makes the uncompatible process.
Here is a test for 0.9.1:
print and_('id=3', None)
id=3 AND NULL
print and_('id=3', '')
id=3
print and_('id=3', true())
id=3
So empty string is the same as true(), and why empty string can be treated
as true() but None is treated as NULL? Commonly, python will treat None,
empty string are false boolean value, but here sqlalchemy does do like
that obviously.
Here is a sample script using the code you gave. Your code is equally
broken in both 0.8 and 0.9, as if the list of conditions is empty, the same
SELECT is produced on both versions both of which are invalid with “WHERE
NULL”:
from sqlalchemy.sql import select, column
def my_select(conditions):
cond = None
for c in conditions:
cond = c cond
return select([column('x')]).where(cond)
print my_select([])
0.8.4:
SELECT x
WHERE NULL
0.9.1:
SELECT x
WHERE NULL
But I'm not talking about empty condition, but condition None. In
application, I can test if the condition is None and don't execute sql at
all.
Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
as follows, in which case it works the same in both versions:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
stmt = select([column(‘x’)])
if cond is not None:
stmt = stmt.where(cond)
return stmt
or you assume that “conditions” is non-empty, in which case, as I
mentioned earlier, do this:
def my_select(conditions):
cond = conditions[0]
for c in conditions[1:]:
cond = c cond
return select([column('x')]).where(cond)
or preferably, just say and_(*conditions).
This thing is about how to deal with condition combination, if there is
None value, above code is still not right. So the correct code maybe need
add some test like if c is None:.
And my point is not mainly about how to write correct condition combine,
but the which the right way to convert None.
as for interpreting None as NULL, None has always been treated as NULL in
a SQL expression context - it is treated as NULL when used as a WHERE
condition by itself and it is treated as NULL when used in a comparison.
0.8 is inconsistent that it is not treated as NULL when it happens to be
part of an AND:
Yes, I saw the code about 0.8.X and 0.9.1, the None convertion are the
same. But difference between them is in AND process. So this inconsistent
that you mean it's a bug in 0.8?
from sqlalchemy.sql import select, column, literal
c = column('x')
print select([c]).where(c == 5) # 0.8 / 0.9: SELECT x WHERE x = :x_1
print select([c]).where(c == None) # 0.8 / 0.9: SELECT x WHERE x IS NULL
print select([c]).where(5”) # 0.8 / 0.9: SELECT x WHERE 5
print select([c]).where(None) # 0.8 / 0.9: SELECT x WHERE NULL
print select([c]).where((c == 5) 5”) # 0.8 / 0.9: SELECT x WHERE x =
:x_1 AND 5
print select([c]).where((c == 5) None) # 0.8: SELECT x WHERE x = :x_1
# 0.9: SELECT x WHERE x = :x_1 AND NULL
The only thing that might be more appropriate than coercing where(None)
and where(x None) into NULL would be raising an error - because in fact
where(x) and where(expr x) already throws an exception if x is not
a SQL expression, string, or None/True/False (on both):
I think raise exception maybe better, so that it'll let user to know what
wrong with the condition. Otherwise some code like condition None can run
in 0.8.X very well, but in 0.9 it'll only return nothing without any error
thrown at all. It will break the old code.
print select([c]).where(5) # 0.8 / 0.9 - raises exception
print select([c]).where(c 5) # 0.8 / 0.9 - raises exception
None also doesn’t act like true() in 0.8:
print select([c]).where(true()) # 0.8: SELECT x WHERE true
print select([c]).where(None) # 0.8: SELECT x WHERE NULL
so overall, this change is mentioned in the “Migration Guide” exactly
because it is in fact a behavioral change. You can argue it should be
listed under “Core Behavioral Changes” instead of “Behavioral Improvements”
and I wouldn’t have much issue with that, it is just listed under
“Improvements” because it doesn’t change the behavior of code that’s
written correctly in the first place.
Or the doc add the inconsistant about condition None maybe the better.
Thank you very much.
--
I like
Re: [sqlalchemy] Inverse the mapping in a composite association proxy
Mike,
It took a few hours to wrap my head around your work and adapt it to my
actual use case, but it's working great now...except for a particular case
when used with templates.
Basically, I'm querying for relevant courses and then iterating over the
results to construct a form for grade entry, similar to this:
% for course in courses:
label${course}/label
input name=${course} value=${user.courses[course]}/
% endfor
This works well when I've already created a record linking a student and
course in the 'user_courses' association table (i.e.,
user.courses['somecourse'] exists), but I can't use this construct *in my
template* to set a grade for a course that I haven't already associated
with the student (because user.courses['newcourse'] doesn't at the time the
user object is passed to Mako).
This is precisely the scenario you used for testing, so I know it's not an
issue with the SQLAlchemy schema. Hard-coding it works, also (i.e. in the
controller, setting user.courses['newcourse'] to the value captured from a
form submission).
How would you recommend tackling this? Should it be addressed in the
controller, the template, or perhaps in the schema (__init__ or a
listener?)?
Thanks again.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
