使用SQLite在SQLAlchemy中将内联查询结果分配给列

Question

我想在SQLAlchemy中从查询结果生成一个主键列。没错，我有理由不使用自动递增。

我有一个表：

-- Describe THERAPY
CREATE TABLE foo (
    pk INTEGER NOT NULL PRIMARY KEY,
    bar INTEGER
)

还有SQLAlchemy的ORM模型：

class Foo(Base):
    __tablename__ = 'foo'
    pk = Column(Integer, primary_key=True)
    bar = Column(Integer)

在SQLite中，这没问题，我可以用子查询简单插入：

insert into foo (pk) values ((select coalesce(max(foo.pk) + 1, 1) from foo));

但在SQLAlchemy中，我无法这样做：

foo = Foo()
foo.pk = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]).as_scalar()
session.add(foo)
session.commit()

这导致了堆栈跟踪中的错误：

InvalidRequestError: Instance <Foo at 0x224f710> cannot be refreshed - it's not  persistent and does not contain a full primary key.

把查询结果赋值给foo.bar是完全没问题的，结果也符合预期。

foo = Foo()
foo.pk = 1
foo.bar = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]).as_scalar()
session.add(foo)
session.commit()
# outputs (1, 1)
print (foo.bar, foo.pk)

Answer 1

结果在堆栈跟踪中显示： InvalidRequestError: 实例无法刷新 - 它不是持久的，并且不包含完整的主键。

我不想显得太较真，但这不是堆栈跟踪，而是一个错误信息。

如果我们能复现你的完整脚本，就能看到发生了什么。

首先，记得总是使用 echo=True，这样可以看到发生了什么。我们可以看到这个语句确实按计划工作：

INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + ?, ?) AS coalesce_1 

然而，错误在后面出现，伴随着这个部分的跟踪信息：

  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 389, in finalize_flush_changes
    self.session._register_newly_persistent(other)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1408, in _register_newly_persistent
    instance_key = mapper._identity_key_from_state(state)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2285, in _identity_key_from_state
    for col in self.primary_key
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 580, in get
    value = callable_(state, passive)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 423, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line 597, in load_scalar_attributes
    "contain a full primary key." % state_str(state))
sqlalchemy.exc.InvalidRequestError: Instance <Foo at 0x10165d510> cannot be refreshed - it's not  persistent and does not contain a full primary key.

我们可以看到 SQLAlchemy ORM，和所有的 ORM 一样，需要知道这个新插入对象的主键 - “状态中的身份键”。 SQLAlchemy 在这里的表现不太好，这里可以改进。

要理解整个情况其实挺复杂的，因为这里有很多细节，我会尽量列出来：

1. 通常情况下，这里是无法避免失败的。SQLite 不支持 RETURNING，所以我们在这种情况下获取值的唯一方法是通过 cursor.lastrowid。 cursor.lastrowid 通常只会给我们数据库生成的序列值。在 MySQL 中，我们需要使用 AUTOINCREMENT 列；否则从 lastrowid 返回的就是零。

2. 显然，根据一个简单的测试，最近的 pysqlite 在 cursor.lastrowid 中给我们提供了实际值。我从未见过这样的 DBAPI，SQLAlchemy 需要额外的改进来区分 lastrowid 只适用于自动增量值的数据库，以及实际上无论来源如何都能给你值的 lastrowid。目前还没有这个功能。

3. 如果我们在支持 RETURNING 的数据库上直接运行这个脚本，整个过程就能正常工作。这里是 Postgresql：

   INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + %(max_1)s, %(param_1)s) AS coalesce_1 
   FROM foo), %(bar)s) RETURNING foo.pk
   

4. 所以现在（2014年7月，注意 问题3133，针对 SQLAlchemy 1.0，已添加以允许使用这个值），对于 ORM 使用（需要主键，而 Core 不需要，因为没有对象需要跟踪），要在 lastrowid 后端使用这种模式，语句需要在 INSERT 之外调用。我们可以使用 ColumnDefault 来实现：

   class Foo(Base):
       __tablename__ = 'foo'
       pk = Column(Integer, primary_key=True)
       bar = Column(Integer)
   
    Foo.__table__.c.pk.default = ColumnDefault(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]).as_scalar())
   

我们将看到：

SELECT (SELECT coalesce(max(foo.pk) + ?, ?) AS coalesce_1 
FROM foo) AS anon_1
(1, 1)
INSERT INTO foo (pk, bar) VALUES (?, ?)
(1, None)

这将在任何后端上都能工作。

或者如果需要“每个实例”的系统，只需运行语句：

foo = Foo()
foo.pk = session.scalar(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]))
session.add(foo)
session.commit()