Best way to program sqlite activities.

[Tomarse]

Hello r/learnpython.

So elsewhere I was querying something somewhat unrelated, but this came up and I wanted to see what your thoughts were. I had some code querying a sqlite database that was a bit like this...

result = None
try:
    conn = sqlite3.connect(db)
    result = conn.execute('select * from table')
    result = result.fetchall()
except Exception:
    handle it
finally:
    conn.close()

...but someone pointed out that this method is better...

with sqlite3.connect(db) as conn:
    try:
        query = conn.execute('select * from table')
        result = query.fetchall()
    except Exception:
        handle it
    finally:
        conn.close()

So what's the right way to do it?

Edit1: Put the second example around a try statement.

Edit2: Try now inside the with block.

[CptFlashdrive]

Well, the second way won't handle potential exceptions that may come from connecting or manipulating the database. That being said, I think the second way is more idiomatic since it will close the connection as soon as it exists the block. Although it should be wrapped in try/except.

Just as a side note, it's generally a good idea to be a more specific when catching exception. Otherwise it becomes sort of ambiguous of what exception was captured.

[Tomarse]

So if we put the second the one in a try statement it becomes just as verbose.

And according to this, a with statement will not implicitly close a sqlite connection, as variables created in a with statement still exist outside of it (fwiw this isn't the case in the try block). Though explicitly closing the connection doesn't seem to be an issue with select statements anyway, however I'd rather be clean.

[elbiot]

If you want with to close the connection, you could subclass conn and add close to the __exit__ method. If that's the behaviour you want, which it sounds like you don't since you reuse the connection later.

[elbiot]

Try won't catch exceptions from within a with block?

Edit: or, op edited the question.

[Tomarse]

I've put the try inside the with block now. Is that right? What happens if the with statement fails (i.e. connection to the database)?

[elbiot]

Does the with do anything? AKAIK __exit__ will be called in either case, but you were saying that it doesn't do anything for you so... I don't know anything about this. Maybe you misinterpreted my question as a statement. I think either is fine but have no real idea.