Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Again, thank you Mike!

I never see these issues happening locally, which is where I use gunicorn 
 as the server. Your theory might 
explain that. The trace comes from our beta server online, which runs 
nginx/uwsgi . I'll 
poke around the configuration, maybe that'll show something.

Eventually though (in the next one-two months) I would like to move to 
gunicorn as the sole server, because handling API requests is the only 
thing it needs to do.

Cheers,
Jens


On Friday, September 29, 2017 at 9:55:30 AM UTC+10, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 7:17 PM,   
> wrote: 
> > Thank you, Mike! 
> > 
> > I would have never extracted your interpretation from the trace! (I 
> don’t 
> > know the code…) 
> > 
> > The two questions that arise now, though, are 
> > 
> >  - Why would connections drop out of the pool, is this a configuration 
> > problem that I should worry about? 
>
> something was already wrong with the connection when the pool got it 
> back, so it had to be discarded. as far as what was wrong, it had to 
> do with the request itself, and it was very possibly the web request's 
> client dropped the connection, e.g. user hit the stop button. Not 
> sure what uswgi does but it might have called a thread.exit() / 
> SystemExit that interrupted PyMySQL's work on the socket.   This is 
> something we definitely see in the eventlet world at least. 
>
> >  - The request still failed with a 502 although, as you said, it’s a 
> > harmless exception. What is the proper way of handling these situations? 
>
> if uwsgi is killing off threads when a connection is cut, it might 
> want to be more graceful about that and at least log that the thread 
> was killed in the middle.   If this is in fact what's going on.I 
> stick with apache/mod_wsgi for reasons like these. 
>
>
> > 
> > Still learning new things here, and I’m curious about your suggestions 
> :-) 
> > Jens 
> > 
> > 
> > On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote: 
> >> 
> >> On Thu, Sep 28, 2017 at 2:48 PM,   wrote: 
> >> > Hello, 
> >> > 
> >> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the 
> >> > context 
> >> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and 
> >> > mysql 
> >> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. 
> >> > 
> >> > On seemingly random requests (although all seem to be OPTIONS) I see 
> the 
> >> > following exception in the server logs (see also this PyMySQL issue): 
> >> > 
> >> > 2017-09-28 18:13:17,765 ERROR 
> >> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during 
> reset 
> >> > or 
> >> > similar 
> >> > Traceback (most recent call last): 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
> >> > line 1182, in _execute_context 
> >> > context) 
> >> >   File 
> >> > 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
> >> > line 470, in do_execute 
> >> > cursor.execute(statement, parameters) 
> >> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >> > line 
> >> > 166, in execute 
> >> > result = self._query(query) 
> >> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> >> > line 
> >> > 322, in _query 
> >> > conn.query(q) 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 856, in query 
> >> > self._affected_rows = 
> self._read_query_result(unbuffered=unbuffered) 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1057, in _read_query_result 
> >> > result.read() 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1340, in read 
> >> > first_packet = self.connection._read_packet() 
> >> >   File 
> >> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> >> > line 1001, in _read_packet 
> >> > % (packet_number, self._next_seq_id)) 
> >> > pymysql.err.InternalError: Packet sequence number wrong - got 102 
> >> > expected 8 
> >> > 
> >> > 
> >> > I see this error in different variations for the packet sequence. Any 
> >> > hint 
> >> > or tip to explain this issue is appreciated! 
> >> 
> >> the log seems to indicate the connection pool has gotten the 
> >> connection back and is attempting to emit a rollback() upon it.  These 
> >> can fail if the connection has already had some varieties of error 
> >> occur upon it, most often when the work on the connection was 
> >> interrupted, such as when receiving a greenlet or thread exit.   It's 
> >> likely when a WSGI request is interrupted, the cleanup logic here 
> >> fails because the connection has been thrown into an invalid state. 
> >> The connection is invalidated (e.g. discarded) in 

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[Mike Bayer]

On Thu, Sep 28, 2017 at 7:17 PM,   wrote:
> Thank you, Mike!
>
> I would have never extracted your interpretation from the trace! (I don’t
> know the code…)
>
> The two questions that arise now, though, are
>
>  - Why would connections drop out of the pool, is this a configuration
> problem that I should worry about?

something was already wrong with the connection when the pool got it
back, so it had to be discarded. as far as what was wrong, it had to
do with the request itself, and it was very possibly the web request's
client dropped the connection, e.g. user hit the stop button. Not
sure what uswgi does but it might have called a thread.exit() /
SystemExit that interrupted PyMySQL's work on the socket.   This is
something we definitely see in the eventlet world at least.

