Inserting new records table with serial primary key in Postgresql using ORM

[dfreedman]

Very basic scenario that I've been struggling with. I have a table
called "Entries" in Postgresql with a primary key of type Serial call
"id". I'm using ORM reflection to map my classes to underlying
tables. I'm then populating my Entry instance and submitting it for
Insert via session.merge, session.flush, session.commit in that
order. When I supply a value to Entry.id, it overrides the default
sequence. When I don't, I get a primary key error. My goal is create
a new entry record but insert with the generated ID. Please
help...code below.


-------------------------------------------------------------
#dt_weblog is the data tier for this weblog application
#dt_weblog leverages the SQLAlchemy (0.7.2) ORM mapper module to
create
#a mapping between the Postgresql tables and their associated Python
Classes
#The Weblog database and tables must be created prior to launch of
this module

from sqlalchemy import *
from sqlalchemy.orm import *

#Creates a Dialect for Postgresql and a Pool object which will be
leveraged by our Session
db = create_engine('postgresql://postgres:genericpassword@localhost:
5432/WebLog', echo=True)

#Create a configured session class. Will use session instances later
for database communication
Session = sessionmaker(bind=db, autoflush=False, autocommit=False,
expire_on_commit=True)

#Reflect database tables metadata into a variable so that schemas do
not have to be
#explicitly defined in code
metadata = MetaData(db)

#Create table objects whose attributes are based on 'metadata'
variable
users_table = Table('users', metadata, autoload=True)
entries_table = Table('entries', metadata, autoload=True)

#Create classes which whose attributes will be automatically
configured by the mapper based on
#table object attributes
class User(object):
pass
class Entry(object):
pass

#Create mapping between tables and associated classes
usermapper = mapper(User, users_table,
primary_key=[users_table.c.username])
entrymapper = mapper(Entry, entries_table,
primary_key=[entries_table.c.id])

>>> s= Session()
>>> e.id
>>> e.id = 0
>>> session.flush()
>>> e.header = 'test'
>>> e.username = 'dfreedman'
>>> e.text = 'test'
>>> s.merge(e)
>>> s.flush()
>>>s.commit()





--------------------------------------------------------------------------------------

[Michael Bayer]

Theres no apparent issue with your code, a completed version working correctly is attached - I only needed to create Entry.

Start with the attached script, using a new, blank database since it drops the tables, and work backwards towards the identical situation as your failing code to identify the element causing the failure. My guess would be your primary key column is not actually SERIAL and is instead INTEGER.

Several things I'd note:

1. Declarative is much easier to use than direct usage of mapper(). The SQLA documentation standardizes on declarative as the usual method of setting up mappers.
2. Using "0" as a primary key value is in general a bad idea, SERIAL starts from 1 and has the advantage over "0" that it doesn't evaluate as "false" in many situations.
3. You should probably be using add() in the general case; merge() is a special purpose method for merging incoming data on top of existing data, performs more poorly and has more complex operation.
4. Not sure what the rationale for autoflush=False is. In general if you do things the way the tutorial at http://www.sqlalchemy.org/docs/orm/tutorial.html lays them out, you'll be in pretty good shape. SQLAlchemy certainly has options to make it work in many ways, but if you're just starting out, it's best to stick with how the documentation recommends until you're accustomed to how things work and can tweak things for special cases.

[Michael Bayer]

Sorry, once more, using the SERIAL value in an autoincrementing fashion instead of the 0. Also works without issue.

[dfreedman]

Thank you, Michael! The only apparent difference was that HasOIDs was
set to TRUE on my table. On your notes:

1. Will do. I've been a exec for a decade and my coding skills/
ability to absorb technical reading has deteriorated severely. For
some reason mapper was easier for me to pick up. It clicking now and
will shift to declarative. Thanks for the pointer.

2. Wasn't my intention. There was another framework (don't recall
which one) where 0 was interpressed as DEFAULT. Had just tested that
case when I posted.

3. Will do. Thanks for the pointer.

4. Was being sloppy. Will follow the guidance moving forward.

Thanks again.