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.
>
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.id = 0
#s.flush()
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 == [
(0, "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.
