On Thursday 25 February 2016 03:50 PM, Simon King wrote:
On Thu, Feb 25, 2016 at 9:43 AM, Krishnakant <krm...@openmailbox.org
<mailto:krm...@openmailbox.org>> wrote:
Hello,
I have a query where there are 2 alias for a single table.
This is because the table contains a self referencing foreign key.
the table is (groupcode integer primary key, groupname text,
subgroupof integer foreign key references groupcode).
Now let's say I wish to have a 2 column query with groups and
their respective subgroups, I need to join the table to itself
making 2 aliases.
I know the raw query but need to do it through sqlalchemy core.
I don't use ORM for my project.and need this in the expression
language.
Something like this perhaps:
import sqlalchemy as sa
md = sa.MetaData()
t = sa.Table(
't', md,
sa.Column('groupcode', sa.Integer, primary_key=True),
sa.Column('groupname', sa.Text()),
sa.Column('subgroupof', sa.ForeignKey('t.groupcode')),
)
subgroup = t.alias('subgroup')
j = t.join(subgroup, subgroup.c.subgroupof == t.c.groupcode)
print sa.select([t.c.groupcode, subgroup.c.groupcode]).select_from(j)
Output:
SELECT t.groupcode, subgroup.groupcode
FROM t JOIN t AS subgroup ON subgroup.subgroupof = t.groupcode
Hope that helps,
Thanks a lot for the help.
I have one query.
do I not need to import alias? some thing like,
from sqlalchemy import alias
I tryed this and I get import error for the above mentioned line.
and the query you provided did not work without alias.
Can you help?
Happy hacking.
Krishnakant.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.