SQLAlchemy not setting primary key from auto increment after commit

Question

I am using SQLAlchemy to connect to a postgresql database. I have defined my primary key columns in postgresql to be of type serial i.e. auto-increment integer and have marked them in my SQLAlchemy model with primary_key=true.

On committing the SQLAlchemy session, the model is saved to the db and I can see the primary key set in the database but the id property on my SQLAlchemy model object always has a value of None i.e. it isn't picking up the auto-increment value. I'm not sure what I have got wrong.

I have checked out the existing SO questions but have not found an answer:

My code is below:

Create the table in postgres:

CREATE TABLE my_model
(
    id serial NOT NULL,
    type text,
    user_id integer,

    CONSTRAINT pk_network_task PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Set up SQLAlchemy and the model:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

engine = create_engine(db_url, convert_unicode=True, echo=True)
session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))

class MyModel(Base):
    __tablename__ = 'my_model'

    id = sa.Column(sa.Integer, primary_key=True)
    user_id = sa.Column(sa.Integer)
    type = sa.Column(sa.String)

Try and store the model:

my_model = MyModel()
user_id = 1
type = "A type"
session.merge(my_model)
session.commit()
my_model.id #Always None, don't know why

my_model.id is still None after the commit. I have also try calling close on the session but that didn't work either.

user783836 user783836 · Accepted Answer · 2014-12-24T07:06:25

Turns out I didn't understand the difference between

session.merge(my_model)

and

session.add(my_model)

session.merge(my_model) (which I had been using) doesn't add the object given to it to the session. Instead it returns a new object i.e. the merged model, which has been added to the session. If you reference this new object all is well i.e.

my_model = session.merge(my_model)

add on the other hand, adds the object given to it to the session.

SQLAlchemy not setting primary key from auto increment after commit

1 Answers