Why doesn't Peewee fill in my object's id?

Question

I am trying to build a database driver for Peewee and i'm having trouble getting the save() method to fill in the primary key/id for objects. Here's some sample code:

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))

Both uncle_bob.id and uncle_bob._pk are None after .save(). From digging into the peewee.py code, it seems that the _WriteQuery.execute() method is supposed to set the _pk attribute, but that isn't happening. My best guess is that the cursor implementation isn't acting properly. Does anyone have more insight than this that can maybe help me track down this problem?

Thanks!

Edit to answer:

For SQL Server, the following code allows you to return the last inserted id:

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

Answer 1

In your SQLRelayDatabase implementation, you will probably need to correctly implement the last_insert_id() method. For python db-api 2.0 drivers, this typically looks like cursor.lastrowid.

The default implementation is:

def last_insert_id(self, cursor, query_type=None):
    return cursor.lastrowid

Where cursor is the cursor object used to execute the insert query.

Databases like Postgresql do not implement this -- instead you execute an INSERT...RETURNING query, so the Postgres implementation is a bit different. The postgres implementation ensures that your insert query includes a RETURNING clause, and then grabs the id returned.

Depending on your DB and the underlying DB-driver, you'll need to pull that last insert id out somehow. Peewee should handle the rest assuming last_insert_id() is implemented.