I want to know what is the best way to write code using sqlalchemy
that given a value of the primary key inserts a record if no record
with that key exists or updates the value of the other columns if the
key already exists.
I wrote a naive version which get()s the value and add()s if the
result is None. But the approach fails with many concurrent processes
since the record may have been inserted between the get and add. I
could catch the IntegrityError and retry, but if there are other
processes deleting records as well then there is no guarantee that the
loop would ever terminate.
Is there some approach to doing this operation which is guaranteed to
succeed.
Here is my naive code:
from sqlalchemy import Integer,Column, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from time import sleep
import random
Base=declarative_base()
engine=create_engine("mysql://nob...@localhost/concur",echo=True)
Session = sessionmaker(bind=engine)
class T(Base):
__tablename__ = 't'
i = Column(Integer,primary_key=True)
j = Column(Integer)
def __init__(self,i,j):
self.i=i
self.j=j
id=55
val=random.choice(range(1000))
session=Session()
t=session.query(T).get(id)
sleep(10) #Create a race
if t is None:
t=T(id,100)
session.add(t)
else:
t.j=val
session.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
-~----------~----~----~----~------~----~------~--~---
