CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyflask-sqlalchemy
bertday
bertday

Reputation: 10971

Integer field not autoincrementing in SQLAlchemy

I have a Flask-SQLAlchemy model with an Integer field that I'd like to autoincrement. It's not a primary key; it's a surrogate ID. The model looks like:

class StreetSegment(db.Model):
    id = db.Column(db.Integer, autoincrement=True)
    seg_id = db.Column(db.Integer, primary_key=True)

When I create the table in my Postgres database, the id field is created as a plain integer. If I insert rows without specifying a value for id, it doesn't get populated. Is there some way I can force SQLAlchemy to use SERIAL even if it isn't the primary key?

Upvotes: 3

Views: 9672

Answers (2)

alexanderdavide
alexanderdavide

Reputation: 1675

SQLAlchemy does not support auto_increment for non-primary-key columns.

If your database supports it, you can setup the same behavior using sequences. PostgreSQL supports this. Sequences actually are not bound to a very specific column. Instead, they exist on the database level and can be reused. Sequences are the exact construct, SQLAlchemy uses for auto incrementing primary-key columns.

To use a sequence as described in the accepted answer, it must exist. Following, I have an example of an alembic migration with SQLAlchemy to achieve that.

You can associate a sequence with a column in the column constructor. The DDL Expression Constructs API helps you creating and dropping the sequence.

An example:

from alembic import op
import sqlalchemy as sa

measurement_id_seq = sa.Sequence('Measurement_MeasurementId_seq') # represents the sequence


def upgrade():
  op.execute(sa.schema.CreateSequence(measurement_id_seq)) # create the sequence
  op.create_table(
      'Measurement',
      sa.Column('DataSourceId',
                sa.Integer,
                sa.ForeignKey('DataSource.DataSourceId'),
                nullable=False),
      sa.Column('LocationId',
                sa.Integer,
                sa.ForeignKey('Location.LocationId'),
                nullable=False),
      sa.Column('MeasurementId',
                sa.Integer,
                measurement_id_seq, # the sequence as SchemaItem
                server_default=measurement_id_seq.next_value())) # next value of the sequence as default
      [...]
  op.create_primary_key('Measurement_pkey', 'Measurement',
                        ['DataSourceId', 'LocationId', 'Timestamp'])
  pass


def downgrade():
  op.execute(
      sa.schema.DropSequence(sa.Sequence('Measurement_MeasurementId_seq')))
  op.drop_constraint('Measurement_pkey', 'Measurement')
  op.drop_table('Measurement')
  pass

Upvotes: 2

univerio
univerio

Reputation: 20548

Use Sequence instead of autoincrement:

id = db.Column(db.Integer, db.Sequence("seq_street_segment_id"))

Upvotes: 7

Related Questions