>  - The request still failed with a 502 although, as you said, it’s a
> harmless exception. What is the proper way of handling these situations?

if uwsgi is killing off threads when a connection is cut, it might
want to be more graceful about that and at least log that the thread
was killed in the middle.   If this is in fact what's going on.I
stick with apache/mod_wsgi for reasons like these.


>
> Still learning new things here, and I’m curious about your suggestions :-)
> Jens
>
>
> On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote:
>>
>> On Thu, Sep 28, 2017 at 2:48 PM,   wrote:
>> > Hello,
>> >
>> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the
>> > context
>> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and
>> > mysql
>> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation.
>> >
>> > On seemingly random requests (although all seem to be OPTIONS) I see the
>> > following exception in the server logs (see also this PyMySQL issue):
>> >
>> > 2017-09-28 18:13:17,765 ERROR
>> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during reset
>> > or
>> > similar
>> > Traceback (most recent call last):
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py",
>> > line 1182, in _execute_context
>> > context)
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py",
>> > line 470, in do_execute
>> > cursor.execute(statement, parameters)
>> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py",
>> > line
>> > 166, in execute
>> > result = self._query(query)
>> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py",
>> > line
>> > 322, in _query
>> > conn.query(q)
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
>> > line 856, in query
>> > self._affected_rows = self._read_query_result(unbuffered=unbuffered)
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
>> > line 1057, in _read_query_result
>> > result.read()
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
>> > line 1340, in read
>> > first_packet = self.connection._read_packet()
>> >   File
>> > "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
>> > line 1001, in _read_packet
>> > % (packet_number, self._next_seq_id))
>> > pymysql.err.InternalError: Packet sequence number wrong - got 102
>> > expected 8
>> >
>> >
>> > I see this error in different variations for the packet sequence. Any
>> > hint
>> > or tip to explain this issue is appreciated!
>>
>> the log seems to indicate the connection pool has gotten the
>> connection back and is attempting to emit a rollback() upon it.  These
>> can fail if the connection has already had some varieties of error
>> occur upon it, most often when the work on the connection was
>> interrupted, such as when receiving a greenlet or thread exit.   It's
>> likely when a WSGI request is interrupted, the cleanup logic here
>> fails because the connection has been thrown into an invalid state.
>> The connection is invalidated (e.g. discarded) in this case and is
>> harmless.
>>
>>
>> >
>> > Thank you!
>> > Jens
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post 

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Thank you, Mike! 

I would have never extracted your interpretation from the trace! (I don’t 
know the code…) 

The two questions that arise now, though, are

 - Why would connections drop out of the pool, is this a configuration 
problem that I should worry about?
 - The request still failed with a 502 although, as you said, it’s a 
harmless exception. What is the proper way of handling these situations?

Still learning new things here, and I’m curious about your suggestions :-)
Jens


On Friday, September 29, 2017 at 6:59:48 AM UTC+10, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 2:48 PM,   
> wrote: 
> > Hello, 
> > 
> > I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the 
> context 
> > of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and 
> mysql 
> > 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation. 
> > 
> > On seemingly random requests (although all seem to be OPTIONS) I see the 
> > following exception in the server logs (see also this PyMySQL issue): 
> > 
> > 2017-09-28 18:13:17,765 ERROR 
> > [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during reset 
> or 
> > similar 
> > Traceback (most recent call last): 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
> > line 1182, in _execute_context 
> > context) 
> >   File 
> > "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
> > line 470, in do_execute 
> > cursor.execute(statement, parameters) 
> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> line 
> > 166, in execute 
> > result = self._query(query) 
> >   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", 
> line 
> > 322, in _query 
> > conn.query(q) 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 856, in query 
> > self._affected_rows = self._read_query_result(unbuffered=unbuffered) 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1057, in _read_query_result 
> > result.read() 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1340, in read 
> > first_packet = self.connection._read_packet() 
> >   File 
> "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", 
> > line 1001, in _read_packet 
> > % (packet_number, self._next_seq_id)) 
> > pymysql.err.InternalError: Packet sequence number wrong - got 102 
> expected 8 
> > 
> > 
> > I see this error in different variations for the packet sequence. Any 
> hint 
> > or tip to explain this issue is appreciated! 
>
> the log seems to indicate the connection pool has gotten the 
> connection back and is attempting to emit a rollback() upon it.  These 
> can fail if the connection has already had some varieties of error 
> occur upon it, most often when the work on the connection was 
> interrupted, such as when receiving a greenlet or thread exit.   It's 
> likely when a WSGI request is interrupted, the cleanup logic here 
> fails because the connection has been thrown into an invalid state. 
> The connection is invalidated (e.g. discarded) in this case and is 
> harmless. 
>
>
> > 
> > Thank you! 
> > Jens 
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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.

