我无法理解如何执行查询来检查 sqlalchemy 中是否已存在匹配的记录。我可以在网上找到的大多数示例似乎都引用了我没有的“会话”和“查询”对象。
这是一个简短的完整程序,说明了我的问题:
1.使用“person”表设置内存中的sqlite数据库。
2. 向person表中插入两条记录。
3. 检查表中是否存在特定记录。这就是它呕吐的地方。
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)
该程序的输出是:
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>