CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Jamie Bull
Jamie Bull

Reputation: 13529

How can increment a sequence only once on a bulk insert?

Given a model like this:

class MyModel(Base):
    __tablename__ = 'my_model'
    id = Column(Integer, nullable=False, primary_key=True, index=True)
    value = Column(Numeric, doc='value')
    batch = Column(Integer, Sequence('my_model_batch_seq'), doc='Batch ID of the update')

I want to issue a batch insert that adds all the new objects with the same batch ID. The code below increments for each object which is not what I'm looking for.

objects = [
    MyModel(
        value=x,
    ) for x in range(10)
]
db.bulk_save_objects(objects)

Upvotes: 0

Views: 423

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52939

If I've understood you correctly, you could first select the next value explicitly:

# Note that this may fail, if you haven't configured a bind on
# your Session.
batch = db.query(func.nextval('my_model_batch_seq')).scalar()

and then just pass it along:

objects = [
    MyModel(
        value=x,
        batch=batch,
    ) for x in range(10)
]
db.bulk_save_objects(objects)

Upvotes: 2

Related Questions