Dear Michael,
Appreciate for help me solving this problem.
Meanwhile I found something interesting, maybe worth to tell you.
Two alias object of same table with same name are treated as conflicts when
both using in query.
Error 1066, "Not unique table/alias is throwing.
Many regards,
Dong
===============================================
dataset = DB.db_tables[db_alias]['ProcessedDataset']
parent = dataset.alias('ProcessedDataset_ItsParent') # First Time
keylist = [parent.c.Name]
whereclause = dataset.c.Name.op('=')('Summer09-MC_31X_V3-v1')
query = select(keylist, whereclause)
dsparent = DB.db_tables[db_alias]['ProcDSParent']
parent = dataset.alias('ProcessedDataset_ItsParent') # Second Time
child_parent = dsparent.alias('ThisDataset_ItsParent')
r_join = dataset
r_join = r_join.join(child_parent, dataset.c.ID ==
child_parent.c.ThisDataset)
r_join = r_join.join(parent, child_parent.c.ItsParent == parent.c.ID)
print r_join
query.append_from(r_join)
print query
DB.execute(query)
===========================================
SELECT `ProcessedDataset_ItsParent`.`Name`
FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`, `ProcessedDataset`
INNER JOIN `ProcDSParent` AS `ThisDataset_ItsParent` ON
`ProcessedDataset`.`ID` = `ThisDataset_ItsParent`.`ThisDataset` INNER
JOIN `ProcessedDataset`
AS `ProcessedDataset_ItsParent` ON `ThisDataset_ItsParent`.`ItsParent` =
`ProcessedDataset_ItsParent`.`ID`
WHERE `ProcessedDataset`.`Name` = %s
Traceback (most recent call last):
File "testJoin.py", line 52, in <module>
DB.execute(query)
File
"/data/Python/lib/python2.6/site-packages/PyQB-1.0.0-py2.6.egg/pyquerybuilder/db/DBManager.py",
line 448, in execute
result = self.con.execute(query)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1405,
in execute
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1538,
in _execute_clauseelement
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1646,
in _execute_context
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1639,
in _execute_context
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line
330, in do_execute
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in
execute
File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in
defaulterrorhandler
sqlalchemy.exc.OperationalError: (OperationalError) (1066, "Not unique
table/alias: 'ProcessedDataset_ItsParent'") 'SELECT
`ProcessedDataset_ItsParent`.`Name` \nFROM `ProcessedDataset` AS
`ProcessedDataset_ItsParent`, `ProcessedDataset` INNER JOIN `ProcDSParent`
AS `ThisDataset_ItsParent` ON `ProcessedDataset`.`ID` =
`ThisDataset_ItsParent`.`ThisDataset` INNER JOIN `ProcessedDataset` AS
`ProcessedDataset_ItsParent` ON `ThisDataset_ItsParent`.`ItsParent` =
`ProcessedDataset_ItsParent`.`ID` \nWHERE `ProcessedDataset`.`Name` = %s'
('Summer09-MC_31X_V3-v1',)
On Wed, Mar 7, 2012 at 12:01 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Mar 6, 2012, at 1:22 AM, Liangd wrote:
>
> > --- Approach <1>:
> > * FromClause.join + expression.select
> > ---------------------------------------------------------------------
> >
> > r_join = dataset
> > r_join.join(child_parent, \
> > dataset.c.ID == child_parent.c.ThisDataset)
> > r_join.join(dsparent, \
> > child_parent.c.ItsParent == parent.c.ID)
> >
> > query = select(keylist, from_obj=r_join, whereclause=whereclause)
> > ---------------------------------------------------------------------
>
>
> the join() method returns a new Join object, that's the one which
> represents the join. The original object is unchanged:
>
> r_join = dataset
> r_join = r_join.join(child_parent, ...)
> r_join = r_join.join(dsparent, ...)
>
>
>
--
Liang Dong
Alias: lia...@cern.ch
From: Institute of High Energy Physics Chinese Academy of Sciences.
--
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.
