[sqlalchemy] Applying an aggregate function to a select of a union_all (expr lang)
I have a pair of selects that I am using with union_all and I'd like
to have better control of aliasing so I can use sum on the column in
the outer select that is made up of the count() and literal columns
from the unioned selects.
s1 = select([count(), id, name], cat_id == 6, [join(item_tbl, aa_tbl,
Item.id == AA.item_id).join(grp_tbl, Group.id == Item.group_id)],
group_by=[Group.name, Group.id])
s2 = select([literal(0), id, name], cat_id == 6, group_by=[Group.name,
Group.id]
u1 = union_all(s1, s2).alias('group_with_zero').select(group_by=
['name','id'])
When I execute u1 I get the expect error that count _1 needs to be
part of the group by or used in an aggregate. Ideally I'd like the
outer select to be performing a sum() on that column. But I'm drawing
a blank in figuring out how to control the aliasing explicitly so I
can make that happen.
Am I going about this backwards or missing the obvious?
Thanks,
Wayne
Here is the SQL I hand crafted before I started building the expr
repr.
select id, name, sum(cnt) as total from
(
(select count(*) as cnt, groupname as name,
invgroups.groupid as id from invgroups
join invtypes on invtypes.groupid = invgroups.groupid
join aa on aa.type_id = invtypes.typeid
group by groupname, invgroups.groupid)
UNION ALL
(select 0 as cnt, groupname as name, groupid as id
from invgroups where categoryid = 6 group by groupname, groupid)
) as TT
group by TT.name, TT.id
order by TT.name
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
Wayne Witzel wrote:
I have a pair of selects that I am using with union_all and I'd like
to have better control of aliasing so I can use sum on the column in
the outer select that is made up of the count() and literal columns
from the unioned selects.
s1 = select([count(), id, name], cat_id == 6, [join(item_tbl, aa_tbl,
Item.id == AA.item_id).join(grp_tbl, Group.id == Item.group_id)],
group_by=[Group.name, Group.id])
s2 = select([literal(0), id, name], cat_id == 6, group_by=[Group.name,
Group.id]
u1 = union_all(s1, s2).alias('group_with_zero').select(group_by=
['name','id'])
When I execute u1 I get the expect error that count _1 needs to be
part of the group by or used in an aggregate. Ideally I'd like the
outer select to be performing a sum() on that column. But I'm drawing
a blank in figuring out how to control the aliasing explicitly so I
can make that happen.
Am I going about this backwards or missing the obvious?
whats count() here, do you mean func.count('*') ?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] timestamp as int
Hello,
What is the equivalent of the following but in the integer version.
Column('_last_updated', DateTime(True),
default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('_last_updated', Integer, default=int(time.time(), onupdate=func.???),
I see that project like trac uses the integer timestamps extensively.
Can it be used safely in any other project or there are some drawbacks
for it?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
Count needs to appear in the group by our be used in an aggregate
function, if I explictly add the literal 'count_1' as it is aliases to
the group by, the SQL executes, but then the count column is not sum'd
with the literal column and produces unwanted results.
What I need to do is sum the result of the union of the count and
literal columns in the outer select.
Wayne
On Jul 2, 11:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
Count needs to appear in the group by our be used in an aggregate
function, if I explictly add the literal 'count_1' as it is aliases to
the group by, the SQL executes, but then the count column is not sum'd
with the literal column and produces unwanted results.
What I need to do is sum the result of the union of the count and
literal columns in the outer select.
Wayne
On Jul 2, 11:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
Count needs to appear in the group by our be used in an aggregate
function, if I explictly add the literal 'count_1' as it is aliases to
the group by, the SQL executes, but then the count column is not sum'd
with the literal column and produces unwanted results.
What I need to do is sum the result of the union of the count and
literal columns in the outer select.
Wayne
On Jul 2, 11:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: timestamp as int
For mySQL you could use unix_timestamp()
For Postgres you could use date_part('epoch',now())
I've always used DateTime personally and have only ever reflected
tables that used Integer timestamps. So I can't speak to the drawbacks
or potential impacts with any authority. There may also be a more DB
portable solution for this as well.
On Jul 2, 12:52 pm, Lukasz Szybalski szybal...@gmail.com wrote:
Hello,
What is the equivalent of the following but in the integer version.
Column('_last_updated', DateTime(True),
default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('_last_updated', Integer, default=int(time.time(), onupdate=func.???),
I see that project like trac uses the integer timestamps extensively.
Can it be used safely in any other project or there are some drawbacks
for it?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.http://lucasmanual.com/mywiki/rsync
DataHub - create a package that gets, parses, loads, visualizes
datahttp://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: timestamp as int
If you define your column as DateTime, then SA will use the db's datetime
column.
If you want to store time in integer, i think you can just set the column to
integer, and set the column value as int(time.time())
- Didip -
On Thu, Jul 2, 2009 at 9:52 AM, Lukasz Szybalski szybal...@gmail.comwrote:
Hello,
What is the equivalent of the following but in the integer version.
Column('_last_updated', DateTime(True),
default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('_last_updated', Integer, default=int(time.time(),
onupdate=func.???),
I see that project like trac uses the integer timestamps extensively.
Can it be used safely in any other project or there are some drawbacks
for it?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
OK your example doesn't really provide enough information as to what the
problem is, the exact SQL you want to issue can be generated using the a
format as follows, perhaps you can derive the information you need from
it:
from sqlalchemy import *
from sqlalchemy.sql import table, column
invgroups = table('invgroups',
column('groupname'),
column('groupid'),
column('categoryid')
)
aa = table('aa', column('type_id'))
invtypes = table('invtypes',column('typeid'), column('groupid'))
s1 = select([
func.count('*').label('cnt'),
invgroups.c.groupname.label('name'),
invgroups.c.groupid.label('id')
]).select_from(
invgroups.join(invtypes, invgroups.c.groupid==invtypes.c.groupid).
join(aa, aa.c.type_id==invtypes.c.typeid)
).group_by(
invgroups.c.groupname, invgroups.c.groupid
)
s2 = select([
literal(0).label('cnt'),
invgroups.c.groupname.label('name'),
invgroups.c.groupid.label('id')
]).where(invgroups.c.categoryid==6).group_by(invgroups.c.groupname,
invgroups.c.groupid)
TT = s2.alias('TT')
u = union_all(s1, TT)
s = select([
u.c.id,
u.c.name,
func.sum(u.c.cnt).label('total')
]).group_by(TT.c.name, TT.c.id).order_by(TT.c.name)
print s
Wayne Witzel wrote:
Count needs to appear in the group by our be used in an aggregate
function, if I explictly add the literal 'count_1' as it is aliases to
the group by, the SQL executes, but then the count column is not sum'd
with the literal column and produces unwanted results.
What I need to do is sum the result of the union of the count and
literal columns in the outer select.
Wayne
On Jul 2, 11:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Applying an aggregate function to a select of a union_all (expr lang)
On Jul 2, 2:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
OK your example doesn't really provide enough information as to what the
problem is, the exact SQL you want to issue can be generated using the a
format as follows, perhaps you can derive the information you need from
it:
from sqlalchemy import *
from sqlalchemy.sql import table, column
invgroups = table('invgroups',
column('groupname'),
column('groupid'),
column('categoryid')
)
aa = table('aa', column('type_id'))
invtypes = table('invtypes',column('typeid'), column('groupid'))
s1 = select([
func.count('*').label('cnt'),
invgroups.c.groupname.label('name'),
invgroups.c.groupid.label('id')
]).select_from(
invgroups.join(invtypes, invgroups.c.groupid==invtypes.c.groupid).
join(aa, aa.c.type_id==invtypes.c.typeid)
).group_by(
invgroups.c.groupname, invgroups.c.groupid
)
s2 = select([
literal(0).label('cnt'),
invgroups.c.groupname.label('name'),
invgroups.c.groupid.label('id')
]).where(invgroups.c.categoryid==6).group_by(invgroups.c.groupname,
invgroups.c.groupid)
TT = s2.alias('TT')
u = union_all(s1, TT)
s = select([
u.c.id,
u.c.name,
func.sum(u.c.cnt).label('total')
]).group_by(TT.c.name, TT.c.id).order_by(TT.c.name)
print s
Wayne Witzel wrote:
Count needs to appear in the group by our be used in an aggregate
function, if I explictly add the literal 'count_1' as it is aliases to
the group by, the SQL executes, but then the count column is not sum'd
with the literal column and produces unwanted results.
What I need to do is sum the result of the union of the count and
literal columns in the outer select.
Wayne
On Jul 2, 11:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
Queb wrote:
On Jul 2, 10:39 am, Michael Bayer mike...@zzzcomputing.com wrote:
whats count() here, do you mean func.count('*') ?
Sorry, yes, count('*')
the error you're getting regards count needing to appear in the group
by, which suggests the count token you're issuing is incorrect.
Ahh this was exactly what I needed to see. Basically just craft a
select using the [union.c.column,...] , this let me apply the sum()
function to the union of the count(*) and literal columns and as long
as I give the columns matching labels in the selects the group by
works as intended.
Exactly what I needed. Trying to do .select off the union was the
wrong approach, as it was using the defined columns property at that
point. Building my own select and explictly setting the columns from
the union worked like a charm and will be noted for the future.
Thanks and sorry for the triple post early my G1 was acting up.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] 0.4 to 0.5 upgrade and 'One-to-many relation fails with unsaved, pending instance and is an orphan' error
In the process of upgrading from 0.4 to 0.5 I've come across a
troubling issue. With the following setup:
from sqlalchemy import Table, Column, Integer, String, MetaData,
create_engine, ForeignKey
from sqlalchemy.orm import relation, sessionmaker, scoped_session
engine = create_engine('sqlite:///mystuff.sqlite', echo=True)
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
metadata = MetaData()
mapper = Session.mapper
time_zones = Table('time_zones', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(35)))
contacts = Table('contacts', metadata,
Column('id', Integer, primary_key=True),
Column('display_as', String(35)),
Column('time_zone_id', Integer, ForeignKey
('time_zones.id')))
phone_numbers = Table('phone_numbers', metadata,
Column('id', Integer, primary_key=True),
Column('number', String(35)),
Column('contact_id', Integer, ForeignKey
('contacts.id')))
class TimeZone(object): pass
class Contact(object): pass
class PhoneNumber(object): pass
mapper(TimeZone, time_zones)
mapper(Contact, contacts, properties={
'time_zone': relation(TimeZone, backref='contacts'),
'phone_numbers': relation(PhoneNumber, backref='contact',
cascade='all, delete-orphan')
})
mapper(PhoneNumber, phone_numbers)
metadata.create_all(bind=engine)
##
Under 0.4 the following code executes fine:
c = Contact(display_as='Fake, User')
c.time_zone = TimeZone.query.filter_by(name='Africa/Algiers').first()
ph = PhoneNumber(full='1234567890')
c.phone_numbers.append(ph)
Session.commit()
But under 0.5 I receive an orphaned object error (see below for full
output). I understand that the TimeZone query causes a flush in
between the creation of the Contact and of the PhoneNumber. Without
the flush in between (if the TimeZone query line is removed) SA 0.5 is
correctly able execute the sample script.
As per this thread (http://groups.google.com/group/sqlalchemy/
browse_thread/thread/6c71c61bc59223f?tvc=2) I see that a suggested
remedy is to change the relation to cascade='all' rather than
cascade='all, delete-orphan'.
I would prefer not to do this as it really does make no sense in this
case to have a PhoneNumber without a Contact. I could also set the
relation via 'ph.contact = contact' but I would prefer to not have to
comb all of our existing code for this new class of bug.
What doesn't make sense to me is why 0.4 was able to correctly delay
the insert of the new phone number record until after the query for
the collection and now 0.5 can't.
Thank you for the assistance
-Brad
Below is the full echo output of 0.4 and 0.5
0.4.8dev_r5095:
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
BEGIN
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
INSERT INTO contacts (display_as, time_zone_id) VALUES (?, ?)
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
['Fake, User', None]
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
SELECT time_zones.id AS time_zones_id, time_zones.name AS
time_zones_name FROM time_zones WHERE time_zones.name = ? ORDER BY
time_zones.oid LIMIT 1 OFFSET 0
2009-07-02 18:51:17,391 INFO sqlalchemy.engine.base.Engine.0x..70
['Africa/Algiers']
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
SELECT phone_numbers.id AS phone_numbers_id, phone_numbers.number AS
phone_numbers_number, phone_numbers.contact_id AS
phone_numbers_contact_id FROM phone_numbers WHERE
phone_numbers.contact_id = ? ORDER BY phone_numbers.oid
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70 [4]
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
INSERT INTO phone_numbers (number, contact_id) VALUES (?, ?)
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
[None, 4]
2009-07-02 18:51:17,407 INFO sqlalchemy.engine.base.Engine.0x..70
COMMIT
0.5.4p2:
sa_test.py:8: SADeprecationWarning: The 'transactional' argument to
sessionmaker() is deprecated; use autocommit=True|False instead.
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True, bind=engine))
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info(time_zones)
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info(contacts)
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
PRAGMA table_info(phone_numbers)
2009-07-02 18:54:41,658 INFO sqlalchemy.engine.base.Engine.0x...2f30
()
2009-07-02 18:54:41,658 INFO
[sqlalchemy] Re: 0.4 to 0.5 upgrade and 'One-to-many relation fails with unsaved, pending instance and is an orphan' error
On Jul 2, 2009, at 7:21 PM, Brad Wells wrote:
But under 0.5 I receive an orphaned object error (see below for full
output). I understand that the TimeZone query causes a flush in
between the creation of the Contact and of the PhoneNumber. Without
the flush in between (if the TimeZone query line is removed) SA 0.5 is
correctly able execute the sample script.
As per this thread (http://groups.google.com/group/sqlalchemy/
browse_thread/thread/6c71c61bc59223f?tvc=2) I see that a suggested
remedy is to change the relation to cascade='all' rather than
cascade='all, delete-orphan'.
I would prefer not to do this as it really does make no sense in this
case to have a PhoneNumber without a Contact. I could also set the
relation via 'ph.contact = contact' but I would prefer to not have to
comb all of our existing code for this new class of bug.
What doesn't make sense to me is why 0.4 was able to correctly delay
the insert of the new phone number record until after the query for
the collection and now 0.5 can't.
its because accessing the lazy collection on c.phone_numbers doesn't
trigger autoflush in 0.4, while it does in 0.5. Below is a test case
which fails only on 0.4 due to this behavior. 0.5 IMO is the one
which is more correct, but its an unfortunate feature since this is
a frequent inconvenience. The issue demonstrated below is probably
not as common of a use case as that of just appending a non-
orphanable, pending object to a collection. But, an argument in favor
of 0.5's behavior is that the orphan error, while inconvenient,
complains loudly and is easily remedied (assuming one understands lazy
collections and autoflush), whereas the failure in 0.4 is silent.
a possible workaround would be to make the autoflush on the relation()
optional based on a configuration option, although that seems like yet
another obscure flag nobody would ever find out about and it also
dilutes the consistency of autoflush's behavior.
What I've advised to people in the past, and have also used myself, is
to temporarily disable autoflush on the session during code sections
that a lot of object manipulation is to occur, such as a form-to-model
population method in a web app. I made a decorator which
accomplishes this and it also fits nicely into a with: construct
(i.e. with no_autoflush():).
using a dynamic relation (i.e. lazy=dynamic) would also alleviate
this problem since those collections don't request data unless they
are iterated. dynamic relations are the style of collection most
other Python ORMs use in all cases.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///', echo=True)
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(30), nullable=False),
)
addresses = Table('addresses', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', None, ForeignKey('users.id')),
Column('email_address', String(50), nullable=False))
metadata.create_all(engine)
engine.execute(users.insert(),dict(id=8, name='ed'))
engine.execute(addresses.insert(),
[dict(id=x, user_id=y, email_address=z) for x, y, z in [
(2, 8, e...@wood.com),
(3, 8, e...@bettyboop.com),
(4, 8, e...@lala.com),
]])
class User(object):
pass
class Address(object):
pass
mapper(User, users, properties={
'addresses':relation(Address, order_by=addresses.c.email_address)
})
mapper(Address, addresses)
s = create_session(autoflush=True, transactional=True, bind=engine)
u = s.query(User).get(8)
ad2 = s.query(Address).get(2)
ad2.email_address = 'a'
assert [a.email_address for a in u.addresses] == ['a', 'e...@bettyboop.com
', 'e...@lala.com']
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: One-to-many relation fails with unsaved, pending instance and is an orphan
On May 5, 6:28 pm, Michael Bayer mike...@zzzcomputing.com wrote: the collection is lazy loaded from the database. autoflush occurs so that any pending values within the collection (items can be present without it being loaded) are persisted, so that you get the right results back. a specific example of why this feature is needed, and what you can do to workaround it, can be viewed at: http://groups.google.com/group/sqlalchemy/msg/595745ed046f1c24 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
