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 [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
