Hi all,
Well before conceding defeat and turning this into three transaction
problem (adding the project, looking it up to get the id and add the
ai) which even I know is just wrong... From more reading of the
SQLAlchemy docs I think I needed to add a DBSession.add(project) right
before the DBSession.flush:
if kw['project_id'] != u'0':
ai.project_id = int(kw['project_id'])
else:
project.projectname = kw['project_name']
ai.projects = project
log.debug(project.projectname)
DBSession.add(project)
DBSession.flush()
log.debug(ai.projects.projectname)
log.debug(ai.projects.id)
....
DBSession.add(ai)
log.debug(ai.projects.id) now gives me 1 but the DBSession.add(ai)
still fails with a None.
23:53:01,221 INFO [sqlalchemy.engine.base.Engine.0x..90] INSERT INTO
actionitems (description, priority, assigned_to_id, assigned_by_id,
project_id, date_added, date_due) VALUES (?, ?, ?, ?, ?, ?, ?)
23:53:01,221 INFO [sqlalchemy.engine.base.Engine.0x..90] [u'asdf',
u'1', 1, 1, None, '2008-10-22', '2008-10-22']
Maybe my models are define incorrectly and SQLAlchemy can't figure it
out?
class ActionItem(DeclarativeBase):
__tablename__ = 'actionitems'
id = Column(Integer, primary_key = True)
description = Column(String, nullable = False)
priority = Column(Integer, nullable = False)
assigned_to_id = Column(Integer, ForeignKey('persons.id'),
nullable = False)
assigned_by_id = Column(Integer, ForeignKey('persons.id'),
nullable = False)
project_id = Column(Integer, ForeignKey('projects.id'), nullable =
False)
date_added = Column(Date, nullable = False)
date_due = Column(Date, nullable = False)
project = relation('Project', backref = backref('projects',
order_by = 'id'))
assigned_to = relation('Person', primaryjoin =
'ActionItem.assigned_to_id == Person.id', backref =
backref('assigned_to', order_by = 'id'))
assigned_by = relation('Person', primaryjoin =
'ActionItem.assigned_by_id == Person.id', backref =
backref('assigned_by', order_by = 'id'))
class Project(DeclarativeBase):
__tablename__ = 'projects'
id = Column(Integer, primary_key = True)
projectname = Column(String, nullable = False, unique = True)
Thanks for any suggestions.
Eric
On Oct 20, 11:30 pm, Eric <[EMAIL PROTECTED]> wrote:
> Hi,
> Thanks for the suggestions, they make sense to me. I think I
> implemented them correctly but still getting the same IntegrityError
> from the id being Null:
>
> Added project = Project() at the top.
>
> Here is the updated code:
>
> if kw['project_id'] != u'0':
> ai.project_id = int(kw['project_id'])
>
> else:
> project.projectname = kw['project_name']
> ai.projects = project
>
> log.debug(project.projectname)
>
> DBSession.flush()
>
> log.debug(ai.projects.projectname)
> log.debug(ai.projects.id)
> ....
> DBSession.save(ai)
>
> The debug output shows that project.projectname and
> ai.projects.projectname are being set correctly but
> log.debug(ai.projects.id) still gives me a Null after the flush?
>
> I guess I need my hand held a bit more...
>
> Thanks,
> Eric
>
> On Oct 20, 12:32 am, Diez Roggisch <[EMAIL PROTECTED]> wrote:
>
> > You need to flush the Session after you added an object to provoke the
> > creation of the id, otherwise it will only created after the commit of the
> > transaction. But at that time, your controller and template code has been
> > run already, and the id was None. This is one of the annoying aspects of SA.
>
> > And when you use an object to form a relationship, you should also use the
> > object itself, not the id - for the same reason. Like this:
>
> > parent = Parent()
>
> > child = Child()
> > child.parent =parent
> > #child.parent_id = parent.id # Won't work, as id will be none!
>
> > Diez
> > -----Ursprüngliche Nachricht-----
> > Von: "Eric" <[EMAIL PROTECTED]>> Gesendet: 20.10.08 07:27:31An: TurboGears
> > <[email protected]>
> > Betreff: [TurboGears] TG2/SQLAlchemy cascading add problem
>
> > Hi all,
> > Using TG 1.9.7a4 on WinXP/2.5.2 and having trouble getting a cascading
> > database add to work; getting a null value which causes an
> > "IntegrityError: (IntegrityError) actionitems.project_id may not be
> > NULL...". I have a table with a relation between Action items and
> > Projects. On my form, I have a TextField to allow entry of a new
> > project name or a SingleSelectField to pick an existing Project from a
> > list that I pull from the Projects table. If a new project name is
> > entered, I want to add it to the Project table and save the id in the
> > Action item table, otherwise the id of the selected Project is saved
> > with the Action item...
>
> > My model classes are:
>
> > class ActionItem(DeclarativeBase):
> > __tablename__ = 'actionitems'
>
> > id = Column(Integer, primary_key = True)
> > description = Column(String, nullable = False)
> > priority = Column(Integer, nullable = False)
> > assigned_to_id = Column(Integer, ForeignKey('persons.id'),
> > nullable = False)
> > assigned_by_id = Column(Integer, ForeignKey('persons.id'),
> > nullable = False)
> > project_id = Column(Integer, ForeignKey('projects.id'), nullable =
> > False)
> > date_added = Column(Date, nullable = False)
> > date_due = Column(Date, nullable = False)
>
> > project = relation('Project', backref = backref('projects',
> > order_by = 'id'))
> > assigned_to = relation('Person', primaryjoin =
> > 'ActionItem.assigned_to_id == Person.id', backref =
> > backref('assigned_to', order_by = 'id'))
> > assigned_by = relation('Person', primaryjoin =
> > 'ActionItem.assigned_by_id == Person.id', backref =
> > backref('assigned_by', order_by = 'id'))
>
> > class Project(DeclarativeBase):
> > __tablename__ = 'projects'
>
> > id = Column(Integer, primary_key = True)
> > projectname = Column(String, nullable = False, unique = True)
>
> > Try to save it here:
>
> > def create_ai(self, **kw):
> > ai = ActionItem()
> > ai.description = kw['description']
> > ai.priority = kw['priority']
>
> > #temp assignments to prevent SQLAlchemy submit error
> > ai.assigned_to_id = 1
> > ai.assigned_by_id = 1
>
> > if kw['project_id'] != u'0':
> > ai.project_id = int(kw['project_id'])
> > else:
> > ai.projects = Project(projectname = kw['project_name'])
>
> > ai.date_added =
> > datetime.datetime(int(kw['date_added'].split('/')[2]),
> > int(kw['date_added'].split('/')[0]), int(kw['date_added'].split('/')
> > [1]))
> > ai.date_due = datetime.datetime(int(kw['date_due'].split('/')
> > [2]), int(kw['date_due'].split('/')[0]), int(kw['date_due'].split('/')
> > [1]))
> > DBSession.save(ai)
>
> > It looks like the line in the if statement isn't working like I hoped
> > it would, ai.projects = Project(projectname = kw['project_name']).
> > Appreciate some help with this... I got this construction from the
> > SQLAlchemy documentation[1]. I am a very new TG user and relatively
> > new with Python too.
>
> > Thanks,
> > Eric
>
> > [1]http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_relate...
>
> > _______________________________________________________________________
> > Jetzt neu! Schützen Sie Ihren PC mit McAfee und WEB.DE. 30 Tage
> > kostenlos testen.http://www.pc-sicherheit.web.de/startseite/?mc=022220
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" 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/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
