SQLAlchemy require primary key to be generated by program

When defining a table, you define one column as primary_key=True. As shown in the tutorial, SQLAlchemy will automatically create an ID for an item, even when it is not supplied by the user. The primary_key=True also automatically sets nullable=False.

Is there a way that I can set up the Primary Key so that it is required, but is not filled by SQLAlchemy? I want my program to have to supply a primary key for each row.

Something like:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autogenerate=False)
    name = Column(String)
    nickname = Column(String)

Answer

You can set the autoincrement attribute to False to get this effect*. This code

import sqlalchemy as sa


meta = sa.MetaData()
tbl = sa.Table('test20210102a', meta,
               sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
               sa.Column('name', sa.String(8)))

engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)

meta.drop_all(bind=engine)
meta.create_all(bind=engine)

with engine.connect() as conn:
    conn.execute(tbl.insert({'name': 'foo'}))
engine.dispose()

emits this warning when the table is created

SAWarning: Column ‘test20210102a.id’ is marked as a member of the primary key for table ‘test20210102a’, but has no Python-side or server-side default generator indicated, nor does it indicate ‘autoincrement=True’ or ‘nullable=True’, and no explicit value is passed. Primary key columns typically may not store NULL.

and produces this error

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column “id” violates not-null constraint DETAIL: Failing row contains (null, foo).

---

* The above code should work for most RDBMS. Sqlite has different autoincrement semantics, as described in the dialect information, but you can get similar behaviour by using a different integer type for the id column:

sa.Column('id', sa.SmallInteger, primary_key=True, autoincrement=False)