How to translate to SA "select 1 from"

[werner]

I have one more variation on my translation stuff.

Trying to translate this sql:

select *
from MyTable M1
where
M1.LANG_CODE5 = 'FR_fr'
or (
M1.LANG_CODE5 = 'EN_en'
AND NOT EXISTS(
SELECT 1 FROM MyTable M2 where M2.CODE_ID = M1.CODE_ID and M2.LANG_CODE5 = 'FR_fr'
)
)

to SA, I can't figure out how to represent "select 1 from mytable".

May I ask again for some tips on this.

Thanks
Werner

[werner]

Forgot to include what I have so far:

s1 =
session.query(db.Somecode_T).filter(db.sa.or_(db.Somecode_T.lang_code5
== 'FR_fr',

db.sa.and_(db.Somecode_T.lang_code5 == 'EN_en',
db.sa.not_(db.sa.exists(
session.query(stAlias1).filter(db.sa.and_(
db.Somecode_T.code_id ==
stAlias1.code_id,
stAlias1.lang_code5 ==
'FR_fr')))))))

But the "limit(1) isn't what is needed :-[ .

So, I tried this:

s1 =
session.query(db.Somecode_T).filter(db.sa.or_(db.Somecode_T.lang_code5
== 'FR_fr',

db.sa.and_(db.Somecode_T.lang_code5 == 'EN_en',
db.sa.not_(db.sa.exists(
session.query(stAlias1.id).filter(db.sa.and_(
db.Somecode_T.code_id ==
stAlias1.code_id,
stAlias1.lang_code5 ==
'FR_fr')))))))

But here I get an exception:
"sqlalchemy.exc.InvalidRequestError: Scalar select can only be created
from a Select object that has exactly one column expression."

Werner
Werner

[werner]

I made a bit of progress, as I couldn't get the orm version to work I
tried creating it using select.

I used aliases and made two separate selects to better be able to debug
things.

a1 = db.Somecode_T.__table__.alias('a1')
a2 = db.Somecode_T.__table__.alias('a2')

sa = db.sa.select([a2.c.id], db.sa.and_(a2.c.code_id == a1.c.code_id,
a2.c.lang_code5 == 'FR_fr'))

s = db.sa.select([a1], db.sa.or_(a1.c.lang_code5 == 'FR_fr',
db.sa.and_(a1.c.lang_code5 == 'EN_en',
db.sa.not_(db.sa.exists(sa)))))

This gives the result I am looking for, so then I went back to the orm
version which I still can't get to work.

stAlias1 = db.sao.aliased(db.Somecode_T)
stAlias2 = db.sao.aliased(db.Somecode_T)

sa = session.query(stAlias2.id).filter(db.sa.and_(stAlias2.code_id ==
stAlias1.code_id,
stAlias2.lang_code5
== 'FR_fr'))

s1 = session.query(stAlias1).filter(db.sa.or_(stAlias1.lang_code5 ==
'FR_fr',

db.sa.and_(stAlias1.lang_code5 == 'EN_en',

db.sa.not_(db.sa.exists(sa)))))

The above gives me the following exception:

c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py:2901:
SAWarning: Column '(4,)' on table 'Select object' being replaced by
another column with the same key. Consider use_labels for select()
statements.
selectable.columns[c.name] = c
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py:2901:
SAWarning: Column '(6,)' on table 'Select object' being replaced by
another column with the same key. Consider use_labels for select()
statements.
selectable.columns[c.name] = c
Traceback (most recent call last):
File "saTest.py", line 116, in <module>
db.sa.not_(db.sa.exists(sa)))))
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 515, in exists
return _Exists(*args, **kwargs)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 2466, in __init__
s = select(*args, **kwargs).as_scalar().self_group()
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 3014, in as_scalar
return _ScalarSelect(self)
File
"c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\sql\expression.py",
line 3117, in __init__
raise exc.InvalidRequestError("Scalar select can only be created "

sqlalchemy.exc.InvalidRequestError: Scalar select can only be created
from a Select object that has exactly one column expression.

Can a kind soul point me in the right direction.

Werner