sqlanydb.connect() behavior in with statement

[jysue]

When I connect explicitly, conn = sqlanydb.connect(), type(conn) is <class 'sqlanydb.Connection'>.

If I have the connect in a with statement, with sqlanydb.connect() as conn: type(conn) is <class 'sqlanydb.Cursor'>.

Is this the intended effect? I can live with this. testsqlanywith.pdf

Thank you, Jeffrey Sue

[gperrow-SAP]

Yes, this is intended behaviour. The idea is for something like:

with sqlanydb.connect( ... ) as c:
   c.execute( 'insert into mytable...' )

This is a quick way to create a connection and execute a single statement. We automatically commit the statement if it succeeds and rollback if an exception occurs.

[jysue]

Thank you for the quick answer.

On Mar 19, 2017, at 8:06 AM, Graeme Perrow notifications@github.com wrote:

Yes, this is intended behaviour. The idea is for something like:

with sqlanydb.connect( ... ) as c: c.execute( 'insert into mytable...' )

This is a quick way to create a connection and execute a single statement. We automatically commit the statement if it succeeds and rollback if an exception occurs.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlanywhere/sqlanydb/issues/8#issuecomment-287635076, or mute the thread https://github.com/notifications/unsubscribe-auth/AE0Yx4gK88qGz7jE0GwqM3XWysREeKQ2ks5rnW64gaJpZM4Mhk30.

[gperrow-SAP]

My apologies, I've found that this is not expected behaviour. With the example I gave above, the insert will work and be committed, but the connection will not be closed when you expect it to be. Something like this will work:

conn = sqlanydb.connect( ... )
with conn as c:
    c.execute( 'insert into mytable...' )
conn.close()

But this seems odd. It would make more sense to me to support:

with sqlanydb.connect( ... ) as conn:
    curs = conn.cursor()
    curs.execute( 'insert into mytable...' )
    curs.close()
    # Other things using conn

Where the connection is closed at the end of the with statement. Let me know if I've misunderstood something, but it seems to me that the second example above would be the more intuitive behaviour.

[gperrow-SAP]

After some more research, I've found that there is no "standard" for how a database API module should handle the with statement. The behaviour we've implemented (which I said was "not expected behaviour" above) is the "usual" behaviour according to this Stack Overflow answer. Given this, and given the fact that this is how our driver is currently implemented, I think it would be safer to leave the behaviour as-is rather than risk breaking existing applications that use this behaviour. Sorry for the confusion.

[jysue]

Thank you for researching. The Stack Overflow answer makes a lot of sense.

Thank you, Jeffrey Sue

On Mar 20, 2017, at 3:25 AM, Graeme Perrow notifications@github.com wrote:

After some more research, I've found that there is no "standard" for how a database API module should handle the with statement. The behaviour we've implemented (which I said was "not expected behaviour" above) is the "usual" behaviour according to this Stack Overflow answer http://stackoverflow.com/a/15568275/1821. Given this, and given the fact that this is how our driver is currently implemented, I think it would be safer to leave the behaviour as-is rather than risk breaking existing applications that use this behaviour. Sorry for the confusion.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlanywhere/sqlanydb/issues/8#issuecomment-287757613, or mute the thread https://github.com/notifications/unsubscribe-auth/AE0Yx8XaBQnNnziGDlmlVk1J9KLygsxSks5rnn4zgaJpZM4Mhk30.

[jysue]

I think I understand more now. Looking at the source for sqlanydb, particularly the Connection methods enter and exit, the use of with is supposed to be only for transactions, and not for closing connections or cursors. Sqlanydb follows this Stack Overflow answer http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb/22618781#22618781 and section 8.1 of What’s New in Python 2.5 http://pys60.garage.maemo.org/doc/whatsnew/pep-343.html.

Thanks again.

On Mar 20, 2017, at 7:37 AM, Jeffrey Sue jeffreysue@gmail.com wrote:

Thank you for researching. The Stack Overflow answer makes a lot of sense.

Thank you, Jeffrey Sue

On Mar 20, 2017, at 3:25 AM, Graeme Perrow <notifications@github.com mailto:notifications@github.com> wrote:

After some more research, I've found that there is no "standard" for how a database API module should handle the with statement. The behaviour we've implemented (which I said was "not expected behaviour" above) is the "usual" behaviour according to this Stack Overflow answer http://stackoverflow.com/a/15568275/1821. Given this, and given the fact that this is how our driver is currently implemented, I think it would be safer to leave the behaviour as-is rather than risk breaking existing applications that use this behaviour. Sorry for the confusion.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/sqlanywhere/sqlanydb/issues/8#issuecomment-287757613, or mute the thread https://github.com/notifications/unsubscribe-auth/AE0Yx8XaBQnNnziGDlmlVk1J9KLygsxSks5rnn4zgaJpZM4Mhk30.