sqlalchemy primary key without auto-increment

Question

I'm trying to establish a table with a layout approximately like the following:

class Widget(db.Model):
    __tablename__ = 'widgets'
    ext_id = db.Column(db.Integer, primary_key=True)
    w_code = db.Column(db.String(34), unique=True)
    # other traits follow...

All field values are provided through an external system, and new Widgets are discovered and some of the omitted trait values may change over time (very gradually) but the ext_id and w_code are guaranteed to be unique. Given the nature of the values for ext_id it behaves ideally as a primary key.

However when I create a new record, specifying the ext_id value, the value is not used in storage. Instead the values in ext_id follow an auto-increment behavior.

>>> # from a clean database
>>> skill = Widget(ext_id=7723, w_code=u'IGF35ac9')
>>> session.add(skill)
>>> session.commit()
>>> Skill.query.first().ext_id
1
>>> 

How can I specify to SQLAlchemy that the ext_id field should be used as the primary key field without auto-increment?

Note: I could add an extra synthetic id column as the primary key and make ext_id be a unique column instead but this both complicates my code and adds a (minimal) extra bloat to the database and all I/O to it. I'm hoping to avoid that.

Issue originated from a larger project but I was able to create a smaller repro.

Testing with sqlite

Answer 1

Set autoincrement=False to disable creating a sequence or serial for the primary key.

ext_id = db.Column(db.Integer, primary_key=True, autoincrement=False)