On Fri, Jun 22, 2007 at 11:50:51AM -0400, Michael Bayer wrote:
> On Jun 22, 2007, at 9:59 AM, Christoph Haas wrote:
>
> > I'm trying to join a table with itself. That works well. However since
> > the column names are identical I had no luck accessing both the
> > original
> > and the joined information.
> >
> > I have aliased the tables already and run the join on the aliased
> > names.
> > But the column names are still not qualified.
> >
> > Example:
> >
> > records_a = model.records_table.alias('records_a')
> > records_ptr = model.records_table.alias('records_ptr')
> > joined = records_a.select(...,
> > from_obj=[outerjoin(records_a, records_ptr,
> > records_a.c.foo==records_ptr.c.bar)).execute().fetchone()
> >
> > The records contain fields like 'id', 'type' or 'name'. So I tried
> > this and failed:
> >
> > print joined[0].records_a.c.id
> > print joined[0]['records_a.id']
>
> that makes no sense. joined is a result of fetchone() so it
> therefore a RowProxy. joined[0] is the first column of the row. the
> value of joined[0] should be a scalar.
Sorry, you are right. I was pasting incorrectly. Here is a real-life session:
In [2]: records_a=model.records_table.alias('records_a')
In [3]: records_ptr=model.records_table.alias('records_ptr')
In [4]: result=records_a.select(records_a.c.type=='A',
from_obj=[model.outerjoin(records_a, records_ptr, (
(records_a.c.inet==records_ptr.c.inet) & (records_ptr.c.type=='PTR')
))]).execute().fetchone()
2007-06-22 18:09:57,852 INFO sqlalchemy.engine.base.Engine.0x..6c SELECT
records_a.id, records_a.domain_id, records_a.dhcpzone_id, records_a.name,
records_a.type, records_a.content, records_a.ttl, records_a.prio,
records_a.change_date, records_a.mac_address, records_a.inet
FROM records AS records_a LEFT OUTER JOIN records AS records_ptr ON
records_a.inet = records_ptr.inet AND records_ptr.type = %(records_ptr_type)s
WHERE records_a.type = %(records_a_type)s
2007-06-22 18:09:57,852 INFO sqlalchemy.engine.base.Engine.0x..6c
{'records_a_type': 'A', 'records_ptr_type': 'PTR'}
In [5]: result.keys()
Out[5]:
['id',
'domain_id',
'dhcpzone_id',
'name',
'type',
'content',
'ttl',
'prio',
'change_date',
'mac_address',
'inet']
As you see the keys are just there once. Although the actual SQL result
contains these columns for both records_a and the joined records_ptr.
> to see all the literal column names, use result.fetchone().keys().
> or, just turn on SQL echoing and watch the names generated. the
> easiest way to get at the column you want is to target using the
> Column objects themselves:
>
> result.fetchone()[records_a.c.id]
Does not work in the way I tried:
In [6]: result=records_a.select(records_a.c.type=='A',
from_obj=[model.outerjoin(records_a, records_ptr, (
(records_a.c.inet==records_ptr.c.inet) & (records_ptr.c.type=='PTR')
))]).execute().fetchone()[recordsd_a.c.type]
2007-06-22 18:11:38,571 INFO sqlalchemy.engine.base.Engine.0x..6c SELECT
records_a.id, records_a.domain_id, records_a.dhcpzone_id, records_a.name,
records_a.type, records_a.content, records_a.ttl, records_a.prio,
records_a.change_date, records_a.mac_address, records_a.inet
FROM records AS records_a LEFT OUTER JOIN records AS records_ptr ON
records_a.inet = records_ptr.inet AND records_ptr.type = %(records_ptr_type)s
WHERE records_a.type = %(records_a_type)s
2007-06-22 18:11:38,572 INFO sqlalchemy.engine.base.Engine.0x..6c
{'records_a_type': 'A', 'records_ptr_type': 'PTR'}
---------------------------------------------------------------------------
exceptions.NameError Traceback (most recent
call last)
/home/chaas/projekte/dnsdhcp/<ipython console>
NameError: name 'recordsd_a' is not defined
Kindly
Christoph
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
