misleading documentation for query.exists()
Issue #3212
resolved
Using:
- Python 2.6.6
- sqlalchemy 0.9.7
- pyodbc 3.0.6
- Microsoft SQL Server 2008 (version 10.50.4000.0)
query.exists produces SELECT EXISTS (...) AS anon_1
, that's bad syntax for SQL Server. SQL Server only accepts it as part of a where clause, ie the equivalent query would be SELECT 1 WHERE EXISTS (...)
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Column, Integer
engine = sqlalchemy.create_engine('mssql+pyodbc://*****:*****@*****/*****')
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()
class User(Base):
__tablename__ = 'User'
id = Column('Id', Integer, primary_key=True)
session.query(session.query(User).exists()).scalar()
Traceback (most recent call last):
File "test.py", line 15, in <module>
session.query(session.query(User).exists()).scalar()
File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2400, in scalar
ret = self.one()
File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2369, in one
ret = list(self)
File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2412, in __iter__
return self._execute_and_instances(context)
File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 2427, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 729, in execute
return meth(self, multiparams, params)
File "c:\Python26\lib\site-packages\sqlalchemy\sql\elements.py", line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 826, in _execute_clauseelement
compiled_sql, distilled_params
File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 958, in _execute_context
context)
File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 1160, in _handle_dbapi_exception
exc_info
File "c:\Python26\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 951, in _execute_context
context)
File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 436, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'EXISTS'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'. (156)") u'SELECT EXISTS (SELECT 1 \nFROM [User]) AS anon_1' ()
Comments (5)
-
repo owner -
repo owner - changed component to documentation
- changed title to misleading documentation for query.exists()
- changed milestone to 0.9.8
-
repo owner - changed status to resolved
- clarify documentation on exists() that it is preferred to be in the
WHERE clause. fixes
#3212
→ <<cset 42837f4bca6a>>
-
repo owner - clarify documentation on exists() that it is preferred to be in the
WHERE clause. fixes
#3212
→ <<cset 98f457a5e2f8>>
- clarify documentation on exists() that it is preferred to be in the
WHERE clause. fixes
-
repo owner this works fine:
from sqlalchemy import Column, Integer, create_engine, literal from sqlalchemy.orm import Session from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True) Base.metadata.create_all(e) s = Session(e) s.add(A(id=5)) exists = s.query(A).exists() print s.query(literal(True)).filter(exists).scalar()
- Log in to comment
There's not a solution to this I can see other than documentation.
If you have an exists() construct, you can either as you say put it into a WHERE clause:
or you can SELECT from it directly, which SQL server doesn't allow even if the expression has no name; as you say, it won't even allow "select exists (select 1 from foo)".
So given that I don't see why you'd want to say
session.query(session.query(User).exists()).scalar()
? SQL server doesn't support that form. SQLAlchemy isn't in the business of designing the structure of SQL, you have to use the structure you want.I'll add an alternate form to the docstring.