Re: [sqlalchemy] Packet sequence number wrong (PyMySQL driver or…?)

[Mike Bayer]

On Thu, Sep 28, 2017 at 2:48 PM,   wrote:
> Hello,
>
> I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the context
> of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and mysql
> 5.6.3. I followed the Pyramid/Alchemy Cookiecutter implementation.
>
> On seemingly random requests (although all seem to be OPTIONS) I see the
> following exception in the server logs (see also this PyMySQL issue):
>
> 2017-09-28 18:13:17,765 ERROR
> [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] Exception during reset or
> similar
> Traceback (most recent call last):
>   File "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1182, in _execute_context
> context)
>   File
> "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py",
> line 470, in do_execute
> cursor.execute(statement, parameters)
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line
> 166, in execute
> result = self._query(query)
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line
> 322, in _query
> conn.query(q)
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
> line 856, in query
> self._affected_rows = self._read_query_result(unbuffered=unbuffered)
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
> line 1057, in _read_query_result
> result.read()
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
> line 1340, in read
> first_packet = self.connection._read_packet()
>   File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py",
> line 1001, in _read_packet
> % (packet_number, self._next_seq_id))
> pymysql.err.InternalError: Packet sequence number wrong - got 102 expected 8
>
>
> I see this error in different variations for the packet sequence. Any hint
> or tip to explain this issue is appreciated!

the log seems to indicate the connection pool has gotten the
connection back and is attempting to emit a rollback() upon it.  These
can fail if the connection has already had some varieties of error
occur upon it, most often when the work on the connection was
interrupted, such as when receiving a greenlet or thread exit.   It's
likely when a WSGI request is interrupted, the cleanup logic here
fails because the connection has been thrown into an invalid state.
The connection is invalidated (e.g. discarded) in this case and is
harmless.


>
> Thank you!
> Jens
>
> --
> 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 - 
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] Packet sequence number wrong (PyMySQL driver or…?)

[jens . troeger]

Hello,

I’m using SQLAlchemy (1.1.13) and SQLAlchemy-Utils (0.32.14) in the context 
of a Pyramid (1.9.1) web-browser, PyMySQL (0.7.11) as a driver, and mysql 
5.6.3. I followed the Pyramid/Alchemy Cookiecutter 
 implementation.

On seemingly random requests (although all seem to be OPTIONS) I see the 
following exception in the server logs (see also this PyMySQL issue 
):

2017-09-28 18:13:17,765 ERROR [sqlalchemy.pool.QueuePool][b'uWSGIWorker1Core0'] 
Exception during reset or similar
Traceback (most recent call last):
  File "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", 
line 1182, in _execute_context
context)
  File "/var/www/…/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", 
line 470, in do_execute
cursor.execute(statement, parameters)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line 166, 
in execute
result = self._query(query)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/cursors.py", line 322, 
in _query
conn.query(q)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
856, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1057, in _read_query_result
result.read()
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1340, in read
first_packet = self.connection._read_packet()
  File "/var/www/…/lib64/python3.6/site-packages/pymysql/connections.py", line 
