Please elaborate on the use of `WITH` over `TRY CATCH` in this context

Question

To handle connections with SQLite3 I studied the WITH keyword and found out it is an alternative to try,except,finally. It was said in case of file-handling, 'WITH' automatically handles closing files and I thought similar with the connection as said in zetcode tutorial:

"With the with keyword, the Python interpreter automatically releases the resources. It also provides error handling."

I couldn't figure out why both (inner scope and outer scope) statements work. Shouldn't WITH release the connection?

import sqlite3

con = sqlite3.connect('test.db')

with con:    
    cur = con.cursor()    

    cur.execute('SELECT 1,SQLITE_VERSION()')
    data = cur.fetchone()   
    print data        
    
cur.execute('SELECT 2,SQLITE_VERSION()')
data = cur.fetchone()
print data

Which outputs:

(1, u'3.6.21')
(2, u'3.6.21')

I don't know what exactly WITH is doing. Please elaborate on the use of WITH over TRY CATCH in this context. And should connections be opened and closed on each query (I am formulating queries inside a function which I call each time with an argument)?

Have a read of: http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager - that should explain your question — Jon Clements, Oct 22 '13 at 15:49
When using 'with' statement, on exit it commits and do not close the connection. — onlyvinish, May 10 '22 at 05:45

score 24 · Answer 1 · edited Jun 20 '23 at 06:57

24

In general, a context manager is free to do whatever its author wants it to do when used. Set/reset a certain system state, cleaning up resources after use, acquiring/releasing a lock, etc.

In particular, as Jon already writes, a database connection object creates a transaction when used as a context manager. If you want to auto-close the connection, you can do

with contextlib.closing(sqlite3.connect(':memory:')) as conn:
    with conn as cur:
        data = cur.execute('SELECT 1, SQLITE_VERSION()').fetchone()
        print(data)  # (1, "3.39.3")

    with conn as cur:
        [[two, version]] = cur.execute('SELECT 2, SQLITE_VERSION()')
        print(two, version)  # 2 3.39.3

edited Jun 20 '23 at 06:57

Community

1
1

answered Oct 22 '13 at 17:38

glglgl

89,107
13
149
217

2

In python 3, this seems to give `AttributeError: 'sqlite3.Connection' object has no attribute 'fetchone'`. https://stackoverflow.com/a/47501337/2786884 seems to solve the problem – Løiten Sep 30 '19 at 06:50
@Løiten More or less, yes. This other answer doesn't retrieve data, however. So it doesn't really solve the problem with `.fetchone()`. I don't have a solution at hand currently for that, as I don't have much experience with Py3 and SQlite. – glglgl Sep 30 '19 at 09:13

score 20 · Answer 2 · answered Jun 01 '18 at 14:19

20

You could also write your own wrapper around sqlite3 to support with:

class SQLite():
    def __init__(self, file='sqlite.db'):
        self.file=file
    def __enter__(self):
        self.conn = sqlite3.connect(self.file)
        self.conn.row_factory = sqlite3.Row
        return self.conn.cursor()
    def __exit__(self, type, value, traceback):
        self.conn.commit()
        self.conn.close()

with SQLite('test.db') as cur:
    print(cur.execute('select sqlite_version();').fetchall()[0][0])

https://docs.python.org/2.5/whatsnew/pep-343.html#SECTION000910000000000000000

answered Jun 01 '18 at 14:19

DSchmidt

1,095
9
14

1

is it necessary to do self.conn.commit()? With doesn't commit automatically? – cikatomo May 08 '20 at 19:12
2

@cikatomo `with` does only what we tell it to do. The `SQLite` class tells `with` what to do. If you want `with` to commit, you have to do it on `__exit__()` – DSchmidt May 12 '20 at 10:02
Maybe do `return self.conn`. Then you can do `with SQLite('test.db') as conn:` and then create a new cursor for every transaction (`with conn as cur:`) and can omit the `self.conn.commit()` (which anyway should be called in the error-free case only) – glglgl Oct 25 '21 at 12:07
Commit shouldn't do anything if there is nothing valid to commit. You could just wrap the connection but I wanted fast direct access for single access and then forget about the file handle. – DSchmidt Oct 26 '21 at 15:45
Similar to [bottle Sqlite decorator](https://github.com/bottlepy/bottle-sqlite/blob/master/bottle_sqlite.py). See `SQLitePlugin.wrapper()` – radioxoma Aug 16 '22 at 08:11

score 17 · Accepted Answer · answered Oct 22 '13 at 15:51

17

From the docs: http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

So, the context manager doesn't release the connection, instead, it ensures that any transactions occurring on the connection are rolled back if any exception occurs, or committed otherwise... Useful for DELETE, UPDATE and INSERT queries for instance.

answered Oct 22 '13 at 15:51

Jon Clements

138,671
33
247
280

so no releasing of resources or error handling as advertised then? – Aavaas Oct 22 '13 at 16:08
2

The tutorial you're reading is a bit misleading. There is no explicit closure of the connection when using the default context manager. You may want to look at `contextlib.closing`... – Jon Clements Oct 22 '13 at 16:15
agreed, it actually says.... Note The context manager neither implicitly opens a new transaction nor closes the connection. – John Henckel Sep 02 '22 at 16:25

Please elaborate on the use of `WITH` over `TRY CATCH` in this context

3 Answers3

Linked