yeah, SA is producing the correct query but SQLite, when presented
with the subselect as a column, produces a fragmented column name
(different on mine than what your test comes up with, but still with
parenthesis and spaces in it). so you need to make a label out of
the select...I added a label() function to Select in rev 2115,
useable only with scalar=True.
now these testcases work:
# Test 1
sub = select([func.count('*')],
(orders_table.c.owner==users_table.c.user_id), scalar=True).label
('foo')
s = select([users_table, sub])
print list(s.execute())[0].keys()
# Test 2
sub = select([func.count('*')], (
(orders_table.c.owner==users_table.c.user_id) &
(orders_table.c.product=='milk')), scalar=True).label('foo')
s = select([users_table, sub])
print list(s.execute())[0].keys()
# Test 3
sub = select([func.count('*')], (
(orders_table.c.owner==users_table.c.user_id) &
(orders_table.c.product=='milk')), scalar=True).label('subcount')
s = select([users_table, sub])
print list(s.execute())[0].keys()
assert 'subcount' in list(s.execute())[0].keys()
On Nov 27, 2006, at 1:20 PM, Nadav Samet wrote:
> Thanks,
>
> I've attached a script (using in-memory sqlite db), that asserts
> the unexpected behaviour.
>
> Sincerely yours,
> Nadav
> >
> from sqlalchemy import *
>
> metadata = BoundMetaData('sqlite://')
>
> users_table = Table('users', metadata,
> Column('user_id', Integer, primary_key=True),
> Column('user_name', String(40)))
>
> orders_table = Table('orders', metadata,
> Column('order_id', Integer, primary_key=True),
> Column('product', String(40)),
> Column('owner', Integer, ForeignKey('users.user_id')))
>
> metadata.create_all()
>
> users_table.insert().execute(
> {'user_id': 1, 'user_name': 'me'},
> {'user_id': 2, 'user_name': 'you'})
>
> orders_table.insert().execute(
> {'product': 'coffee', 'owner': 1},
> {'product': 'milk', 'owner': 1},
> {'product': 'sugar', 'owner': 2})
>
> # Test 1
> sub = select([func.count('*')],
> (orders_table.c.owner==users_table.c.user_id), scalar=True)
> s = select([users_table, sub])
> assert 'user_id)' in list(s.execute())[0].keys()
>
> # Test 2
> sub = select([func.count('*')], (
> (orders_table.c.owner==users_table.c.user_id) &
> (orders_table.c.product=='milk')), scalar=True)
> s = select([users_table, sub])
> assert 'product = ?)' in list(s.execute())[0].keys()
>
> # Test 3
> sub = select([func.count('*').label('subcount')], (
> (orders_table.c.owner==users_table.c.user_id) &
> (orders_table.c.product=='milk')), scalar=True)
> s = select([users_table, sub])
> assert 'subcount' not in list(s.execute())[0].keys()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