1001, in _read_packet
% (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 102 expected 8


I see this error in different variations for the packet sequence. Any hint 
or tip to explain this issue is appreciated!

Thank you!
Jens

-- 
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.

Re: [sqlalchemy] Understanding session.begin(subtransactions=True) as a context manager

[Doug Farrell]

Hi Mike,

Thanks very much for the response and the clear explanation, and the link. 
All of that was very helpful to me, and I'm made some changes to my code 
accordingly. I am using Flask-SQLAlchemy by the way, so what you wrote 
makes even more sense in that context.

Doug

On Thursday, September 28, 2017 at 10:54:13 AM UTC-4, Mike Bayer wrote:
>
> On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell  > wrote: 
> > Hi all, 
> > 
> > I'm having trouble understanding how to use 
> > session.begin(subtransactions=True) as a context manager. I'm working in 
> > Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on 
> CentOS 
> > 7 servers. I like session.begin() as a context manager, but things 
> aren't 
> > working as I thought they would. For example: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
>
>
> I would not recommend using the Session in this pattern.Leaving 
> the session with its default setting of "autocommit=False" means you 
> never have to call begin() at all; this is desirable because there's 
> no reason to use a Session outside of an explicit transaction (this 
> was not the case many years ago which is why begin() remains). 
> Methods that work with objects should not also be demarcating 
> transaction scope; there's general discussion of this at 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it.
>  
>
>
> if you're doing Flask, the commit of the Session should be at the end 
> of the request, the begin is implicit, and that's it. If you 
> absolutely need a commit to occur at some special place before you've 
> reached the official end of the request + commit, you can just simply 
> call session.commit() at any time.The Session will start a new 
> transaction if and when additional work is performed with it. 
>
> Now, if you need your work to be in an **independent** transaction, 
> that is, totally separate from the "main" one, then you need to do 
> your work in a totally different Session object.   This is an 
> acceptable pattern that is sometimes necessary. 
>
>
> > 
> > I thought when the context manager went out of scope it would perform a 
> > db.session.commit() to persist the thing instance, but I haven't seen 
> > changes to the database. 
>
> the commit() will happen if the begin() is the outermost begin().  If 
> not, then it's one of the "inner" blocks and no commit will occur. 
> If you haven't placed this session into autocommit=True, which is 
> required for making explicit use of the begin() method, then there is 
> already a transaction in progress and the block will never cause a 
> commit() to occur. 
>
> > But if I change the code to this: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
> > db.session.commit() 
> > 
> > it raises a ResourceClosedError: This transaction is closed 
>
> This is because that code interferes with the state of the context 
> manager.The context manager is referring to the current 
> SessionTransaction upon which it will call commit() at the end of the 
> block.  Because you are calling commit() on it, that effectively ends 
> that SessionTransaction being in a usable state, and the context 
> manager then fails to do its final step. 
>
>
> > 
> > What works for me is this: 
> > 
> > with db.session.begin(subtransactions=True): 
> > # create a model instance 
> > thing = Thing(...) 
> > db.session.add(thing) 
> > db.session.commit() 
> > 
> > Where the commit() is outside the scope of the context manager. But this 
> > seems contrary to me, and makes me think I'm doing something wrong as my 
> > expectation of what 
>
> this sounds a lot like you have the session in its default mode of 
> autocommit=False, which is great, but you shouldn't be using the 
> begin() method. 
>
> If we go to the doc for begin: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=session%20begin#sqlalchemy.orm.session.Session.begin
>  
>
> the second line is: "The Session.begin() method is only meaningful if 
> this session is in autocommit mode prior to it being called; see 
> Autocommit Mode for background on this setting." 
>
> then if we go to autocommit mode at 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit
>  
> there is a giant green/red warning box referring to the whole thing as 
> a legacy pattern.SQLAlchemy back in the 0.1, 0.2 days was 
> developed against Python 2.3/2.4, before context managers existed and 
> also when the Session had a more naive view of transactions and 
> flushing.  Ideally the subtransactions concept would not have been 
> present at all nor would Session have a begin() method. 
>
>
>
>
>
>
> > 
> > with 

Re: [sqlalchemy] Understanding session.begin(subtransactions=True) as a context manager

[Mike Bayer]

On Thu, Sep 28, 2017 at 9:29 AM, Doug Farrell  wrote:
> Hi all,
>
> I'm having trouble understanding how to use
> session.begin(subtransactions=True) as a context manager. I'm working in
> Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on CentOS
> 7 servers. I like session.begin() as a context manager, but things aren't
> working as I thought they would. For example:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)


I would not recommend using the Session in this pattern.Leaving
the session with its default setting of "autocommit=False" means you
never have to call begin() at all; this is desirable because there's
no reason to use a Session outside of an explicit transaction (this
was not the case many years ago which is why begin() remains).
Methods that work with objects should not also be demarcating
transaction scope; there's general discussion of this at
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it.

if you're doing Flask, the commit of the Session should be at the end
of the request, the begin is implicit, and that's it. If you
absolutely need a commit to occur at some special place before you've
reached the official end of the request + commit, you can just simply
call session.commit() at any time.The Session will start a new
transaction if and when additional work is performed with it.

Now, if you need your work to be in an **independent** transaction,
that is, totally separate from the "main" one, then you need to do
your work in a totally different Session object.   This is an
acceptable pattern that is sometimes necessary.


>
> I thought when the context manager went out of scope it would perform a
> db.session.commit() to persist the thing instance, but I haven't seen
> changes to the database.

the commit() will happen if the begin() is the outermost begin().  If
not, then it's one of the "inner" blocks and no commit will occur.
If you haven't placed this session into autocommit=True, which is
required for making explicit use of the begin() method, then there is
already a transaction in progress and the block will never cause a
commit() to occur.

