CODEWITHSUNDEEP
pythonmysqlsqlalchemyflask-sqlalchemy
James
James

Reputation: 807

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

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. Image of person table

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?

Upvotes: 10

Views: 4752

Answers (4)

mishnea
mishnea

Reputation: 131

SQLAlchemy 1.4 and above

In addition to sequences, you can use sqlalchemy.schema.Identity with some supported databases. OP's example becomes:

from sqlalchemy import Identity
...

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

class Person(Base):
    __tablename__ = "people"
    id = Column(Integer, Identity(), index=True, unique=True)
    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()

As of writing, the Identity construct is currently known to be supported by:

  • PostgreSQL as of version 10.
  • Oracle as of version 12. It also supports passing always=None to enable the default generated mode and the parameter on_null=True to specify “ON NULL” in conjunction with a “BY DEFAULT” identity column.
  • Microsoft SQL Server. MSSQL uses a custom syntax that only supports the start and increment parameters, and ignores all other.

For more info on arguments and supported databases, see the following

Upvotes: 3

Ivan
Ivan

Reputation: 436

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

Upvotes: 3

yurenchen
yurenchen

Reputation: 2473

a workaroud here for mysql:

After sqlalchemy create table,
　alter table manully (add AUTO_INCREMENT property) use DDL.
　( at table after_create event ).

then it will execute on demand like create_all()

test code (1.4.x):

from sqlalchemy.ext.declarative import declarative_base

# --- 1. define table
base = declarative_base()
class store(base):
    __tablename__ = 'store'
    id  = Column(Integer,    autoincrement=True, unique=True, primary_key=False) # AI here not work

    did = Column(String(64), unique=False, nullable=False)
    fid = Column(String(64), unique=False, nullable=False)

    __table_args__ = (
        PrimaryKeyConstraint('did', 'fid', name='idx_did_fid'),
    )


print(store)

# --- 2. fix autoincre on non-primary key // will execute as need, like create_all() dose
from sqlalchemy import event, DDL
event.listen(
    base.metadata, 'after_create',  DDL('ALTER TABLE `store` CHANGE `id` `id` INT(11) NULL DEFAULT NULL AUTO_INCREMENT')
)

# --- 3. create table as need
base.metadata.create_all(engine)

Upvotes: 2

James
James

Reputation: 807

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

Upvotes: 8

Related Questions