How to start integer primary key from specific number in sqlalchemy

Question

I'm creating a client model and it has an integer primary key.

I'm trying to make the primary key start incrementing from 10000000 but it is not working, nothing happens it is still starting from 1.

How to make the primary key start from a specific number?

Here is the model:

class Client(db.Model):
    client_id = db.Column(db.Integer(), db.Sequence('seq_reg_id', start=10000000, increment=1), primary_key=True)
    first_name = db.Column(db.String(length=30), nullable=False)
    middle_name = db.Column(db.String(length=30), nullable=False)
    last_name = db.Column(db.String(length=30), nullable=False)
    email = db.Column(db.String(length=30), unique=True, nullable=False)
    address = db.Column(db.String(length=50), nullable=False)
    mobile_number = db.Column(db.String(length=15), unique=True, nullable=False)
    sex = db.Column(db.String(length=10), nullable=False)
    age = db.Column(db.Integer(), nullable=False)
    birth_date = db.Column(db.String(length=20), nullable=False)
    password = db.Column(db.String(), nullable=False)

Why do you care where your surrogate primary key starts ? – ljmc Feb 11 '23 at 16:47 — ljmc, Feb 11 '23 at 16:47

score 0 · Answer 1 · answered Feb 11 '23 at 16:53

If you really need to have an autoincrementing column with a specific start, you can use an Identity (only implemented for PostgreSQL > 10, Oracle > 12, SQL Server).

From the docs:

from sqlalchemy import Table, Column, MetaData, Integer, Identity, String

metadata_obj = MetaData()

data = Table(
    "data",
    metadata_obj,
    Column("id", Integer, Identity(start=42, cycle=True), primary_key=True),
    Column("data", String),
)

How to start integer primary key from specific number in sqlalchemy

1 Answers1