[sqlalchemy] Re: Transactions

2018-01-08 Thread Tolstov Sergey
Problem fixed with delete *threaded* parameter on Flask, sorry

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


[sqlalchemy] Re: Transactions

2018-01-08 Thread Tolstov Sergey


вторник, 9 января 2018 г., 9:08:22 UTC+3 пользователь Tolstov Sergey 
написал:
>
> I use transactions and sometimes it must be *session.rollback*.
> Sometimes i get a AttributeError: 'NoneType' object has no attribute 
> 'twophase' 
> That error raises, when one transaction open and processed, and *another* 
> transaction with session use *rollback*
>


Some example on stackoverflow 
 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


[sqlalchemy] Re: transactions with multiple potential constraint violations

2008-07-07 Thread Simon

Thanks Mike!

Your last suggestion was the best (ans easiest) solution, I guess I
just needed someone to point that out to me ;-) Since I want the whole
transaction to either succeed or fail, there's no need to use
SAVEPOINTs.

Cheers, Simon

On 3 Jul., 19:55, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jul 3, 2008, at 1:09 PM, Simon wrote:



  Hi all,

  I have a transactional session which save()s multiple objects to
  multiple tables where each object potentially violates a unique
  primary key or just a unique key constraint. At flush() time, MySQL
  returns an IntegrityError which specifies the value of the offending
  key and the key's number within its table (i.e. first key, second
  key...), but I cannot from this information figure out which of my
  objects is the culprit. Any suggestions on how to solve this problem?
  Ideally, I would like to rollback() the whole transaction when such an
  error occurs, regenerate the keys on the offending object, and repeat
  until commit() succeeds.

 use SQLA 0.5 if you'd like to rollback() a session and continue using  
 the objects within.  Read 
 throughhttp://www.sqlalchemy.org/docs/05/session.html#unitofwork_using_rolling
   and elsewhere to see what the SQLA contract is regarding sessions,  
 errors and rolling back.   It's also worthwhile to give SAVEPOINT a  
 try, which allows partial transactions to commit and is described 
 athttp://www.sqlalchemy.org/docs/05/session.html#unitofwork_managing_sa...
   .  In particular, some databases such as Postgres do not allow the  
 transaction to continue if an integrity error is raised, necessitating  
 the use of SAVEPOINT, or just querying the table beforehand (which is  
 the traditional approach to this problem).

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: transactions with multiple potential constraint violations

2008-07-03 Thread Michael Bayer


On Jul 3, 2008, at 1:09 PM, Simon wrote:


 Hi all,

 I have a transactional session which save()s multiple objects to
 multiple tables where each object potentially violates a unique
 primary key or just a unique key constraint. At flush() time, MySQL
 returns an IntegrityError which specifies the value of the offending
 key and the key's number within its table (i.e. first key, second
 key...), but I cannot from this information figure out which of my
 objects is the culprit. Any suggestions on how to solve this problem?
 Ideally, I would like to rollback() the whole transaction when such an
 error occurs, regenerate the keys on the offending object, and repeat
 until commit() succeeds.


use SQLA 0.5 if you'd like to rollback() a session and continue using  
the objects within.  Read through 
http://www.sqlalchemy.org/docs/05/session.html#unitofwork_using_rolling 
  and elsewhere to see what the SQLA contract is regarding sessions,  
errors and rolling back.   It's also worthwhile to give SAVEPOINT a  
try, which allows partial transactions to commit and is described at 
http://www.sqlalchemy.org/docs/05/session.html#unitofwork_managing_savepoint 
  .  In particular, some databases such as Postgres do not allow the  
transaction to continue if an integrity error is raised, necessitating  
the use of SAVEPOINT, or just querying the table beforehand (which is  
the traditional approach to this problem). 
   

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-08 Thread Tomer

Wow, thanks for the detailed response!
That was very helpful (and I'm going to do some more reading now on
database transactions in general, and then dig deeper into how
SQLAlchemy can use the different options).