> But if I change the code to this:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)
> db.session.commit()
>
> it raises a ResourceClosedError: This transaction is closed

This is because that code interferes with the state of the context
manager.The context manager is referring to the current
SessionTransaction upon which it will call commit() at the end of the
block.  Because you are calling commit() on it, that effectively ends
that SessionTransaction being in a usable state, and the context
manager then fails to do its final step.


>
> What works for me is this:
>
> with db.session.begin(subtransactions=True):
> # create a model instance
> thing = Thing(...)
> db.session.add(thing)
> db.session.commit()
>
> Where the commit() is outside the scope of the context manager. But this
> seems contrary to me, and makes me think I'm doing something wrong as my
> expectation of what

this sounds a lot like you have the session in its default mode of
autocommit=False, which is great, but you shouldn't be using the
begin() method.

If we go to the doc for begin:

http://docs.sqlalchemy.org/en/latest/orm/session_api.html?highlight=session%20begin#sqlalchemy.orm.session.Session.begin

the second line is: "The Session.begin() method is only meaningful if
this session is in autocommit mode prior to it being called; see
Autocommit Mode for background on this setting."

then if we go to autocommit mode at
http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#session-autocommit
there is a giant green/red warning box referring to the whole thing as
a legacy pattern.SQLAlchemy back in the 0.1, 0.2 days was
developed against Python 2.3/2.4, before context managers existed and
also when the Session had a more naive view of transactions and
flushing.  Ideally the subtransactions concept would not have been
present at all nor would Session have a begin() method.






>
> with db.session.begin(...)
>
> does is this (pseudocode):
>
> begin transaction
> try:
>   create some model instance
>   add them to the session
>   commit handled by leaving the scope of the context manager
> except
>   rollback on exception
>
> It would be very much appreciated is someone could point me in the right
> direction, give me some suggestions or references about what I'm missing.
>
> Thanks,
> Doug
>
>
>
>
>
>
>
> --
> 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 

Re: [sqlalchemy] SQLALchemy lazy compile classes

[Mike Bayer]

On Thu, Sep 28, 2017 at 4:29 AM, Tolstov Sergey  wrote:
> i haven't found solution for this. Problem in very load compile (40 sec).
>  I have a :
>  - more then 1200 classes
> - 300 enums
> - and some custom primitive classes for working
> - 7 levels of polymorphic inherits
> - some itself relationship, may one-to-many and many-to-many in one class to
> itself
> - parents and childs may have somenamed relationship to one table
>
> I undestand, that's big project, but in develop i need load ~100 classes. Do
> the product exists that can do it or i need to create custom class map and
> custom factory?

h by "compile" are you referring to just when you "import" the
Python files and the mappers go into the "compile()" phase?   is it
possible you could organize your imports such that not all 1200
classes are imported every time? to deal with the issue of
linkages between classes, you can have dependent classes add their
dependencies to the classes they depend on only when they are
imported.   Even something like a foreign key column can work this
way:

#  myapp/module_a.py

class SomeImportantClass(Base):
   __tablename__ = "important"

  # ...


# myapp/module_b.py

from myapp import module_a

class SomeNotImportantClass(Base):
   __tablename__ = 'not_important'


module_a.SomeImportantClass.not_important_fk =
Column('not_important_fk', ForeignKey('not_important.id'))

as long as you only import module_a and not module_b, you only get
SomeImportantClass set up and not SomeNotImportantClass.   Assuming
you don't need to refer to the "SomeImportantClass.not_important_fk"
column, it doesn't need to be there, even for inserts where it would
get inserted with NULL.

If you have 1200 classes I'd try to work the modules into clusters
that have a uni-directional dependency chain:


   [super important modules]
   ^
   |
 [usually needed modules]  <-- [ dont need these much]
  ^
  |
 [ dont need these]







>
>
> --
> 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 - 
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.

Re: [sqlalchemy] pyodbc and is_disconnect

[David Moore]

Fantastic, thanks so much for the help, Mike.

Kind regards,


David Moore

Support Technical Lead

j5 Software South Africa

Skype:

Phone:

Email:

Web:

davidm.j5int

+27 21 762 1440

dav...@j5int.com 

www.j5int.com

[image: j5InternationalLogo_small.png]

--

