CODEWITHSUNDEEP
pythonsqlalchemyexists
sql_knievel
sql_knievel

Reputation: 1411

Executing a sqlalchemy exists query

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>

Upvotes: 7

Views: 21746

Answers (4)

sql_knievel
sql_knievel

Reputation: 1411

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

Upvotes: 0

orange
orange

Reputation: 8090

Try this instead:

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

Upvotes: 0

clockwatcher
clockwatcher

Reputation: 3373

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

Upvotes: 12

Sam Hartman
Sam Hartman

Reputation: 6529

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.

Upvotes: 1

Related Questions