You may prefer to use session.merge() leaving the SELECT and the
subsequent decision to INSERT or UPDATE to SQLAlchemy (see
http://www.sqlalchemy.org/docs/05/session.html?highlight=merge#merging)
The main section of your example should look like this then:
---snip-----------------------
id = 55
val = random.choice(range(1000))
t = T(id, val)
session = Session()
session.merge(t)
session.commit()
---snip-----------------------
In case your table type forces you to use locks, I _think_ this
should be done as shown below (anyone correct me if I am wrong):
---snip-----------------------
id = 55
val = random.choice(range(1000))
t = T(id, val)
session = Session()
session.execute('LOCK TABLES t WRITE')
session.merge(t)
session.commit()
session.execute('UNLOCK TABLES')
---snip-----------------------
Jyotirmoy wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
