python - 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.