SqlAlchemy Core and bare exists query

Question

I have faced a problem where I have to find if the data exists in the table using SqlAlchemy Core.

I think the best way to do this query is to use exists method, which stops searching as soon as the first item is found. So, I crafted this version of query:

conn = self.db.connect()
query = exists().where(cookie_table.c.cookie_id == cookie_id)
result = conn.execute(query)

But it produces this error:

StatementError: Not an executable clause (original cause: ArgumentError: Not an
executable clause) 'EXISTS (SELECT * \nFROM cookie \nWHERE cookie.cookie_id = ?)' []

I tried modifying it a bit (combining with select), but to no avail.

So, eventually I came up with another solution, using limit(1), which works.

conn = self.db.connect()
query = select([1], cookie_table.c.cookie_id == cookie_id).limit(1)
result = conn.execute(query).fetchone()
return True if result is not None else False

I have two questions:

How the task can be achieved with exists method?

Is the query with limit as efficient as with exists?

Answer 1

According to the documentation, exists applies to a Select object, and the following examples are provided:

# use on an existing select()
s = select([table.c.col1]).where(table.c.col2==5)
s = exists(s)

# construct a select() at once
exists(['*'], **select_arguments).where(criterion)

# columns argument is optional, generates "EXISTS (SELECT *)"
# by default.
exists().where(table.c.col2==5)

What is going wrong with your code then?

Well, from my understanding, EXISTS is not a directive by itself, so trying to execute an exists() will fail as it is not an executable clause.

To illustrate, you can try with a simple sqlite console:

• EXISTS(SELECT * from t); is an error
• SELECT EXISTS(SELECT * FROM t); yields 0 or 1 as expected.

How to fix your issue?

Wrap your exists() in a select() executable statement:

result = conn.execute(select([exists().where(cookie_table.c.cookie_id == cookie_id)]))

It should work as expected:

>>> print select([exists().where(users.c.name=='test')])
SELECT EXISTS (SELECT *
FROM users
WHERE users.name = :name_1)

Now, should you use exists or limit? Frankly I have no idea, and I'm not even sure the answer doesn't depend on your database engine...