I am trying different solutions to my problem of getting default
languages back.
I have following statement:
Select
T1.id, T1.code_id, T1.lang_code5, T1.name
from
somecode_t T1
where
T1.lang_code5 = 'FR_fr'
UNION
Select
T2.id, T2.code_id, T2.lang_code5, T2.name
from
somecode_t T2
left outer join
somecode_t T3
on T2.code_id = T3.code_id
and
T3.lang_code5 = 'FR_fr'
where
T2.lang_code5 = 'EN_en'
and
T3.id is null;
Which I like to translate into SA and have come up with this:
stAlias1 = db.sao.aliased(db.Somecode_T)
q1 = session.query(stAlias1).filter(stAlias1.lang_code5=='FR_fr')
stAlias2 = db.sao.aliased(db.Somecode_T)
stAlias3 = db.sao.aliased(db.Somecode_T)
q2 = session.query(stAlias2).outerjoin((stAlias2,
stAlias2.code_id==stAlias3.code_id),
(stAlias3,
stAlias3.lang_code5=='FR_fr')
).filter(db.sa.and_(
stAlias2.lang_code5=='EN_en',
stAlias3.id==db.sa.null()))
q3 = q1.union(q2)
print q3
print '================'
print q3.all()
I am probably overusing "alias" but it makes it clearer to me to match
with the original sql.
However I am getting the following exception as "somecode_t_1" and
"somecode_t_2" use the same column aliases, what am I doing wrong that
this is happening?
Werner
Traceback (most recent call last):
File "saTest.py", line 102, in <module>
print q3.all()
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py",
line 1267, in all
return list(self)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py",
line 1361, in __iter__
return self._execute_and_instances(context)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py",
line 1364, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\session.py",
line 753, in execute
clause, params or {})
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py",
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py",
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py",
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py",
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py",
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-204,
'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -204\n
alias ANON_1 SOMECODE_T_2 conflicts with an alias in the same
statement') 'SELECT anon_1.id AS anon_1_id, anon_1.code_id AS
anon_1_code_id, anon_1.lang_code5 AS anon_1_lang_code5, anon_1.name AS
anon_1_name, anon_1.short_name AS anon_1_short_name, anon_1.created_at
AS anon_1_created_at, anon_1.updated_at AS anon_1_updated_at \nFROM
(SELECT somecode_t_1.id AS id, somecode_t_1.code_id AS code_id,
somecode_t_1.lang_code5 AS lang_code5, somecode_t_1.name AS name,
somecode_t_1.short_name AS short_name, somecode_t_1.created_at AS
created_at, somecode_t_1.updated_at AS updated_at \nFROM somecode_t
somecode_t_1 \nWHERE somecode_t_1.lang_code5 = ? UNION SELECT
somecode_t_2.id AS id, somecode_t_2.code_id AS code_id,
somecode_t_2.lang_code5 AS lang_code5, somecode_t_2.name AS name,
somecode_t_2.short_name AS short_name, somecode_t_2.created_at AS
created_at, somecode_t_2.updated_at AS updated_at \nFROM somecode_t
somecode_t_2 LEFT OUTER JOIN somecode_t somecode_t_2 ON
somecode_t_2.code_id = somecode_t_3.code_id LEFT OUTER JOIN somecode_t
somecode_t_3 ON somecode_t_3.lang_code5 = ? \nWHERE
somecode_t_2.lang_code5 = ? AND somecode_t_3.id IS NULL) anon_1'
['FR_fr', 'FR_fr', 'EN_en']
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
