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:
- Set SQLAlchemy to use PostgreSQL SERIAL for identity generation
- sqlalchemy flush() and get inserted id?
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.
