Using SQLAlchemy ORM for a non-primary key, unique, auto-incrementing id

Question

When I run the following code, I am expecting the first_name, and last_name to be a composite primary key and for the id to be an autoincrementing index for the row, but not to act as the primary key, as there the information in the rest of the table is what I need to define it's uniqueness, rather than the given ID.

Base = declarative_base()
Session = sessionmaker(bind=db)
session = Session()

class Person(Base):
    __tablename__ = "people"
    id = Column(Integer, index=True, unique=True, autoincrement=True, primary_key=False)
    first_name = Column(String(30), primary_key=True)
    last_name = Column(String(30), primary_key=True)

if __name__ == "__main__":
    Base.metadata.create_all(db)
    session.add_all([
        Person(first_name="Winston", last_name="Moy"),
        Person(first_name="Bill", last_name="Gates"),
        Person(first_name="Steve", last_name="Jobs"),
        Person(first_name="Quinten", last_name="Coldwater")
    ])
    session.commit()

The problem I view the results in DataGrip, I'm getting the following table. The data is not in the order added, and the id column is null, instead of the auto-incrementing integer I'm expecting it to be.

To be clear: My question is: How would I make an auto-incrementing index for a SQLAlchemy ORM class that is not a primary key?

Answer 1

At the time of writing this, SQLAlchemy 1.1 does not support auto-incrementing on a non-primary key field.

Answer 2

Postgresql 2022 answer:

the following code

import uuid
from sqlalchemy import Integer, Column, String, Sequence
from sqlalchemy.dialects.postgresql import UUID

class Test(Base):
    __tablename__ = 'Test'

    id_sec = Sequence(__tablename__ + "_id_seq")
    id = Column(Integer, id_sec, server_default=id_sec.next_value(), nullable=False)
    uuid = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

    # your data
    .... 

generates the following table:

                                      Table "public.Test"
    Column     |       Type        | Collation | Nullable |                   Default                    
---------------+-------------------+-----------+----------+----------------------------------------------
 id            | integer           |           | not null | nextval('"Test_id_seq"'::regclass)
 uuid          | uuid              |           | not null | 

so you can insert rows without specifying the id

PostgreSQL 13.5
SQLAlchemy==1.4.29