[sqlalchemy] Re: anyone have an improved WindowedRangeQuery ?
Try basing the interval query on a correlated subquery, so instead of
q = session.query(
column,
func.row_number().\
over(order_by=column).\
label('rownum')
).\
from_self(column)
try
subquery = base_query.subquery()q = session.query(
column,
func.row_number().\
over(order_by=column).\
label('rownum')
).\
join(subquery, subquery.columns[column.name] == column).\
from_self(column)
This should give a SQL statement of
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget JOIN (
SELECT widget.id AS id, widget.data AS data
FROM widget
WHERE widget.id :id_1) AS anon_2
ON anon_2.id = widget.id) AS anon_1
WHERE rownum % 1000=1
where myQuery is a filtered query on the widget table, in this example I
used the following to take the first half of the widget table
base_query = session.query(Widget).filter(Widget.id 5000)
For reference, the SQL statement in the original post is
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget) AS anon_1
WHERE rownum % 1000=1
On Tuesday, August 5, 2014 12:12:42 AM UTC+1, Jonathan Vanasco wrote:
I've been working on a migration script, and turned to my trusty friend
the WindowedRangeQuery recipe:
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery
I ran into a small problem and hoping someone else may have done this and
saved me some work.
I'm trying to work out some edge cases on a large table, and the default
performance is pretty bad in Postgres.
The two big issues are:
1. The initial query to create an iterable range is painful. It takes me
about 2 minutes. This seems to be due to:
a - loading a lot of data into memory
b - sequentially scanning the 'id' column. this function won't use
the primary key, or any other index, for id. it seq scans the whole table.
2. Window ranges are created for all ids in the table. If I only want to
work on half the records, with a min/max, the 'iterable' query doesn't use
the min/max filter values; it generates 100% of potential ranges based on
the id, and the filters are only used for the 'where' clause.
Has anyone worked out an improved version of this technique, or am I
cobbling something together myself?
--
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/d/optout.
Re: [sqlalchemy] anyone have an improved WindowedRangeQuery ?
Try basing the interval query on a correlated subquery, so instead of
q = session.query(
column,
func.row_number().\
over(order_by=column).\
label('rownum')
).\
from_self(column)
try
subquery = base_query.subquery()
q = session.query(
column,
func.row_number().\
over(order_by=column).\
label('rownum')
).\
join(subquery, subquery.columns[column.name] == column).\
from_self(column)
This should give a SQL statement of
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget JOIN (
SELECT widget.id AS id, widget.data AS data
FROM widget
WHERE widget.id :id_1) AS anon_2
ON anon_2.id = widget.id) AS anon_1
WHERE rownum % 1000=1
where base_query is a filtered query on the widget table, in this example I
used the following to take the first half of the widget table
base_query = session.query(Widget).filter(Widget.id 5000)
For reference, the SQL statement in the original post is
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget) AS anon_1
WHERE rownum % 1000=1
On Tuesday, August 5, 2014 5:15:06 PM UTC+1, Jonathan Vanasco wrote:
On Monday, August 4, 2014 11:59:36 PM UTC-4, Michael Bayer wrote:
ah because ROW_NUMBER() needs…the row number! that is, we need to count
the rows between PK values. Well if you have truly increment-by-one
primary keys and that’s what you’re windowing on, you can make that the
“rownum”…otherwise, you’re looking for windows of N rows within the whole
set of rows. Counting is necessary, I’m not sure how to force it to use an
index-only scan to figure this out. I haven’t looked into this that
deeply.
Yeah, I played with it for a while and dug deep into some psql listserves
and dba forums trying to trick it. i might jump on the postgres list for
some insight next.
add a WHERE clause to the query that is selecting the windows, that is
instead of:
i ended up cobbling one together to do that. though to eek out a bit of
better performance, i used a series of temp tables.
i was just wondering if anyone had pulled together a drop-in recipe yet
that inspected the base-query for any filters, and pushed that into the
'select' that generates the iterables.
--
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/d/optout.
Re: [sqlalchemy] anyone have an improved WindowedRangeQuery ?
Can do one better by just passing the filter expression.
table_filter = Widget.id 5000
passing table_filter as an optional parameter to column_windows, the
interval query becomes
q = session.query(
column,
func.row_number().\
over(order_by=column).\
label('rownum')
)if table_filter is not None:q =
q.filter(table_filter)
q = q.from_self(column)
This applies the WHERE clause directly without needing a JOIN
The SQL statement with the example table_filter above is
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget
WHERE widget.id :id_1) AS anon_1
WHERE rownum % 1000=1
or if table_filter is None:
SELECT anon_1.widget_id AS anon_1_widget_id
FROM (
SELECT widget.id AS widget_id, row_number() OVER (ORDER BY widget.id) AS
rownum
FROM widget) AS anon_1
WHERE rownum % 1000=1
--
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/d/optout.
Re: [sqlalchemy] Help producing query, making use of nested joins
For nesting the joins you have to use the join() construct manually, your
example is pretty verbose there, here's a shorter example.Note that
SQLAlchemy has two join() functions - one is sqlalchemy.sql.join, the other
is sqlalchemy.orm.join. The main thing you get from orm.join is that you can
use a relationship as the ON clause as in the example below.
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import Session, relationship, join
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
cs = relationship(C)
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
sess = Session()
j1 = join(B, C, B.cs)
q = sess.query(A).join(j1)
print q
On Aug 10, 2014, at 3:13 AM, mike waites mikey.wai...@googlemail.com wrote:
Hey!
I not sure if this is entirely possible in SQA and i'm more than open to
suggestions for ways to improve the query itself but im looking to produce
the following.
https://gist.github.com/mikeywaites/9e7290eaddcf003e4407
The thing in particular i'm having trouble with is the syntax for the nested
joins. The below doesn't work but i thought it would serve as a start point
for anyone able to help.
https://gist.github.com/mikeywaites/59476c0fd91301b53697
Some related material i've found:
https://bitbucket.org/zzzeek/sqlalchemy/issue/2120/support-nesting-of-joins-with-joined-eager
http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#right-nested-inner-joins-available-in-joined-eager-loads
So mostly its just the syntax of the nested joins im struggling with at the
moment. I can't quite nail the alias + nesting combinations!
Again, any suggestions on improving the query overall are more than welcome
too!!
Thanks!
--
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/d/optout.
--
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/d/optout.
Re: [sqlalchemy] Use a string or reference in ForeignKey?
On Aug 10, 2014, at 2:52 PM, alchemy1 veerukrish...@hotmail.com wrote:
I see that in ForeignKey I can use either a string or reference, so
ForeignKey(MyModel.id) or ForeignKey('my_model.id'). Any advantage to using
one or the other? What's the recommended approach?
whichever one is more convenient to the style in which you are setting up table
metadata is fine. The advantage of the string is that the target table
doesn't have to exist at the point at which you define the ForeignKey, which
eliminates the need to use imports for tables set up in other modules.
When using the string way, is it recommended to use the model name like
'MyModel.id' or table name like 'my_model.id'?
the strings here are intercepted by ForeignKey() which works at the level of
SQLAlchemy Core, that is, it doesn't know anything about mapped classes, just
table names that are present in the same MetaData collection.So table
names.The class names won't resolve right now (could be something we add
later though).
--
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/d/optout.
Re: [sqlalchemy] self-referential relationship and aliases
I've got the expression part of the hybrid properties working now, using an
alias:
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
alias = aliased(AffinionCode)
return select([func.count(alias.Rtid)]). \
where(alias.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
I was going to ask how to add aliasing to the relationship definitions, but
it seems like I don't have to. Awesome, thanks for your help.
On Sunday, August 10, 2014 7:08:43 PM UTC-5, Michael Bayer wrote:
it seems like you might want to make use of aliased() in those
@hybrid_properties you have; if you do an equijoin from
AffinionCode.something == cls.somethingelse, “cls” here is AffinionCode
again. One side should be an aliased() object.
On Aug 10, 2014, at 8:01 PM, dweitzenfeld dweitz...@gmail.com
javascript: wrote:
I'm having trouble getting a self-referential table to alias correctly.
In the example below, Jack was given a family code of 'FOO.' He shares
this with his family member Jill, who enters it as her PromoCode.
RtidPromoCodeFamilyCodeJackBARFOOJillFOO
This is my sqlalchemy class for the table:
class AffinionCode(MssqlBase):
__tablename__ = AffinionCodes
Rtid = Column(String, ForeignKey('MemberFacts.Rtid'), primary_key=True)
PromoCode = Column(String)
FamilyCode = Column(String)
ReferAFriendCode = Column(String)
family_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode == FamilyCode,
foreign_keys=FamilyCode,
remote_side=PromoCode,
backref='family_source')
friend_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode ==
ReferAFriendCode,
foreign_keys=ReferAFriendCode,
remote_side=PromoCode,
backref='friend_source')
@hybrid_property
def family_code_usage_count(self):
return len([_ for _ in self.family_code_usages])
@family_code_usage_count.expression
def family_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.FamilyCode).label('family_code_usage_count')
@hybrid_property
def friend_code_usage_count(self):
return len([_ for _ in self.friend_code_usages])
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
The problem is that this is producing queries like:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
AS family_code_usagesFROM [AffinionCodes]WHERE (SELECT COUNT([AffinionCodes].
[Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
1;
when I need it alias, like so:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode])
AS family_code_usagesFROM [AffinionCodes]WHERE (SELECT
COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode])
1;
Any help would be greatly appreciated, thanks!
--
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 tosqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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
Re: [sqlalchemy] self-referential relationship and aliases
I do have a follow up, actually.
If I wanted to make friend_code_usage_count a column property so that it
was always loaded with the object, how would I do that?
It doesn't look like I can add
alias = aliased(AffinionCode)
within the class definition. Where/how would I define the alias?
On Monday, August 11, 2014 12:56:01 PM UTC-5, dweitzenfeld wrote:
I've got the expression part of the hybrid properties working now, using
an alias:
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
alias = aliased(AffinionCode)
return select([func.count(alias.Rtid)]). \
where(alias.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
I was going to ask how to add aliasing to the relationship definitions,
but it seems like I don't have to. Awesome, thanks for your help.
On Sunday, August 10, 2014 7:08:43 PM UTC-5, Michael Bayer wrote:
it seems like you might want to make use of aliased() in those
@hybrid_properties you have; if you do an equijoin from
AffinionCode.something == cls.somethingelse, “cls” here is AffinionCode
again. One side should be an aliased() object.
On Aug 10, 2014, at 8:01 PM, dweitzenfeld dweitz...@gmail.com wrote:
I'm having trouble getting a self-referential table to alias correctly.
In the example below, Jack was given a family code of 'FOO.' He shares
this with his family member Jill, who enters it as her PromoCode.
RtidPromoCodeFamilyCodeJackBARFOOJillFOO
This is my sqlalchemy class for the table:
class AffinionCode(MssqlBase):
__tablename__ = AffinionCodes
Rtid = Column(String, ForeignKey('MemberFacts.Rtid'), primary_key=True)
PromoCode = Column(String)
FamilyCode = Column(String)
ReferAFriendCode = Column(String)
family_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode == FamilyCode,
foreign_keys=FamilyCode,
remote_side=PromoCode,
backref='family_source')
friend_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode ==
ReferAFriendCode,
foreign_keys=ReferAFriendCode,
remote_side=PromoCode,
backref='friend_source')
@hybrid_property
def family_code_usage_count(self):
return len([_ for _ in self.family_code_usages])
@family_code_usage_count.expression
def family_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.FamilyCode).label('family_code_usage_count')
@hybrid_property
def friend_code_usage_count(self):
return len([_ for _ in self.friend_code_usages])
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
The problem is that this is producing queries like:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
AS family_code_usagesFROM [AffinionCodes]WHERE (SELECT
COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
1;
when I need it alias, like so:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode])
AS family_code_usagesFROM [AffinionCodes]WHERE (SELECT
COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
WHERE [AffinionCodes1]. [PromoCode] = [AffinionCodes]. [FamilyCode])
1;
Any help would be greatly appreciated, thanks!
--
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 tosqlalchemy+...@googlegroups.com.
To post to this group, send email to
Re: [sqlalchemy] self-referential relationship and aliases
For a fancy column_property like that you likely need to define it after
the fact and attach it to the class. There's some new features I have
in the works for 1.0 to make that easier.
The general idea is:
class MyClass(Base):
# ...
alias = aliased(MyClass)
stmt = select([alias.foo]).where(alias.id ==
MyClass.other_id).correlate(MyClass).as_scalar()
MyClass.some_prop = column_property(stmt)
On 08/11/2014 02:03 PM, dweitzenfeld wrote:
I do have a follow up, actually.
If I wanted to make friend_code_usage_count a column property so that
it was always loaded with the object, how would I do that?
It doesn't look like I can add
alias = aliased(AffinionCode)
within the class definition. Where/how would I define the alias?
On Monday, August 11, 2014 12:56:01 PM UTC-5, dweitzenfeld wrote:
I've got the expression part of the hybrid properties working now,
using an alias:
|@friend_code_usage_count.expression
def friend_code_usage_count(cls):
alias = aliased(AffinionCode)
return select([func.count(alias.Rtid)]). \
where(alias.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
|
I was going to ask how to add aliasing to the relationship
definitions, but it seems like I don't have to. Awesome, thanks
for your help.
On Sunday, August 10, 2014 7:08:43 PM UTC-5, Michael Bayer wrote:
it seems like you might want to make use of aliased() in those
@hybrid_properties you have; if you do an equijoin from
AffinionCode.something == cls.somethingelse, “cls” here is
AffinionCode again. One side should be an aliased() object.
On Aug 10, 2014, at 8:01 PM, dweitzenfeld
dweitz...@gmail.com wrote:
I'm having trouble getting a self-referential table to alias
correctly.
In the example below, Jack was given a family code of 'FOO.'
He shares this with his family member Jill, who enters it as
her PromoCode.
Rtid PromoCode FamilyCode
Jack BAR FOO
Jill FOO
This is my sqlalchemy class for the table:
|class AffinionCode(MssqlBase):
__tablename__ = AffinionCodes
Rtid = Column(String, ForeignKey('MemberFacts.Rtid'),
primary_key=True)
PromoCode = Column(String)
FamilyCode = Column(String)
ReferAFriendCode = Column(String)
family_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode ==
FamilyCode,
foreign_keys=FamilyCode,
remote_side=PromoCode,
backref='family_source')
friend_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode ==
ReferAFriendCode,
foreign_keys=ReferAFriendCode,
remote_side=PromoCode,
backref='friend_source')
@hybrid_property
def family_code_usage_count(self):
return len([_ for _ in self.family_code_usages])
@family_code_usage_count.expression
def family_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.FamilyCode).label('family_code_usage_count')
@hybrid_property
def friend_code_usage_count(self):
return len([_ for _ in self.friend_code_usages])
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
|
The problem is that this is producing queries like:
|SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes].
[FamilyCode]) AS family_code_usages
FROM [AffinionCodes]
WHERE (SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes].
[FamilyCode]) 1;
|
Re: [sqlalchemy] self-referential relationship and aliases
cool, thanks again.
On Monday, August 11, 2014 1:19:30 PM UTC-5, Michael Bayer wrote:
For a fancy column_property like that you likely need to define it after
the fact and attach it to the class. There's some new features I have in
the works for 1.0 to make that easier.
The general idea is:
class MyClass(Base):
# ...
alias = aliased(MyClass)
stmt = select([alias.foo]).where(alias.id ==
MyClass.other_id).correlate(MyClass).as_scalar()
MyClass.some_prop = column_property(stmt)
On 08/11/2014 02:03 PM, dweitzenfeld wrote:
I do have a follow up, actually.
If I wanted to make friend_code_usage_count a column property so that it
was always loaded with the object, how would I do that?
It doesn't look like I can add
alias = aliased(AffinionCode)
within the class definition. Where/how would I define the alias?
On Monday, August 11, 2014 12:56:01 PM UTC-5, dweitzenfeld wrote:
I've got the expression part of the hybrid properties working now,
using an alias:
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
alias = aliased(AffinionCode)
return select([func.count(alias.Rtid)]). \
where(alias.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
I was going to ask how to add aliasing to the relationship definitions,
but it seems like I don't have to. Awesome, thanks for your help.
On Sunday, August 10, 2014 7:08:43 PM UTC-5, Michael Bayer wrote:
it seems like you might want to make use of aliased() in those
@hybrid_properties you have; if you do an equijoin from
AffinionCode.something == cls.somethingelse, “cls” here is AffinionCode
again. One side should be an aliased() object.
On Aug 10, 2014, at 8:01 PM, dweitzenfeld dweitz...@gmail.com wrote:
I'm having trouble getting a self-referential table to alias correctly.
In the example below, Jack was given a family code of 'FOO.' He shares
this with his family member Jill, who enters it as her PromoCode.
Rtid PromoCode FamilyCode Jack BAR FOO Jill FOO
This is my sqlalchemy class for the table:
class AffinionCode(MssqlBase):
__tablename__ = AffinionCodes
Rtid = Column(String, ForeignKey('MemberFacts.Rtid'), primary_key=True)
PromoCode = Column(String)
FamilyCode = Column(String)
ReferAFriendCode = Column(String)
family_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode == FamilyCode,
foreign_keys=FamilyCode,
remote_side=PromoCode,
backref='family_source')
friend_code_usages = relationship(AffinionCode,
primaryjoin=PromoCode ==
ReferAFriendCode,
foreign_keys=ReferAFriendCode,
remote_side=PromoCode,
backref='friend_source')
@hybrid_property
def family_code_usage_count(self):
return len([_ for _ in self.family_code_usages])
@family_code_usage_count.expression
def family_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.FamilyCode).label('family_code_usage_count')
@hybrid_property
def friend_code_usage_count(self):
return len([_ for _ in self.friend_code_usages])
@friend_code_usage_count.expression
def friend_code_usage_count(cls):
return select([func.count(AffinionCode.Rtid)]). \
where(AffinionCode.PromoCode ==
cls.ReferAFriendCode).label('friend_code_usage_count')
The problem is that this is producing queries like:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
AS family_code_usagesFROM [AffinionCodes]WHERE (SELECT
COUNT([AffinionCodes]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes]
WHERE [AffinionCodes]. [PromoCode] = [AffinionCodes]. [FamilyCode])
1;
when I need it alias, like so:
SELECT TOP 10 [AffinionCodes]. [Rtid] AS [AffinionCodes_Rtid],
[AffinionCodes]. [PromoCode] AS [AffinionCodes_PromoCode],
[AffinionCodes]. [FamilyCode] AS [AffinionCodes_FamilyCode],
[AffinionCodes]. [ReferAFriendCode] AS
[AffinionCodes_ReferAFriendCode],
(SELECT COUNT([AffinionCodes1]. [Rtid]) AS count_1
FROM [AffinionCodes] AS [AffinionCodes1]
[sqlalchemy] Trying to get tables to be created by unit tests
I have combined several examples I've found to try to get the 'transactional-style' of unit tests to work, where you roll back the database after each test. However when I run this, the test fails when trying to insert the object with DBSession.add, complaining that the tables don't exist. I thought Base.metadata.create_all(connection) would create the tables? I'd like to create the tables within the setup_module and roll it back in teardown_module so that the testdb database always goes back to being empty. This is to ensure that the tests are always running against a known state. (Start with empty db, create the tables, do the tests, then empty the db) Also, since a lot of copy-pasting was involved in creating this, could you please take a look and see what isn't necessary? I'm just trying to do simple tests (in Pyramid). For example is self.session = Session(connection) required? And is using the global variables the way I am a good way of doing it? Quite new to this so just trying to learn the best practices. from sqlalchemy.engine import create_engine from sqlalchemy.orm.session import Session from pyramid import testing from .models import Base from .models import DBSession transaction = None connection = None engine = None def setup_module(): global transaction, connection, engine engine = create_engine( 'postgresql+psycopg2://username:password@host:5432/testdb') DBSession.configure(bind=engine) connection = engine.connect() transaction = connection.begin() Base.metadata.create_all(connection) def teardown_module(): global transaction, connection, engine transaction.rollback() connection.close() engine.dispose() class DatabaseTest(object): def setup(self): self.__transaction = connection.begin_nested() self.session = Session(connection) def teardown(self): self.session.close() self.__transaction.rollback() class TestCustom(DatabaseTest): def test_passing_view(self): from .models import MyModel model = MyModel(name='one', value=55) DBSession.add(model) from .views import my_view request = testing.DummyRequest() info = my_view(request) assert info['one'].name == 'one' -- 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/d/optout.
Re: [sqlalchemy] Trying to get tables to be created by unit tests
On 08/11/2014 04:37 PM, alchemy1 wrote:
I have combined several examples I've found to try to get the
'transactional-style' of unit tests to work, where you roll back the
database after each test. However when I run this, the test fails when
trying to insert the object with DBSession.add, complaining that the
tables don't exist.
are you setting up the Connection in DBSession? I see you doing
something with self.session, but that is not the same session as
DBSession. If DBSession is a scoped session you'd want to say
DBSession(bind=connection) for each test.
I thought Base.metadata.create_all(connection) would create the
tables? I'd like to create the tables within the setup_module and roll
it back in teardown_module so that the testdb database always goes
back to being empty. This is to ensure that the tests are always
running against a known state. (Start with empty db, create the
tables, do the tests, then empty the db)
Also, since a lot of copy-pasting was involved in creating this, could
you please take a look and see what isn't necessary? I'm just trying
to do simple tests (in Pyramid). For example is |self.session
=Session(connection)required? And is using the global variables the
way I am a good way of doing it? Quite new to this so just trying to
learn the best practices.
|
|
fromsqlalchemy.engine importcreate_engine
fromsqlalchemy.orm.session importSession
frompyramid importtesting
from.models importBase
from.models importDBSession
transaction =None
connection =None
engine =None
defsetup_module():
globaltransaction,connection,engine
engine
=create_engine('postgresql+psycopg2://username:password@host:5432/testdb')
DBSession.configure(bind=engine)
connection =engine.connect()
transaction =connection.begin()
Base.metadata.create_all(connection)
defteardown_module():
globaltransaction,connection,engine
transaction.rollback()
connection.close()
engine.dispose()
classDatabaseTest(object):
defsetup(self):
self.__transaction =connection.begin_nested()
self.session =Session(connection)
defteardown(self):
self.session.close()
self.__transaction.rollback()
classTestCustom(DatabaseTest):
deftest_passing_view(self):
from.models importMyModel
model =MyModel(name='one',value=55)
DBSession.add(model)
from.views importmy_view
request =testing.DummyRequest()
info =my_view(request)
assertinfo['one'].name =='one'
|
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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/d/optout.
