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.

Talvalin · Accepted Answer · 2014-12-26 11:48:48Z

3

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()

answered Dec 26, 2014 at 11:48

Talvalin

7,8972 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user26249 Over a year ago

Thanks for the answer with clarification. Can you please let me know how to use literal() in the above select statement. In the SQL documentation, example is given with session object and I am not able to convert/utilize that in my select statement. I have tried few variation at random but none worked. Ex: conn.execute(exists([select([literal(True)]).where(ItemData.c.name == 'skv')])) gives following error *** StatementError: Not an executable clause (original cause: ArgumentError: Not an executable clause)

Collectives™ on Stack Overflow

SqlAlchemy exists usage

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related