Peewee调用save()后对象主键id未自动填充问题求助

Hey there, let's work through this issue where your Peewee model's primary key isn't being populated after calling save() with your custom SQLRelay database driver.

问题背景 #

You're building a SQLRelay database driver for Peewee, but after calling save() on a new model instance, both uncle_bob.id and uncle_bob._pk remain None. You've checked Peewee's source code and noticed that _WriteQuery.execute() should set the _pk attribute, but it's not working—your hunch about a cursor implementation issue is right on track.

示例代码 #

Here's the code you provided that demonstrates the problem:

from datetime import date
from peewee import BooleanField
from peewee import CharField
from peewee import DateField
from peewee import ForeignKeyField
from peewee import IntegerField
from peewee import Model
from SQLRelay import PySQLRDB
from sqlrelay_ext import SQLRelayDatabase

DB = SQLRelayDatabase('test2', host='<host>', user='<un>', password='<pwd>')

class BaseModel(Model):
    class Meta:
        database = DB

class Person(BaseModel):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

class Pet(BaseModel):
    owner = ForeignKeyField(Person, backref='pets')
    name = CharField()
    animal_type = CharField()

DB.connect()
Person.create_table(safe=False)
Pet.create_table(safe=False)

uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
uncle_bob.save()  # bob is now stored in the database
print('Uncle Bob id: {}'.format(uncle_bob.id))
print('Uncle Bob _pk: {}'.format(uncle_bob._pk))

排查思路 #

Let's break down the steps to diagnose and fix this:

• Check last_insert_id implementation: Peewee relies on the database driver's last_insert_id method to retrieve the auto-generated primary key after an INSERT. If this method isn't properly implemented for SQLRelay/SQL Server, Peewee can't get the ID to populate the model's attributes.
• Validate cursor functionality: Test if your SQLRelay cursor can correctly execute SELECT SCOPE_IDENTITY() and return results. Manually run this query right after an INSERT to confirm it returns the correct ID—if the cursor's fetchone() isn't working as expected, that's a key issue.
• Debug the execution flow: Add print statements or logging to your last_insert_id method to confirm it's being called by Peewee. Check if any exceptions are being silently caught that prevent the ID from being returned.
• Verify table schema: Ensure the Person table in your SQL Server database has an auto-incrementing primary key. Since you didn't define one explicitly, Peewee should create an id INT IDENTITY(1,1) column—but double-check that the table creation actually resulted in this schema.
• Confirm save() is doing an INSERT: If the model instance had an id set (even implicitly), save() would perform an UPDATE instead of an INSERT, which won't generate a new ID. Confirm uncle_bob.id is None before calling save().

解决方案 #

For SQL Server, the correct way to retrieve the last inserted auto-increment ID is using SCOPE_IDENTITY(), which returns the ID generated in the current scope (avoids issues with triggers). You need to implement this in your SQLRelayDatabase class:

class SQLRelayDatabase(/* Your base database class */):
    # ... existing driver code ...

    def last_insert_id(self, cursor, query_type=None):
        try:
            cursor.execute('SELECT SCOPE_IDENTITY()')
            result = cursor.fetchone()
            return result[0]
        except (IndexError, KeyError, TypeError):
            pass

Once this method is properly integrated, Peewee will call it after executing the INSERT, retrieve the generated ID, and populate both the model's id field and internal _pk attribute automatically.

内容的提问来源于stack exchange，提问作者Nicholas Tulach