如何在 sqlalchemy 中使用子查询添加行?
问题描述
我正在使用带有 SQLAlchemy 的 Postgresql,但似乎 sqlalchemy 在使用子查询时无法添加行.
I'm using Postgresql with SQLAlchemy but it seems sqlalchemy is having trouble adding rows when using subqueries.
在我的示例中,我想为表中的特定标签更新计数器.
In my example, I want to update a counter for a specific tag in a table.
在 SqlAlchemy 中,测试运行类如下所示:
In SqlAlchemy a test run class would look like the following:
class TestRun( base ):
__tablename__ = 'test_runs'
id = sqlalchemy.Column( 'id', sqlalchemy.Integer, sqlalchemy.Sequence('user_id_seq'), primary_key=True )
tag = sqlalchemy.Column( 'tag', sqlalchemy.String )
counter = sqlalchemy.Column( 'counter', sqlalchemy.Integer )
插入代码应如下所示:
tag = 'sampletag'
counterquery = session.query(sqlalchemy.func.coalesce(sqlalchemy.func.max(TestRun.counter),0) + 1).
filter(TestRun.tag == tag).
subquery()
testrun = TestRun()
testrun.tag = tag
testrun.counter = counterquery
session.add( testrun )
session.commit()
这个问题是,它在运行这段代码时给出了一个非常有趣的错误,它正在尝试运行以下 SQL 查询:
The problem with this, is it gives a very interesting error when running this code, it's trying to run the following SQL Query:
'INSERT INTO test_runs (id, tag, counter)
VALUES (%(id)s,
%(tag)s,
SELECT coalesce(max(test_runs.counter), %(param_1)s) + %(coalesce_1)s AS anon_1
FROM test_runs
WHERE test_runs.tag = %(tag_1)s)'
{'coalesce_1': 1, 'param_1': 0, 'tag_1': 'mytag', 'tag': 'mytag', 'id': 267L}
这看起来很合理,只是在 SELECT 调用周围缺少括号.当我手动运行 SQL 查询时,它给了我与 sqlalchemy 给我的完全相同的错误,直到我手动输入括号来修复所有问题.似乎是一个不太可能的错误,sqlalchemy 在需要时会忘记放括号,所以我的问题是我是否缺少在使用 sqlalchemy 添加行时正确使用子查询的函数?
Which looks reasonable, except it's missing parenthesis around the SELECT call. When I run the SQL query manually it gives me the same exact error that sqlalchemy gives me until I type in the parenthesis manually which fixes everything up. Seems like an unlikely bug that sqlalchemy would forget to put parenthesis when it needs to, so my question is am I missing a function to use subqueries correctly when adding rows using sqlalchemy?
解决方案
而不是使用 subquery()
调用 as_scalar()
方法:
Instead of using subquery()
call as_scalar()
method:
返回此 Query 表示的完整 SELECT 语句,转换后到一个标量子查询.
Return the full SELECT statement represented by this Query, converted to a scalar subquery.
示例:
具有分类父子关系的模型:
Models with classing parent-child relationship:
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
counter = Column(Integer, nullable=False, default=0)
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(ForeignKey(Parent.id), nullable=False)
parent = relationship(Parent)
更新counter
字段的代码:
parent.counter = session.query(func.count(Child.id))
.filter_by(parent=parent).as_scalar()
产生的 SQL(从日志中复制):
Produced SQL (copied from the log):
UPDATE parents SET counter=(SELECT count(children.id) AS count_1
FROM children
WHERE ? = children.parent_id) WHERE parents.id = ?
相关文章