That looks like an OK way to do things to me, but I'm by no means an expert.
Depending on memory limitations, and how big the table is going to get, you
might be able to do something like:
* Store all index objects at application startup:
self.indices = self.session.query(Index).all()
* Check the list in memory, rather than the database, for the values:
def _add_index(self, src_id, tar_id):
if (src_id, tar_id) in [(index.src_id, index.tar_id) for index in
self.indices]:
pass #update old object here
else:
pass #create new object here
self.session.commit()
This will increase memory usage, but stops you from having to go back to the DB
as much. Of course, SQLA does lazy loading by default, so for this to work,
you'd need to make it load the src_id's and tar_id's eagerly:
http://www.sqlalchemy.org/docs/orm/loading.html
-----Original Message-----
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Mason
Sent: Thursday, 12 January 2012 11:55 AM
To: sqlalchemy
Subject: [sqlalchemy] insert or update on duplicate key
Hi,
I have been looking at the group archive and googling around a bit,
but it seems like if I want to do insert to table if row doesn't
exist, otherwise update a field in the row is not really possible. I
end up doing something like below.
def _add_index(self, src_id, tar_id):
row = self.session.query(Index).filter(Index.src_id==src_id).\
filter(Index.tar_id==tar_id).first()
if row:
row.new += 1
row.total += 1
else:
row = Index(src_id, tar_id, 1, 1, 0)
self.session.add(row)
self.session.commit()
return
This doesn't look very efficient, is there a better way to do this? I
have been looking at merge(), but that looks like it is for merging
uncommitted transactions in the session before committing to the db.
Is this correct?
TIA,
Mason
--
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.
-------------------------------------------------------------------------
DISCLAIMER: This e-mail transmission and any documents, files and
previous e-mail messages attached to it are private and confidential.
They may contain proprietary or copyright material or information that
is subject to legal professional privilege. They are for the use of
the intended recipient only. Any unauthorised viewing, use, disclosure,
copying, alteration, storage or distribution of, or reliance on, this
message is strictly prohibited. No part may be reproduced, adapted or
transmitted without the written permission of the owner. If you have
received this transmission in error, or are not an authorised recipient,
please immediately notify the sender by return email, delete this
message and all copies from your e-mail system, and destroy any printed
copies. Receipt by anyone other than the intended recipient should not
be deemed a waiver of any privilege or protection. Thales Australia
does not warrant or represent that this e-mail or any documents, files
and previous e-mail messages attached are error or virus free.
-------------------------------------------------------------------------
--
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.