On Jun 8, 7:23 am, Kyle Schaffrick [EMAIL PROTECTED] wrote:
 On Sat, 7 Jun 2008 22:21:20 -0700 (PDT)

 Tomer [EMAIL PROTECTED] wrote:
  I have two follow-up questions:

  1) From what I understand, if I read an object (eg, Student) from
  the database, modify that object or other objects, and then commit, I
  have no guarantee that the object didn't change between the time I
  read it and the time I committed.

 Well, I think I can describe PostgreSQL's behavior as I understand it, I
 would guess other DBMSs have reasonably similar semantics. I can't speak
 to whether SA has any precautions of it's own above and beyond what the
 DBMS provides, but I would tend to doubt it.

 In the case of selecting a Student, altering it, and flushing it back
 all within onetransaction: If a concurrenttransactionhas uncommitted
 UPDATEs or DELETEs on any rows you try to UPDATE or DELETE, your
 statement will block until the concurrent writer commits or rolls back.
 Then, either

   A) You are using read committed isolation (allows nonrepeatable
   reads), in which case it then runs the statement against the new view
   of the world, or

   B) You are using serializable isolation (no nonrepeatable reads), in
   which case it aborts yourtransactionif the other writer changed the
   rows you were trying to write.

 Whether nonrepeatable reads cause breakage depends on the specific
 nature of the updates.

                                    For example:

      if len(Session.query(Student).filter_by(Student.name ==
  'Bill').all())  0: # are there any students named 'Bill'?
          school = Session.query(School).one() # assume there is one
  school in the database
          school.hasStudentNamedBill = True
          Session.commit()

  When the commit is issued, I might end up with an inconsistent
  database, because a differenttransaction(in another thread, for
  example) may have changed the name of the student after I checked if
  there is a student named Bill, but before I committed.

 This is interesting, and I think it's where SELECT FOR SHARE (or
 similar) can help. In the non-contended case it's mostly just a normal
 SELECT. If a concurrenttransactionhas uncommitted UPDATEs or DELETEs
 on any rows selected by this statement, the SELECT FOR SHARE will wait,
 as above, until the concurrent writer commits or rolls back.  Then, it
 either

   A) (Read committed isolation) Re-runs the SELECT to get the new view
   of the world and returns that, or

   B) (Serializable isolation) Aborts yourtransactionif the other
   writer changed any of the rows SELECTed.

 In SA I think it would look like this, modulo note [1]:

   if len(Session.query(Student).with_lockmode('read')
           .filter_by(Student.name == 'Bill').all()):
       # ...and so on

 It is also possible to use SELECT FOR SHARE NOWAIT to request that the
 DB abort yourtransactionif the operation would have blocked. SA
 doesn't implement NOWAIT except on SELECT FOR UPDATE, as far as I can
 tell.

                                                         From the last
  answer it seems like databases that support transactions might not
  suffer from this problem (if they take locks on SELECTed rows), but I
  tried in SQLite (which supports transactions) and it didn't help.
  Would a different database help solve the problem?

 That would probably be the case. I have never attempted to use SQLite in
 a high-concurrency situation, and I get the impression it's not
 particularly attuned for it: SQLite's lock granularity is the entire
 database. It doesn't support SELECT FOR UPDATE/SHARE or anything like
 it that I can tell.

 -Kyle

 [1] I happened to notice in the process of writing this email
     that SA's PG dialect doesn't support select(for_update='read')
     a.k.a. SELECT FOR SHARE. Bug?

    http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-FOR-UPD...
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread az

On Saturday 07 June 2008 21:35:35 Tomer wrote:
 If I am just issuing a read-only query, and I want to make sure
 that nothing will change in the middle, do I need a transaction? If
 so, what do I do with it when I'm done - commit or rollback?

 For example, I want to do this:

 for user in Session.query(User):
 print user.name

 Should I use a transaction like this:

 Session.begin()
 for user in Session.query(User):
 print user.name
 Session.commit()
 If not, how would I deal with this if my session (created with
 scoped_session) is transactional?
if the __str__ happens to self-change (well, shit happens), then u 
should not do commit nrt flush. the safest is to rollback - 
regardless what u do. or maybe close() or clear()?
i think there was some way to make the session readonly alltogether -- 
but i'm not sure.

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread Tomer

Thanks. What would happen if I didn't do anything (I've seen lots of
examples online that will just issue a query like
Session.query(User).all() and that's all). Will that query start a
transaction if it's a transactional session?

