Sqlite3, connection, and general concepts

Python Help
1

Hi,
I don’t have solid understanding about context manager, objects in memory etc… so sorry for bothering you if this question doesn’t make sense at all…

Assuming code as follows:

import sqlite3

def get_price(item_code):
    with sqlite3.connect("/items.db") as conn:
        result = conn.execute(
            "SELECT price WHERE item_code=:item_code",
            dict(item_code=item_code)
        )
        return result.fetchone()

When I call above function such as my_price = get_price(42) the Connection(file resource?) still exists? I mean… that I wrote resource inefficient code?

I read the docs and docs say following

Note The context manager neither implicitly opens a new transaction nor closes the connection. If you need a closing context manager, consider using contextlib.closing().

So, should I have coded like that for manging sqlite database file connection resources efficiently?
(To be honest I used the word efficient and resource without what these word actually means…)

import sqlite3
from contextlib import closing

def get_price(item_code):
    with closing(sqlite3.connect("/items.db")) as conn:
        result = conn.execute(
            "SELECT price WHERE item_code=:item_code",
            dict(item_code=item_code)
        )
        return result.fetchone()

I don’t have a degree about computer science and below-average IQ meaning that I have to put efforts to get understanding about general concepts of what opening the file, efficient resource management(what resource itself), etc means.

Besides just code differences, to get understanding of what happens actually, would you recommend the books, references?, or any youtube?

Thanks a lot for reading this question.
Happy Christmas!

2

In the situation you have here, the connection should be closed promptly. The example given in the documentation shows what happens if you have a single connection object and then use it in multiple contexts, and as such, the “context” here refers to a transaction.

The wording is a little confusing there, in that it does not OPEN a transaction, but it does END the transaction as the with block exits - either by rolling back, if any exception happened, or by committing. If you need full control over the beginning and end of a transaction, have a read of this section.

Note that, with other database engines such as PostgreSQL (with the psycopg2 module), the same sort of code WOULD start a transaction as you ender the with block, and that behaviour can also be requested with sqlite3. But with con: is never going to close the connection, as it always implies transactional context instead.

1 Like
3

Thanks for your comment. Um… Can I say that ,in the first code snippets(without closing), connection is closed after I run the function?

4

The connection should be closed once there are no more references to it. But Python doesn’t guarantee that this will happen promptly, so you might get a ResourceWarning, and if you run this in a loop, you might accumulate a lot of spare connections before they get closed. But, most likely, yes, it will be closed after the function returns.

1 Like
5

Oh… It’s more clearer for me. Thank you!!

Um… slightly different situation when I don’t understand at first is… when without closing such as

def insert_price(price):
   with sqlite3.connect("/items.db") as conn:
      conn.execute("INSERT INTO prices(price) VALUES(:price)", dict(price=price))

When I run insert_price(42) I can see the inserted data, meaning with block handle commit for me.

However, when I run

def insert_price(price):
   with closing(sqlite3.connect("/items.db")) as conn:
      conn.execute("INSERT INTO prices VALUES(:price)", dict(price=price))

Nothing happens… I have assumed that closing help me to handle commit and close… However, to insert data I have to write the code such as

def insert_price(price):
   with closing(sqlite3.connect("/items.db")) as conn:
      conn.execute("INSERT INTO prices VALUES(:price)", dict(price=price))
      conn.commit()

means that I have to code “explicitly”… Can I say that when using closing, transactional handling(commit) not working intentionally, so I have to always manually express the commit given default configurations?

Sorry for many dumb following questions…

6

I’m not entirely sure of SQLite’s behaviour if you don’t commit, but my guess, based on the above, is that an unfinished transaction is getting rolled back on close. When you use with closing(...) you aren’t using the connection itself as a context manager, only closing(); you still get the same connection to work with inside the block, but it isn’t triggering the “commit or roll back” logic.

2 Likes
7

If the docs are confusing, we should try to make them clearer. If you have something in mind, please open a PR :slightly_smiling_face:

8

contextlib.closing is a different type of context manager than the sqlite3.Connection context manager. They do different things. The former (closing) closes the thing you ask it to close; the latter (the connection context manager) on the other hand, handles the transaction for you. If you want to use both context managers, you’ll have to use two with statements:

def insert_price(price):
   with closing(sqlite3.connect("/items.db")) as conn:  # <= makes sure conn is closed
      with conn:  # <= makes sure your database transaction is completed (committed or rolled back)
         conn.execute("INSERT INTO prices VALUES(:price)", dict(price=price))
1 Like
9

The confusing part is that transactions in general aren’t obvious, since there are so many different options like autocommit. A cursory glance at the docs simply isn’t going to be sufficient to properly comprehend whether or not you have a transaction open. So I’m not sure that it’s the docs’ fault that it’s confusing - it’s like blaming a physics textbook for the universe being a confusing place :slight_smile:

1 Like
10

I don’t have an easy way to check this off hand, but is it possible to collapse those into one statement?

with closing(sqlite3.connect("/items.db")) as conn, conn:
2 Likes
11

I think that you are 100% right ( I am 100% wrong). I tried following code, working perfectly in the way that I have expected…

with closing(conn) as conn:
    with conn:
        conn.execute("INSERT INTO price values(:price)", dict(price=price))

The code above actually is inserting the record!!

To be honest, I don’t understand the code in the sense of what really is happening. It’s definitely my faults (I have to read docs…). I think I have the mis-understanding that Python is for everybody (espically who have crude understanding of Python) it is (very) high-level language that you can do anything easily if you are smart enough.

image
image1035×754 28.7 KB

1 Like
12

Yeah… You are totally 100% right. Thanks a lot for your comments that make me code in the way what I want. I think I have to study(learn) Python itself seriously to use it in correct way. Thanks !!

1 Like