sqlalchemy flush()并获取ID?

问题:sqlalchemy flush()并获取ID?

我想做这样的事情:

f = Foo(bar='x')
session.add(f)
session.flush()

# do additional queries using f.id before commit()
print f.id # should be not None

session.commit()

但是f.idNone我尝试的时候。我怎样才能使它工作?

I want to do something like this:

f = Foo(bar='x')
session.add(f)
session.flush()

# do additional queries using f.id before commit()
print f.id # should be not None

session.commit()

But f.id is None when I try it. How can I get this to work?


回答 0

您的示例代码应该已经按原样工作。SQLAlchemy应该为其提供一个值f.id,并假设其为自动生成的主键列。主键属性在flush()生成时立即在过程中填充,并且不需要调用commit()。因此,这里的答案在于以下一项或多项:

  1. 映射的详细信息
  2. 如果使用的后端有任何奇怪现象(例如,SQLite不会为复合主键生成整数值)
  3. 打开echo时发出的SQL表示什么

Your sample code should have worked as it is. SQLAlchemy should be providing a value for f.id, assuming its an autogenerating primary-key column. Primary-key attributes are populated immediately within the flush() process as they are generated, and no call to commit() should be required. So the answer here lies in one or more of the following:

  1. The details of your mapping
  2. If there are any odd quirks of the backend in use (such as, SQLite doesn’t generate integer values for a composite primary key)
  3. What the emitted SQL says when you turn on echo

回答 1

我遇到了同样的问题,经过测试,我发现这些答案中没有一个是足够的。

当前,或者从sqlalchemy .6+开始,有一个非常简单的解决方案(我不知道它是否存在于以前的版本中,尽管我认为确实存在):

session.refresh()

因此,您的代码将如下所示:

f = Foo(bar=x)
session.add(f)
session.flush()
# At this point, the object f has been pushed to the DB, 
# and has been automatically assigned a unique primary key id

f.id
# is None

session.refresh(f)
# refresh updates given object in the session with its state in the DB
# (and can also only refresh certain attributes - search for documentation)

f.id
# is the automatically assigned primary key ID given in the database.

就是这样。

I’ve just run across the same problem, and after testing I have found that NONE of these answers are sufficient.

Currently, or as of sqlalchemy .6+, there is a very simple solution (I don’t know if this exists in prior version, though I imagine it does):

session.refresh()

So, your code would look something like this:

f = Foo(bar=x)
session.add(f)
session.flush()
# At this point, the object f has been pushed to the DB, 
# and has been automatically assigned a unique primary key id

f.id
# is None

session.refresh(f)
# refresh updates given object in the session with its state in the DB
# (and can also only refresh certain attributes - search for documentation)

f.id
# is the automatically assigned primary key ID given in the database.

That’s how to do it.


回答 2

谢谢大家 我通过修改列映射解决了我的问题。对我来说,autoincrement=True是必需的。

起源:

id = Column('ID', Integer, primary_key=True, nullable=False)

修改后:

id = Column('ID', Integer, primary_key=True, autoincrement=True, nullable=True)

然后

session.flush()  
print(f.id)

没关系!

Thanks for everybody. I solved my problem by modifying the column mapping. For me, autoincrement=True is required.

origin:

id = Column('ID', Integer, primary_key=True, nullable=False)

after modified:

id = Column('ID', Integer, primary_key=True, autoincrement=True, nullable=True)

then

session.flush()  
print(f.id)

is ok!


回答 3

与dpb给出的答案不同,不需要刷新。刷新后,您可以访问id字段,sqlalchemy会自动刷新在后端自动生成的id

我遇到了这个问题,经过一番调查后找出了确切的原因,我的模型是用id作为integerfield创建的,而在我的表单中,id用hiddenfield表示(因为我不想在表单中显示id)。默认情况下,隐藏字段表示为文本。一旦我使用widget = hiddenInput()将表单更改为integerfield,问题就解决了。

unlike the answer given by dpb, a refresh is not necessary. once you flush, you can access the id field, sqlalchemy automatically refreshes the id which is auto generated at the backend

I encountered this problem and figured the exact reason after some investigation, my model was created with id as integerfield and in my form the id was represented with hiddenfield( since i did not wanted to show the id in my form). The hidden field is by default represented as a text. once I changed the form to integerfield with widget=hiddenInput()) the problem was solved.


回答 4

我曾经0在调用session.add方法之前分配给ID时遇到问题。该ID已由数据库正确分配,但在之后的会话中未检索到正确的ID session.flush()

I once had a problem with having assigned 0 to id before calling session.add method. The id was correctly assigned by the database but the correct id was not retrieved from the session after session.flush().


回答 5

您应该尝试使用session.save_or_update(f)而不是session.add(f)

You should try using session.save_or_update(f) instead of session.add(f).