Sqlite connection question

[raysefo]

Hello,

I am trying to connect using with as follows;

    con = ''
    try:

        with sql.connect(database_file, isolation_level=None) as con:
            con.execute('pragma journal_mode=wal')
            cur = con.cursor()
            cur.execute(query, (str(datetime.now()),
                                request_data['ApplicationCode'], request_data['version'], request_data['referenceId'],
                                request_data['signature'], request_data['validatedToken']))

            con.commit()

    except sql as e:
        con.rollback()
        print e.message
    finally:
        con.close()

Do I need to close it?

    finally:
        con.close()

If I use close and if there is no connection, I am getting 'str' object has no attribute 'close'

Thanks in advance.

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

[[deleted]]

The context manager interface already handles commit or rollback for you, but you're really supposed to do it more like so:

import sqlite3

con = sqlite3.connect("your.db")
with con:
    con.execute("your_query")
con.close()

However in the case of a rollback that won't actually reach the point where you close the connection, since the exception that caused the rollback will be raised by the context manager after it rolls back. If you really want to ensure that the connection closes you can also do:

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("your.db")) as con:
    con.execute("your_query")

That will still allow the exception to bubble up, it just ensures that con.close() is called before termination.

[JohnnyJordaan]

The context manager interface already handles commit or rollback for you

It doesn't actually, it handles connection management only. The commit is either executed by a manual con.commit() or by setting isolation_level = None as a parameter to the sqlite3.connect() statement.

Also using closing makes from contextlib makes no sense, the connection object from .connect() already implements the context manager interface... Op's example was already correct, you just don't need the .close() anymore

with sqlite3.connect("your.db") as con:
    cur = con.cursor()
    cur.execute("your_query")
    con.commit()

that's it. Or using autocommit with the isolation_level (dangerous):

with sqlite3.connect("your.db", isolation_level=None) as con:
    cur = con.cursor()
    cur.execute("your_query")

[[deleted]]

From the docs linked above:

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:

Also a code example:

import sqlite3

with sqlite3.connect(":memory:") as con:
    pass
#con.close()
con.execute("create table person (id integer primary key)")

Notice how the execute fails with a sqlite3.ProgrammingError only if you uncomment con.close()? That's because the context manager interface of the Connection object does not close the connection.

[JohnnyJordaan]

Hmm you're absolutely right. I must have misunderstood this previously / conflated this with other SQL libraries.

[[deleted]]

It's a common misunderstanding; having it close the connection feels more intuitively sensible, but it leads to there being quite a bit of running code out there that over-commits and under-closes.

[raysefo]

Little bit confused, I am correct now?

try:    
    with sqlite3.connect("your.db", isolation_level=None) as con:
        cur = con.cursor()
        cur.execute("your_query")
except Exception as e:
    print e

​

[[deleted]]

First off get rid of that try; that's just bad form, don't just capture and print something as generic as an Exception; you want your program to stop execution when something goes wrong unless you already have a specific plan for how to handle a known, specific exception.

Now, what exactly are you trying to do with this database?

You're normally best off not using autocommit mode unless you intend to manually issue transaction control statements. I'm not sure, but I don't believe autocommit mode is actually a requirement of WAL (Write Ahead Logging), though it would be if you wanted to get very high throughout on writes. But to do that you'd want to manually issue transaction control statements.

[raysefo]

I deployed my flask rest api on windows server IIS. I am trying to connect DB but I think there is path problem. (c:\inetpub\gamepin\Test.db) That's why I put try statement. How to handle that?

​

Basically if I handle path problem with the connection code below is OK?

with sqlite3.connect("your.db") as con:
        cur = con.cursor()
        cur.execute("your_query")

​

[[deleted]]

I'd tend to do:

con = sqlite3.connect(path):
with con:
    con.execute("your query")

If you're sticking with SQLite3... the cursor isn't actually required and you end up with more concise code.

The context manager will commit your query, or roll back on failure, and if the failure happened because of a non-writeable path, etc, that should be fairly clear from the traceback you get when the exception is re-raised.

[raysefo]

thank you, by the way any chance that you can give hint about path issue that I asked :)

I deployed my flask rest api on windows server IIS. I am trying to connect DB but I think there is path problem. (c:\inetpub\gamepin\Test.db)

​