CODEWITHSUNDEEP
mysqlsqlalchemyflask-sqlalchemy
jaapz
jaapz

Reputation: 1009

Setting an ID on a new model

Is it possible to create a new instance of a model and setting it's ID? Whenever I try to do that, the model gets persisted, but the ID is set to the next available number in the MySQL auto-increment list.

For example I want to create a new Sensor like this:

s = new Sensor()
s.id = 14
s.name = 'my sensor'
db.session.add(s)
db.session.commit()

saved_instance = Sensor.query.get(14)
assert saved_instance is not None

The assert fails.

Upvotes: 4

Views: 90

Answers (1)

AlexLordThorsen
AlexLordThorsen

Reputation: 8488

I'm going to go on a limb (since you haven't provided the information that will let me know for sure) but I believe your sensor declaration uses an auto-increment. If that's the case then setting it before hand will be completely ignored.

sensor = Table('sensor', metadata,
    Column('id', Integer, primary_key = True),

In MySQL primary keys come pre-packaged with an auto-increment.

For most databases (including MySQL), if ID 14 has ever existed in your database then the auto-increment will not allow that number to be used again during the creation phase. Even if you've deleted the row that held ID 14.

You can go back after the fact and update the row to have a different ID provided that ID doesn't already exist in the system.

s = new Sensor()
s.id = 14
s.name = 'my sensor'
db.session.add(s)
db.session.commit()

saved_instance = Sensor.query.get(14)
print(saved_instance is not None) # Should fail.

print(s.id) # will return the ID that was actually inserted.
s.update().\
    where(users.c.id==s.id).\
    values(id=14)

Upvotes: 1

Related Questions