Using SAProvider, how do I get the last_insert_id() back from a record
that has been added?
model:
class cp_ticket(DeclarativeBase):
__tablename__ = 'cp_ticket'
ticket_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True, autoincrement = True)
client_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('clients.client_id'), default = '1')
stamp = Column(mysql.MSDateTime, default = '1')
controller:
@expose('cp.templates.template')
def newticket(self, **kw):
c.form = NewTicketForm()
return dict(template='form',value=dict())
@validate(NewTicketForm(), error_handler=newticket)
def ticketsave(self, **kw):
provider.add('cp_ticket',values=kw);
# get last_insert_id()
provider.add('cp_ticket_detail',values=kw);
flash('Ticket Entered')
redirect('tickets')
In order to insert the record into cp_ticket_detail, I need to have
the last_insert_id() from the provider.add to 'cp_ticket', but, I'm
unable to see the method for doing this. sqlalchemy claims
last_insert_ids() is the function for this, but, I'm not able to
figure out how to get that data back and provider.add returns None.
I tried to use DBSession.query(cp_ticket).from_statement('select
last_insert_id() as value), but, when printing the results from that,
I received the query and it never sent the request. I tried using a
blank class as documented on the dbsprockets mailing list, but, that
still results in the query being returned rather than the results.
Second, I've got a number of relational tables where I have a header
and detail records. I've been able to work things backwards by
querying the detail records which backreference the header records
which backreferences the client data.
On a particular table, I don't have any identifying data on the detail
records that I can query so I need to query the header record which
should pull the detail records in.
models:
class cp_ticket(DeclarativeBase):
__tablename__ = 'cp_ticket'
ticket_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True, autoincrement = True)
client_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('clients.client_id'), default = '1')
stamp = Column(mysql.MSDateTime, default = '1')
last_alert = Column(mysql.MSTimeStamp, default = '1')
tech_id = Column(mysql.MSTinyInteger(4, unsigned = True), default
= '1')
subject = Column(Unicode(100), default = 'Trouble Report from
CCM')
status = Column(mysql.MSEnum('U','A','P','C','R','S'), default =
'U')
priority = Column(mysql.MSEnum('1','2','3','4','5'), default =
'3')
domain = Column(Unicode(80))
clients = relation(clients, backref=backref('clients',
order_by=client_id))
class cp_ticket_detail(DeclarativeBase):
__tablename__ = 'cp_ticket_detail'
ticket_id = Column(mysql.MSBigInteger(20, unsigned = True),
ForeignKey('cp_ticket.ticket_id'), default = '0')
ticket_detail_id = Column(mysql.MSBigInteger(20, unsigned = True),
primary_key=True, autoincrement = True)
stamp = Column(mysql.MSTimeStamp, PassiveDefault
('CURRENT_TIMESTAMP'))
detail = Column(mysql.MSLongText, default = '0')
ticket = relation(cp_ticket, backref=backref
('cp_ticket_detail',order_by=ticket_detail_id))
current controllers:
@expose('cp.templates.template')
@paginate('tickets', items_per_page = 30)
def tickets(self, sort = 'priority'):
tickets = DBSession.query(cp_ticket).filter(cp_ticket.status!
='C').order_by(sort)
flash('test');
return dict(template='tech_tickets',tickets=tickets,
sort=sort)
@expose('cp.templates.template')
def ticket(self, **kw):
c.form = TicketForm()
value = {'ticket_id':kw['ticket_id']}
ticket_data = DBSession.query(cp_ticket_detail).filter
(cp_ticket_detail.ticket_id==kw['ticket_id'])
return dict
(template='tech_ticket',ticket_data=ticket_data,value=value)
In this case, when I am calling ticket, I am querying the detail
records which gives me duplicates of the header data for each row. In
perl I would have queried cp_ticket which would have given me a nested
array of the header record with the detail records in an array. I'm
doing a one to many relationship, 1 cp_ticket record with multiple
cp_ticket_detail records so I was expecting something like
${ticket_data.ticket_id} from cp_ticket
${ticket_data.detail[0].stamp} from cp_ticket_detail
As it stands now, my ticket_id comes through as ${ticket_data.ticket
[0].ticket_id}
Pardon the models, they were generated from a script I wrote that
worked from an existing mysql database so not all of the fields are
properly mapped.
Thank you.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears Trunk" 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-trunk?hl=en
-~----------~----~----~----~------~----~------~--~---