On Jun 7, 11:58 am, [EMAIL PROTECTED] wrote:
 On Saturday 07 June 2008 21:35:35 Tomer wrote:

  If I am just issuing a read-only query, and I want to make sure
  that nothing will change in the middle, do I need a transaction? If
  so, what do I do with it when I'm done - commit or rollback?

  For example, I want to do this:

  for user in Session.query(User):
      print user.name

  Should I use a transaction like this:

  Session.begin()
  for user in Session.query(User):
      print user.name
  Session.commit()
  If not, how would I deal with this if my session (created with
  scoped_session) is transactional?

 if the __str__ happens to self-change (well, shit happens), then u
 should not do commit nrt flush. the safest is to rollback -
 regardless what u do. or maybe close() or clear()?
 i think there was some way to make the session readonly alltogether --
 but i'm not sure.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread az

On Saturday 07 June 2008 22:50:36 Tomer wrote:
 Thanks. What would happen if I didn't do anything (I've seen lots
 of examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?
no idea, never used those, see docs.
as long as u know your objects, these things should be fine. But if 
it's not _your_ objects and/or u expect anything from those or u're 
paranoic about the db safety... open it with readonly access?

 On Jun 7, 11:58 am, [EMAIL PROTECTED] wrote:
  On Saturday 07 June 2008 21:35:35 Tomer wrote:
   If I am just issuing a read-only query, and I want to make sure
   that nothing will change in the middle, do I need a
   transaction? If so, what do I do with it when I'm done - commit
   or rollback?
  
   For example, I want to do this:
  
   for user in Session.query(User):
       print user.name
  
   Should I use a transaction like this:
  
   Session.begin()
   for user in Session.query(User):
       print user.name
   Session.commit()
   If not, how would I deal with this if my session (created with
   scoped_session) is transactional?
 
  if the __str__ happens to self-change (well, shit happens), then
  u should not do commit nrt flush. the safest is to rollback -
  regardless what u do. or maybe close() or clear()?
  i think there was some way to make the session readonly
  alltogether -- but i'm not sure.

 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread Lukasz Szybalski

On Sat, Jun 7, 2008 at 2:50 PM, Tomer [EMAIL PROTECTED] wrote:

 Thanks. What would happen if I didn't do anything (I've seen lots of
 examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?


Currently that is what I use to do to get readonly. Since I am the one
that manages sa code as long as I don't say,

User.somefiled= 123, I should have no worries about any changes being made.

User.query.filter.is just a select statement.

Unless proven/shown otherwise.

Lucas

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread Michael Bayer


On Jun 7, 2008, at 3:50 PM, Tomer wrote:


 Thanks. What would happen if I didn't do anything (I've seen lots of
 examples online that will just issue a query like
 Session.query(User).all() and that's all). Will that query start a
 transaction if it's a transactional session?

when you use the DBAPI (which every SQLA driver does), anytime you get  
a hold of a connection, there is a transaction in progress.  Depending  
on what database backend you're using, this might have different  
meaning; such as if you're using MySQL with ISAM tables, there isnt  
really much transactional.   But generally, selecting from tables  
implies that those rows might be pulled into the transaction such that  
changes made external to the trans aren't visible (depending on  
isolation behavior).  But this doesn't actually write anything to  
the database.  Only INSERT/UPDATE/DELETE has that effect.   When using  
the ORM, these operations are encapsulated entirely wihtin a flush(),  
so if your transaction does not flush(), then nothing is written.

Doing a rollback() will ensure completely that nothing is written,  
even if flushes have taken place.  With SQLA 0.4, rollback() has the  
caveat that the Session is not really usable after a rollback() until  
it is clear()'ed.  With 0.5 this restriction is lifted.



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions and read-only operations

2008-06-07 Thread Tomer

Thanks for the detailed response.

I have two follow-up questions:

1) From what I understand, if I read an object (eg, Student) from
the database, modify that object or other objects, and then commit, I
have no guarantee that the object didn't change between the time I
read it and the time I committed. For example:

if len(Session.query(Student).filter_by(Student.name ==
'Bill').all())  0: # are there any students named 'Bill'?
school = Session.query(School).one() # assume there is one
school in the database
school.hasStudentNamedBill = True
Session.commit()

