SqlAlchemy exists usage

Question

I have used exist to check the the existence of data in the database.

I have followed the answer by @icecrime in the following thread. SqlAlchemy Core and bare exists query

I am getting following error when EXISTS used as explained in the above thread.

*** 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]Statement(s) could not be prepared. (8180)") u'SELECT EXISTS (SELECT * \nFROM [ItemData] \nWHERE [ItemData].[name] = ?)' ('skv',)

I am using Sqlalchemy and following is the code I have used.

result=conn.execute(select([exists().where(ItemData.c.name == 'skv')]))

I have connected to the server using "mssql+pyodbc//" prefix. When I have worked on databases with sqlite:, it worked flawless, Is it something very specific to Microsoft sql database accessing?.

PS: I am new to sqlalchemy, apologies for my terminology if wrong.

Answer 1

To start, I tried running an equivalent query on my own SQL Server database:

SELECT EXISTS (SELECT * FROM [ItemData] WHERE [ItemData].[name] = 'skv')
Incorrect syntax near the keyword 'EXISTS'.

MSDN documentation shows that this use of EXISTS is not supported, so we go to the SQL Alchemy documentation and find that:

Note that some databases such as SQL Server don’t allow an EXISTS expression to be present in the columns clause of a SELECT. To select a simple boolean value based on the exists as a WHERE, use literal()