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>

score 9 · Accepted Answer · answered Jun 05 '17 at 02:14

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

Thanks! This is exactly what I wanted! I was so close, I just couldn't figure out how to wrap that extra select on. Thank you. Only minor change I might make is: `result = conn.execute(s).scalar()` so we just get back the basic True / False instead of a result row. — sql_knievel, Jun 05 '17 at 02:24

score 1 · Answer 2 · answered Jun 05 '17 at 01:54

1

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.

answered Jun 05 '17 at 01:54

Sam Hartman

6,210
3
23
40

Thanks, Sam, but I would think there would have to be a better way. I don't want to transfer what could potentially be lots of matching rows from the db, I just want to transfer the true or false scalar value. – sql_knievel Jun 05 '17 at 01:56
Add a `limit 1` to your query. That's what I've seen a lot of web apps do. – Sam Hartman Jun 05 '17 at 02:06
"You can't have a exists as the outermost statement in an SQL query" Why not? According to https://www.postgresql.org/docs/current/functions-subquery.html and testing, of course you can! – auxsvr Jan 12 '23 at 14:04

score 0 · Answer 3 · answered Jun 05 '17 at 02:16

0

Try this instead:

...
s = person.select().where(person.c.name == "Bob")
s = select(exists(s))
print(s)
...

answered Jun 05 '17 at 02:16

orange

7,755
14
75
139

score 0 · Answer 4 · answered Jun 05 '17 at 02:21

Unless someone suggests a better answer, here's what I've come up with that works. Having the DB count the matching records and send just the count to the python app.

from sqlalchemy import select, func   # more imports not in my example code above

s = select([func.count(1)]).select_from(person).where(person.c.name == "Bob")
print(s)
record_count = conn.execute(s).scalar()
print("Matching records: ", record_count)

Example output:

SELECT count(:count_2) AS count_1 
FROM person 
WHERE person.name = :name_1
Matching records:  1

Executing a sqlalchemy exists query

4 Answers4

Linked

Related