Sorry, once more, using the SERIAL value in an autoincrementing fashion instead of the 0. Also works without issue.
On Aug 26, 2011, at 11:20 PM, Michael Bayer wrote: > 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. > > > > > On Aug 26, 2011, at 10:38 PM, dfreedman wrote: > >> 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() >> >> >> >> >> >> -------------------------------------------------------------------------------------- >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > <test.py>-- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. >
from sqlalchemy import * from sqlalchemy.orm import * db = create_engine('postgresql://scott:tiger@localhost/test', echo=True) db.execute("DROP TABLE IF EXISTS users") db.execute("DROP TABLE IF EXISTS entries") db.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(30) ) """) db.execute(""" CREATE TABLE entries ( id SERIAL PRIMARY KEY, header VARCHAR(30), username VARCHAR(30), text VARCHAR(30) )""") Session = sessionmaker(bind=db, autoflush=False, autocommit=False,expire_on_commit=True) metadata = MetaData(db) users_table = Table('users', metadata, autoload=True) entries_table = Table('entries', metadata, autoload=True) class User(object): pass class Entry(object): pass 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 = Entry() e.header = 'test' e.username = 'dfreedman' e.text = 'test' s.merge(e) s.commit() r = s.query(Entry.id, Entry.header, Entry.username, Entry.text).all() assert r == [ (1, "test", "dfreedman", "test") ]
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.