This message is confidential. It may also be privileged or otherwise
protected by work product immunity or other legal rules. If you have
received it by mistake, please let us know by e-mail reply and delete it
from your system; you may not copy this message or disclose its contents to
anyone. Please send us by fax any message containing deadlines as incoming
e-mails are not screened for response deadlines. The integrity and security
of this message cannot be guaranteed on the Internet.You should carry out
your own virus checks before opening any attachments. Opinions, conclusions
and other information that do not relate to the official business of the
company are neither given nor endorsed by it.


On Thu, Sep 28, 2017 at 3:46 PM, Mike Bayer 
wrote:

>
>
> On Thu, Sep 28, 2017 at 3:58 AM, David Moore  wrote:
>
>> Hi,
>>
>> I've recently had an issue with pyodbc not correctly identifying a
>> disconnect exception when connected to a replicating SQL Server cluster
>> with failover. As far as I've been able to ascertain, what happened is that
>> the database failed over, leaving all connections in a weird state. Since
>> sqlalchemy didn't correctly identify the errors as disconnect exceptions,
>> it kept trying to use the connections which never became usable again. We
>> recycle connections after an hour, but that left us with an hour of no
>> database functionality. Production SQLAlchemy version is 1.0.6, but there
>> is no relevant change I can see on latest master.
>>
>> So, I went digging into how sqlalchemy classifies these errors. It seems
>> the only disconnect condition that pyodbc takes special note of is [08S01]
>> (in lib/sqlalchemy/connectors/pyodbc.py). Back before we used
>> sqlalchemy, we collected a more comprehensive set of errors which imply a
>> disconnection event, and I'd love to see these in sqlalchemy. These
>> are '01002', '08003', '08007', '08S01','08S02', '08001', 'HYT00' and
>> 'HY010'.
>>
>> So, two questions (assuming that these additions will be welcome):
>>   * I notice 08S01 is handled in lib/sqlalchemy/connectors/pyodbc.py,
>> whereas pymssql looks for disconnect errors in
>> lib/sqlalchemy/dialects/pymssql.py. Where is the correct place to patch
>> this? The former, or lib/sqlalchemy/dialects/pyodbc.py?
>>
>
> I think the presence of 08S01 in connectors/pyodbc.py is a bug -this error
> code is specific to SQL Server, so should be in dialects/mssql/pyodbc.
> For that and adding the error codes https://bitbucket.org/zzzeek/
> sqlalchemy/issues/4095/sql-server-close-connection-codes-for is added
> should be pushed today.
>
>
>
>>   * Is there a place I can hook or extend or override our current setup
>> to get this detection into production without a full sqlalchemy upgrade
>> testing cycle?
>>
>
> big time, there's an exception catch hook with deep functionality:
>
> http://docs.sqlalchemy.org/en/latest/core/events.html?
> highlight=handle_error#sqlalchemy.events.ConnectionEvents.handle_error
>
> per the example we can illustrate your codes:
>
> @event.listens_for(Engine, "handle_error")def handle_exception(context):
> if isinstance(context.original_exception,
> pyodbc.Error):
>
> for code in ('08S01', '01002', '08003',
> '08007', '08S02', '08001', 'HYT00', 'HY010'):
>
> if code in str(context.original_exception):
>
> context.is_disconnect = True
>
>
>
>
>>
>> Kind regards,
>>
>> David Moore
>>
>> Support Technical Lead
>>
>> j5 Software South Africa
>>
>> Skype:
>>
>> Phone:
>>
>> Email:
>>
>> Web:
>>
>> davidm.j5int
>>
>> +27 21 762 1440 <+27%2021%20762%201440>
>>
>> dav...@j5int.com 
>>
>> www.j5int.com
>>
>> [image: j5InternationalLogo_small.png]
>>
>> --
>>
>> This message is confidential. It may also be privileged or otherwise
>> protected by work product immunity or other legal rules. If you have
>> received it by mistake, please let us know by e-mail reply and delete it
>> from your system; you may not copy this message or disclose its contents to
>> anyone. Please send us by fax any message containing deadlines as incoming
>> e-mails are not screened for response deadlines. The integrity and security
>> of this message cannot be guaranteed on the Internet.You should carry
>> out your own virus checks before opening any attachments. Opinions,
>> conclusions and other information that do not relate to the official
>> business of the company are neither given nor endorsed by it.
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please 

Re: [sqlalchemy] pyodbc and is_disconnect

[Mike Bayer]

On Thu, Sep 28, 2017 at 3:58 AM, David Moore  wrote:

