Executing a sqlalchemy exists query

Question

I'm having trouble understanding how to execute a query to check and see if a matching record already exists in sqlalchemy. Most of the examples I can find online seem to reference "session" and "query" objects that I don't have.

Here's a short complete program that illustrates my problem:
1. sets up in-memory sqlite db with "person" table.
2. inserts two records into the person table.
3. check if a particular record exists in the table. This is where it barfs.

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.sql.expression import exists

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

person = Table('person', metadata,
                        Column('id', Integer, primary_key=True),
                        Column('name', String(255), nullable=False))

metadata.create_all(engine)
conn = engine.connect()

s = person.insert()
conn.execute(s, name="Alice")
conn.execute(s, name="Bob")

print("I can see the names in the table:")
s = person.select()
result = conn.execute(s)
print(result.fetchall())

print('This query looks like it should check to see if a matching record exists:')
s = person.select().where(person.c.name == "Bob")
s = exists(s)
print(s)

print("But it doesn't run...")
result = conn.execute(s)

The output of this program is:

I can see the names in the table:
[(1, 'Alice'), (2, 'Bob')]
This query looks like it should check to see if a matching record exists:
EXISTS (SELECT person.id, person.name 
FROM person 
WHERE person.name = :name_1)
But it doesn't run...
Traceback (most recent call last):
  File "/project_path/db_test/db_test_env/exists_example.py", line 30, in <module>
    result = conn.execute(s)
  File "/project_path/db_test/db_test_env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/project_path/db_test/db_test_env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 265, in _execute_on_connection
    raise exc.ObjectNotExecutableError(self)
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: <sqlalchemy.sql.selectable.Exists object at 0x105797438>

Answer 1

The s.exists() is only building the exists clause. All you need to do to get your code to work is to generate a select for it.

s = exists(s).select()

Here's your full example:

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.sql.expression import exists

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

person = Table('person', metadata,
                        Column('id', Integer, primary_key=True),
                        Column('name', String(255), nullable=False))

metadata.create_all(engine)
conn = engine.connect()

s = person.insert()
conn.execute(s, name="Alice")
conn.execute(s, name="Bob")

print("I can see the names in the table:")
s = person.select()
result = conn.execute(s)
print(result.fetchall())

print('This query looks like it should check to see if a matching record exists:')
s = person.select().where(person.c.name == "Bob")
s = exists(s).select()
print(s)

print("And it runs fine...")
result = conn.execute(s)
print(result.fetchall())

Answer 2

exists is used in SQL subqueries. If you had a table posts containing blog post with an author_id, mapping back to people, you might use a query like the following to find people who had made a blog post:

select * from people where exists (select author_id from posts where author_id = people.id);

You can't have a exists as the outermost statement in an SQL query; it's an operator to use in SQL boolean clauses. So, SQLAlchemy is not letting you execute that query because it's not well-formed. If you want to see if a row exists, just construct a select statement with a where clause and see how many rows the query returns.