If you need to get the primary key value of a newly created object, you can do manually within the db_session() in order to get this value:

When is called, the object is saved only inside the current session. It means it will be persisted to the database after calling commit() manually (not necessary in most cases) or automatically before leaving the current database session.

Usually Pony saves objects in the database in the same order as they are created or modified. In some cases Pony can reorder SQL INSERT statements if this is required for saving objects. Let’s consider the following example:

from pony.orm import *
db = Database()
class TeamMember(db.Entity):
    name = Required(str)
    team = Optional('Team')
class Team(db.Entity):
    name = Required(str)
    team_members = Set(TeamMember)
db.bind('sqlite', ':memory:')
db.generate_mapping(create_tables=True)
set_sql_debug(True)
with db_session:
    john = TeamMember(name='John')
    mary = TeamMember(name='Mary')
    team = Team(name='Tenacity', team_members=[john, mary])

  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
)
CREATE TABLE "TeamMember" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "team" INTEGER REFERENCES "Team" ("id")
)

When Pony creates john, mary and team objects, it understands that it should reorder SQL INSERT statements and create an instance of the Team object in the database first, because it will allow using the team id for saving TeamMember rows:

Cyclic chains during saving objects

Now let’s say we want to have an ability to assign a captain to a team. For this purpose we need to add a couple of attributes to our entities: Team.captain and reverse attribute TeamMember.captain_of

class TeamMember(db.Entity):
    name = Required(str)
    team = Optional('Team')
    captain_of = Optional('Team')
class Team(db.Entity):
    name = Required(str)
    team_members = Set(TeamMember)
    captain = Optional(TeamMember, reverse='captain_of')

And here is the code for creating entity instances with a captain assigned to the team:

with db_session:
    john = TeamMember(name='John')
    mary = TeamMember(name='Mary')
    team = Team(name='Tenacity', team_members=[john, mary], captain=mary)

Why did it happen? Let’s see. Pony sees that for saving the john and mary objects in the database it needs to know the id of the team, and tries to reorder the insert statements. But for saving the object with the captain attribute assigned, it needs to know the id of mary object. In this case Pony cannot resolve this cyclic chain and raises an exception.

In order to save such a cyclic chain, you have to help Pony by adding the flush() command:

    john = TeamMember(name='John')
    mary = TeamMember(name='Mary')
    flush() # saves objects created by this moment in the database
    team = Team(name='Tenacity', team_members=[john, mary], captain=mary)

In this case, Pony will save the john and mary objects in the database first and then will issue SQL UPDATE statement for building the relationship with the team object:

INSERT INTO "TeamMember" ("name") VALUES (?)
[u'John']
INSERT INTO "TeamMember" ("name") VALUES (?)
[u'Mary']
INSERT INTO "Team" ("name", "captain") VALUES (?, ?)
[u'Tenacity', 2]
UPDATE "TeamMember"
SET "team" = ?
WHERE "id" = ?
[1, 2]
UPDATE "TeamMember"
SET "team" = ?
WHERE "id" = ?