sqlalchemy 判断表是否存在,执行sqlalchemy存在查询

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

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:

解决方案

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