# Sqlite3, connection, and general concepts **URL:** \ **Category:** Python Help\ **Created:** [December 22, 2023, 12:14am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663 "2023-12-22T00:14:51Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![capymind](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/capymind/32/15179_2.png) [@capymind](https://discuss.python.org/u/capymind)\ **Post date:** [December 22, 2023, 12:14am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/1 "2023-12-22T00:14:51Z")
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: ```python 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()`](https://docs.python.org/3/library/contextlib.html#contextlib.closing). > **[sqlite3 — DB-API 2.0 interface for SQLite databases](https://docs.python.org/3/library/sqlite3.html#how-to-use-the-connection-context-manager)** > > Source code: Lib/sqlite3/ SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard ... 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…) ```auto 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! ---
**Author:** ![Rosuav](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/rosuav/32/3429_2.png) [@Rosuav](https://discuss.python.org/u/Rosuav)\ **Post date:** [December 22, 2023, 12:34am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/2 "2023-12-22T00:34:20Z")
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](https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions). 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. ---
**Author:** ![capymind](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/capymind/32/15179_2.png) [@capymind](https://discuss.python.org/u/capymind)\ **Post date:** [December 22, 2023, 6:00am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/3 "2023-12-22T06:00:49Z")
Thanks for your comment. Um… Can I say that ,in the first code snippets(without `closing`), connection is closed after I run the function? ---
**Author:** ![Rosuav](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/rosuav/32/3429_2.png) [@Rosuav](https://discuss.python.org/u/Rosuav)\ **Post date:** [December 22, 2023, 6:15am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/4 "2023-12-22T06:15:20Z")
> [@capymind](#): > > Can I say that ,in the first code snippets(without `closing`), connection is closed after I run the function? 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. ---
**Author:** ![capymind](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/capymind/32/15179_2.png) [@capymind](https://discuss.python.org/u/capymind)\ **Post date:** [December 22, 2023, 6:40am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/5 "2023-12-22T06:40:32Z")
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 ```python 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 ```python 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 ```python 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… ---
**Author:** ![Rosuav](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/rosuav/32/3429_2.png) [@Rosuav](https://discuss.python.org/u/Rosuav)\ **Post date:** [December 22, 2023, 8:30am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/6 "2023-12-22T08:30:50Z")
> [@capymind](#): > > However, when I run > > ```auto > 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` 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. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [December 22, 2023, 10:12am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/7 "2023-12-22T10:12:02Z")
> [@Rosuav](#): > > 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 the docs are confusing, we should try to make them clearer. If you have something in mind, please open a PR 🙂 ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [December 22, 2023, 10:19am UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/8 "2023-12-22T10:19:14Z")
> [@capymind](#): > > However, when I run > > ```auto > 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 `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: ```auto 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)) ``` ---
**Author:** ![Rosuav](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/rosuav/32/3429_2.png) [@Rosuav](https://discuss.python.org/u/Rosuav)\ **Post date:** [December 22, 2023, 12:17pm UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/9 "2023-12-22T12:17:25Z")
> [@erlendaasland](#): > > If the docs are confusing, we should try to make them clearer. If you have something in mind, please open a PR 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 🙂 ---
**Author:** ![Rosuav](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/rosuav/32/3429_2.png) [@Rosuav](https://discuss.python.org/u/Rosuav)\ **Post date:** [December 22, 2023, 12:18pm UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/10 "2023-12-22T12:18:28Z")
> [@erlendaasland](#): > > ```auto > 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) > > ``` I don’t have an easy way to check this off hand, but is it possible to collapse those into one statement? ```auto with closing(sqlite3.connect("/items.db")) as conn, conn: ``` ---
**Author:** ![capymind](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/capymind/32/15179_2.png) [@capymind](https://discuss.python.org/u/capymind)\ **Post date:** [December 22, 2023, 3:10pm UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/11 "2023-12-22T15:10:37Z")
> [@Rosuav](#): > > When you use `with closing(...)` you aren’t using the connection itself as a context manager, only `closing()`; I think that you are 100% right ( I am 100% wrong). I tried following code, working perfectly in the way that I have expected… ```python 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](https://us1.discourse-cdn.com/flex002/uploads/python1/original/3X/2/2/22f1951145ffdb5285f41520b0ea72463dc17081.jpeg) ---
**Author:** ![capymind](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/capymind/32/15179_2.png) [@capymind](https://discuss.python.org/u/capymind)\ **Post date:** [December 22, 2023, 3:16pm UTC](https://discuss.python.org/t/sqlite3-connection-and-general-concepts/41663/12 "2023-12-22T15:16:56Z")
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 !!