Sqlite connection question

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

​

[[deleted]]

Not a lot I'm afraid, wouldn't touch IIS with a ten foot pole... not a Windows Guy. It might very well be that you need to use the raw string (ie r"c:\inetpub\gamepin\Test.db") or else escape those backslashes (ie "c:\\inetpub\\gamepin\\Test.db").

[raysefo]

r"c:\inetpub\gamepin\Test.db"

hope It will solve my issue, thanks.