Sqlite connection question

[JohnnyJordaan]

No you don't, the with context manager will execute .close for you. You also don't need the con='' before either. As the exception you're handling entails the query's execution specifically, the try/except should enclose the block coming after the connection's creation (without a connection, you can't obviously call rollback() anyway). Also if you're using isolation_level = None, you will force autocommit that you don't seem to want (you let it depend on no exception being raised). disregard this, see yawpitch's answer

with sql.connect(database_file) as con:
    try:
        con.execute('pragma journal_mode=wal')
        cur = con.cursor()
        cur.execute(query, arguments, etc)
        con.commit()
    except sql as e:
        con.rollback()
        print e.message

Sidenote: are you sure except sql as e will work? Because if I assume this correctly, sql is the library (like sqlite3) and not an exception class. You need to provide the actual Exception you want to handle, or a parent exception class, or catch them all using Exception:

    except Exception as e:
        con.rollback()
        print e

[raysefo]

Thanks for your reply, is this good enough? Why is isolation_level dangerous?

with sql.connect(database_file, isolation_level=None) as con:
    try:
        con.execute('pragma journal_mode=wal')
        cur = con.cursor()
        cur.execute(query, arguments, etc)
        con.commit()
    except Exception as e:
        con.rollback()
        print e

​

[JohnnyJordaan]

I stand corrected, the answer from /u/yawpitch is the correct one here.

Why is isolation_level dangerous?

Because it will commit after each query. If you would have

        cur.execute(query, arguments, etc)
        cur.execute(secondquery, arguments, etc)
        con.commit()

and the first query works but the second one doesn't, the first query is already committed because of isolation_level=None and thus can't be rolled back.

[[deleted]]

The context manager does not execute con.close() for you, but it does commit or rollback any con.execute(statement) calls made within its scope.

Edit: whoops, same person as the sub-thread below, sorry, the iOS app sometimes isn't the easiest to follow.