When the commit is issued, I might end up with an inconsistent
database, because a different transaction (in another thread, for
example) may have changed the name of the student after I checked if
there is a student named Bill, but before I committed. From the last
answer it seems like databases that support transactions might not
suffer from this problem (if they take locks on SELECTed rows), but I
tried in SQLite (which supports transactions) and it didn't help.
Would a different database help solve the problem?

2) You mentioned that the recommended approach to roll back in SA 0.4
is to call rollback() and then clear(). Can I just call close() on the
session instead? From the documentation it seems that close() is
essentially a rollback combined with a clear.

Thanks!

On Jun 7, 9:29 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Jun 7, 2008, at 3:50 PM, Tomer wrote:



  Thanks. What would happen if I didn't do anything (I've seen lots of
  examples online that will just issue a query like
  Session.query(User).all() and that's all). Will that query start a
  transaction if it's a transactional session?

 when you use the DBAPI (which every SQLA driver does), anytime you get  
 a hold of a connection, there is a transaction in progress.  Depending  
 on what database backend you're using, this might have different  
 meaning; such as if you're using MySQL with ISAM tables, there isnt  
 really much transactional.   But generally, selecting from tables  
 implies that those rows might be pulled into the transaction such that  
 changes made external to the trans aren't visible (depending on  
 isolation behavior).  But this doesn't actually write anything to  
 the database.  Only INSERT/UPDATE/DELETE has that effect.   When using  
 the ORM, these operations are encapsulated entirely wihtin a flush(),  
 so if your transaction does not flush(), then nothing is written.

 Doing a rollback() will ensure completely that nothing is written,  
 even if flushes have taken place.  With SQLA 0.4, rollback() has the  
 caveat that the Session is not really usable after a rollback() until  
 it is clear()'ed.  With 0.5 this restriction is lifted.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: transactions

2007-01-20 Thread Jonathan Ellis

yes, you should, and your commented-out code looks fine

On 1/20/07, jose [EMAIL PROTECTED] wrote:

 Hi all,

 I have a question about transactions
 I need to insert data into two tables (anag and user), sice I need to
 insert the generated primary key of anag into user.
 I have to flush anag to have the id to insert into user table.
 What happens if there's an interruption in the middle of this, for
 example, immediately after to flush anag?
 Should I use create_transaction() to avoid a such thing?

 #transaction = session.create_transaction()
 #try:
 anag = Anag(
 name = data.get('display_name')
 )
 session.save(anag)
 session.flush()

 user = User(
 anag_id = anag.id
 )
 session.save(user)
 session.flush()

 #transaction.commit()
 #except:
 #transaction.rollback()

 jo


 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: transactions

2007-01-20 Thread jose

Jonathan Ellis wrote:

yes, you should, and your commented-out code looks fine
  

thank you, Jonathan.

On 1/20/07, jose [EMAIL PROTECTED] wrote:
  

Hi all,

I have a question about transactions
I need to insert data into two tables (anag and user), sice I need to
insert the generated primary key of anag into user.
I have to flush anag to have the id to insert into user table.
What happens if there's an interruption in the middle of this, for
example, immediately after to flush anag?
Should I use create_transaction() to avoid a such thing?

#transaction = session.create_transaction()
#try:
anag = Anag(
name = data.get('display_name')
)
session.save(anag)
session.flush()

user = User(
anag_id = anag.id
)
session.save(user)
session.flush()

#transaction.commit()
#except:
#transaction.rollback()

jo




jo

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



[sqlalchemy] Re: Transactions

2007-01-05 Thread Rick Morrison

You may want to turn on logging to see exactly what SQL is being issued:

try adding

import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

to the top of your test script

On 1/5/07, Paul Johnston [EMAIL PROTECTED] wrote:



Hi,

The following program outputs 1; I thought it should output 0. Any
comments?

from sqlalchemy import *

db = create_engine(sqlite:///:memory:)
metadata = BoundMetaData(db)

users = Table('users', metadata,
  Column('user_id', Integer, primary_key=True),
  Column('user_name', String))
users.create()


c1 = db.connect()
t1 = c1.begin()
c1.execute(users.insert(), user_id=1, user_name='user1')

c2 = db.connect()
t2 = c2.begin()

print len(c2.execute(select * from users).fetchall())

Regards,

Paul





--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---