> Hi,
>
> I've recently had an issue with pyodbc not correctly identifying a
> disconnect exception when connected to a replicating SQL Server cluster
> with failover. As far as I've been able to ascertain, what happened is that
> the database failed over, leaving all connections in a weird state. Since
> sqlalchemy didn't correctly identify the errors as disconnect exceptions,
> it kept trying to use the connections which never became usable again. We
> recycle connections after an hour, but that left us with an hour of no
> database functionality. Production SQLAlchemy version is 1.0.6, but there
> is no relevant change I can see on latest master.
>
> So, I went digging into how sqlalchemy classifies these errors. It seems
> the only disconnect condition that pyodbc takes special note of is [08S01]
> (in lib/sqlalchemy/connectors/pyodbc.py). Back before we used sqlalchemy,
> we collected a more comprehensive set of errors which imply a disconnection
> event, and I'd love to see these in sqlalchemy. These are '01002', '08003',
> '08007', '08S01','08S02', '08001', 'HYT00' and 'HY010'.
>
> So, two questions (assuming that these additions will be welcome):
>   * I notice 08S01 is handled in lib/sqlalchemy/connectors/pyodbc.py,
> whereas pymssql looks for disconnect errors in 
> lib/sqlalchemy/dialects/pymssql.py.
> Where is the correct place to patch this? The former, or
> lib/sqlalchemy/dialects/pyodbc.py?
>

I think the presence of 08S01 in connectors/pyodbc.py is a bug -this error
code is specific to SQL Server, so should be in dialects/mssql/pyodbc.
For that and adding the error codes
https://bitbucket.org/zzzeek/sqlalchemy/issues/4095/sql-server-close-connection-codes-for
is added should be pushed today.



>   * Is there a place I can hook or extend or override our current setup to
> get this detection into production without a full sqlalchemy upgrade
> testing cycle?
>

big time, there's an exception catch hook with deep functionality:

http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=handle_error#sqlalchemy.events.ConnectionEvents.handle_error

per the example we can illustrate your codes:

@event.listens_for(Engine, "handle_error")def handle_exception(context):
if isinstance(context.original_exception,
pyodbc.Error):

for code in ('08S01', '01002', '08003',
'08007', '08S02', '08001', 'HYT00', 'HY010'):

if code in str(context.original_exception):

context.is_disconnect = True




>
> Kind regards,
>
> David Moore
>
> Support Technical Lead
>
> j5 Software South Africa
>
> Skype:
>
> Phone:
>
> Email:
>
> Web:
>
> davidm.j5int
>
> +27 21 762 1440 <+27%2021%20762%201440>
>
> dav...@j5int.com 
>
> www.j5int.com
>
> [image: j5InternationalLogo_small.png]
>
> --
>
> This message is confidential. It may also be privileged or otherwise
> protected by work product immunity or other legal rules. If you have
> received it by mistake, please let us know by e-mail reply and delete it
> from your system; you may not copy this message or disclose its contents to
> anyone. Please send us by fax any message containing deadlines as incoming
> e-mails are not screened for response deadlines. The integrity and security
> of this message cannot be guaranteed on the Internet.You should carry out
> your own virus checks before opening any attachments. Opinions, conclusions
> and other information that do not relate to the official business of the
> company are neither given nor endorsed by it.
>
> --
> 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 - 
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] Understanding session.begin(subtransactions=True) as a context manager

[Doug Farrell]

Hi all,

I'm having trouble understanding how to use 
session.begin(subtransactions=True) as a context manager. I'm working in 
Python 2.7.5 with SQLAlchemy 1.1.14 in a Flask 0.12.2 application on CentOS 
7 servers. I like session.begin() as a context manager, but things aren't 
working as I thought they would. For example:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)

I thought when the context manager went out of scope it would perform a 
db.session.commit() to persist the thing instance, but I haven't seen 
changes to the database. But if I change the code to this:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)
db.session.commit()

it raises a ResourceClosedError: This transaction is closed

What works for me is this:

with db.session.begin(subtransactions=True):
# create a model instance
thing = Thing(...)
db.session.add(thing)
db.session.commit()

Where the commit() is outside the scope of the context manager. But this 
seems contrary to me, and makes me think I'm doing something wrong as my 
expectation of what 

with db.session.begin(...) 

does is this (pseudocode):

begin transaction
try:
  create some model instance
  add them to the session
  commit handled by leaving the scope of the context manager
except
  rollback on exception

It would be very much appreciated is someone could point me in the right 
direction, give me some suggestions or references about what I'm missing.

Thanks,
Doug







-- 
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] SQLALchemy lazy compile classes

[Tolstov Sergey]

i haven't found solution for this. Problem in very load compile (40 sec).
 I have a :
 - more then 1200 classes
