我使用了 SQLAlchemy-Core 来处理您的查询。我将为简单的 Table
对象和使用 declarative_base
的模型提供示例。
给定以下演示代码:
from sqlalchemy import table, column, Unicode, String, Integer
user = table(
'user',
column('id', Integer),
column('name', Unicode),
column('email', String),
)
other = table(
'other',
column('id', Integer),
column('name', Unicode),
column('email', String),
)
如果您使用ORM,则定义可能如下:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Unicode, String
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column('name', Unicode)
email = Column('email', String)
class Other(Base):
__tablename__ = 'other'
id = Column(Integer, primary_key=True)
name = Column('name', Unicode)
email = Column('email', String)
user = User.__table__
other = Other.__table__
鉴于此,您可以像这样渲染查询:
from sqlalchemy import select, literal, exists, text
name = 'foo'
email = 'foo@example.com'
other_id = 1
print(
user
.insert()
.from_select(
['name', 'email'],
select(
[literal(name),
literal(email)])
.where(~exists([other.c.id])
.where(other.c.id > other_id))
)
.returning(text('id'))
)
编译查询将会给你下面的SQL字符串:
INSERT INTO "user" (name, email) SELECT :param_1 AS anon_1, :param_2 AS anon_2
WHERE NOT (EXISTS (SELECT other.id
FROM other
WHERE other.id > :id_1)) RETURNING id
Table
或声明性(ORM)类?换句话说,您是尝试使用核心还是ORM - SQLAlchemy实际上是两个库。 - Ilja Everilädeclarative_base
的类,但没有任何从“Table”继承的类(这进一步使我在文档中寻找时变得更加复杂)。典型的数据库交互调用类似于session.add(some_obj)
这样的东西-因此我认为那是ORM? - dwanderson