- 300 enums 
- and some custom primitive classes for working
- 7 levels of polymorphic inherits 
- some itself relationship, may one-to-many and many-to-many in one class 
to itself
- parents and childs may have somenamed relationship to one table

I undestand, that's big project, but in develop i need load ~100 classes. 
Do the product exists that can do it or i need to create custom class map 
and custom factory? 

-- 
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] pyodbc and is_disconnect

[David Moore]

Hi,

I've recently had an issue with pyodbc not correctly identifying a
disconnect exception when connected to a replicating SQL Server cluster
with failover. As far as I've been able to ascertain, what happened is that
the database failed over, leaving all connections in a weird state. Since
sqlalchemy didn't correctly identify the errors as disconnect exceptions,
it kept trying to use the connections which never became usable again. We
recycle connections after an hour, but that left us with an hour of no
database functionality. Production SQLAlchemy version is 1.0.6, but there
is no relevant change I can see on latest master.

So, I went digging into how sqlalchemy classifies these errors. It seems
the only disconnect condition that pyodbc takes special note of is [08S01]
(in lib/sqlalchemy/connectors/pyodbc.py). Back before we used sqlalchemy,
we collected a more comprehensive set of errors which imply a disconnection
event, and I'd love to see these in sqlalchemy. These are '01002', '08003',
'08007', '08S01','08S02', '08001', 'HYT00' and 'HY010'.

So, two questions (assuming that these additions will be welcome):
  * I notice 08S01 is handled in lib/sqlalchemy/connectors/pyodbc.py,
whereas pymssql looks for disconnect errors in
lib/sqlalchemy/dialects/pymssql.py. Where is the correct place to patch
this? The former, or lib/sqlalchemy/dialects/pyodbc.py?
  * Is there a place I can hook or extend or override our current setup to
get this detection into production without a full sqlalchemy upgrade
testing cycle?

Kind regards,

David Moore

Support Technical Lead

j5 Software South Africa

Skype:

Phone:

Email:

Web:

davidm.j5int

+27 21 762 1440

dav...@j5int.com 

www.j5int.com

[image: j5InternationalLogo_small.png]

--

This message is confidential. It may also be privileged or otherwise
protected by work product immunity or other legal rules. If you have
received it by mistake, please let us know by e-mail reply and delete it
from your system; you may not copy this message or disclose its contents to
anyone. Please send us by fax any message containing deadlines as incoming
e-mails are not screened for response deadlines. The integrity and security
of this message cannot be guaranteed on the Internet.You should carry out
your own virus checks before opening any attachments. Opinions, conclusions
and other information that do not relate to the official business of the
company are neither given nor endorsed by it.

-- 
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.

Re: [sqlalchemy] Empty deletion history unless column value accessed between commits (works with flush)

[Levon Saldamli]

Thank you!

Den måndag 25 september 2017 kl. 15:38:59 UTC+2 skrev Mike Bayer:
>
> On Mon, Sep 25, 2017 at 8:29 AM, Levon Saldamli  > wrote: 
> > I have an application which listens for before_flush events and does 
> some 
> > side effects when column values are deleted. But in some cases the 
> history 
> > for an attribute is empty even though it has been deleted. For example: 
> > 
> > person = Person(address="VALUE1") 
> > 
> > self.session.add(person) 
> > self.session.flush() 
> > #self.session.commit() 
> > 
> > # Without the next line, deletion of VALUE1 is not registered if 
> committed 
> > above 
> > #assert person.address 
> > 
> > person.address = None 
> > self.session.commit() 
> > 
> > 
> > Here, "VALUE1" is registered as deleted in the history only if: 
> > 
> > flush is called but not commit, or 
> > person.address is accessed before it is set to None 
> > 
> > Is this a bug or is it expected? 
>
> it is expected, the ORM does not waste a whole SQL round trip to load 
> "VALUE1" that is only to be discarded in the vast majority of cases. 
>  If you want that SQL round trip to occur, set active_history=True on 
> the attribute: 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html?highlight=active_history#using-column-property-for-column-level-options
>  
>
>
> http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html?highlight=active_history#sqlalchemy.orm.column_property.params.active_history
>  
>
>
>
>
> > 
> > 
> > A complete example is attached with some prints that show the attribute 
> > state. 
> > 
> > 
> > SQLAlchemy version 1.1.14 
> > python version 3.6.2 [GCC 4.2.1 Compatible Apple LLVM 8.1.0 
> > (clang-802.0.42)] 
> > 
> > 
> > Best regards 
> > 
> > Levon Saldamli 
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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.