I am doing this:
conn = sqlite3.connect(db_filename)
with conn:
cur = conn.cursor()
cur.execute( ... )
with automatically commits the changes. But the documentation says nothing about closing the connection. I can use conn in later statements, hence the context manager is not closing the connection.
Do I have to manually close the connection? What if I leave it open?
In answer to the specific question of what happens if you do not close a SQLite database, the answer is quite simple and applies to using SQLite in any programming language. When the connection is closed explicitly by code or implicitly by program exit then any outstanding transaction is rolled back. (The rollback is actually done by the next program to open the database.) If there is no outstanding transaction open then nothing happens.
This means you do not need to worry too much about always closing the database before process exit, and that you should pay attention to transactions making sure to start them and commit at appropriate points.
Edit: "implicit transactions" are enabled by default starting with Python 3.11 according to PEP-249.
This means that a transaction is started during sqlite3.connect().
Edit: Read https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.isolation_level and set transaction control to manual, typically isolation_level=None, after which the below applies.
You have a valid underlying concern here, however it's also important to understand how sqlite operates too:
1. connection open
2. transaction started
3. statement executes
4. transaction done
5. connection closed
in terms of data correctness, you only need to worry about transactions and not open handles. sqlite only holds a lock on a database inside a transaction(*) or statement execution.
however in terms of resource management, e.g. if you plan to remove sqlite file or use so many connections you might run out of file descriptors, you do care about open out-of-transaction connections too.
there are two ways a connection is closed: either you call .close() explicitly after which you still have a handle but can't use it, or you let the connection go out of scope and get garbage-collected.
if you must close a connection, close it explicitly, according to Python's motto "explicit is better than implicit."
if you are only checking code for side-effects, letting a last variable holding reference to connection go out of scope may be acceptable, but keep in mind that exceptions capture the stack, and thus references in that stack. if you pass exceptions around, connection lifetime may be extended arbitrarily.
caveat programmator, sqlite uses "deferred" transactions by default, that is the transaction only starts when you execute a statement. In the example above, transaction runs from 3 to 4, rather than from 2 to 4.
sqlite3.connect(':memory:'))? I'd expect no file descriptors to be used in that scenario.
:memory: databases. I can't be sure about internal resources though, I mean, memory after all :) Note that in-memory databases are deleted when last connection is closed, so for the OP use-case and :memory:, they'd have to keep at least 1 connection open to keep the data around.This is the code that I use. The Connection and the Cursor will automatically close thanks to contextlib.closing(). The Connection will automatically commit thanks to the context manager.
import sqlite3
import contextlib
def execute_statement(statement):
with contextlib.closing(sqlite3.connect(path_to_file)) as conn: # auto-closes
with conn: # auto-commits
with contextlib.closing(conn.cursor()) as cursor: # auto-closes
cursor.execute(statement)
with? To free some memory? What kind of problems can you run into if you don't use that context manager? Wouldn't just returning from the function be an equivalent if you don't return the cursor?
Cursor object is good practice to prevent operation errors. See this post for more information.__del__ are for. Dima's 2nd last paragraph is disturbing, because it suggests a flaw of the language.
with and instead directly do conn.execute() inside the second with and a try. Official Python documentation in § How to use connection shortcut methods calls out code can be written more concisely because you don’t have to create the (often superfluous) Cursor objects explicitly. § How to use the connection context manager is also a recommended read under § How-to guides.You can use a with block like this:
from contextlib import closing
import sqlite3
def query(self, db_name, sql):
with closing(sqlite3.connect(db_name)) as con, con, \
closing(con.cursor()) as cur:
cur.execute(sql)
return cur.fetchall()
all safe in both happy and exceptional cases
con in with closing(sqlite3.connect(db_name)) as con, con?Your version leaves conn in scope after connection usage.
EXAMPLE:
your version
conn = sqlite3.connect(db_filename) #DECLARE CONNECTION OUT OF WITH BLOCK
with conn: #USE CONNECTION IN WITH BLOCK
cur = conn.cursor()
cur.execute( ... )
#conn variable is still in scope, so you can use it again
new version
with sqlite3.connect(db_filename) as conn: #DECLARE CONNECTION AT START OF WITH BLOCK
cur = conn.cursor()
cur.execute( ... )
#conn variable is out of scope, so connection is closed
# MIGHT BE IT IS NOT CLOSED BUT WHAT Avaris SAID!
#(I believe auto close goes for with block)
For managing a connection to a database I usually do this,
# query method belonging to a DB manager class
def query (self, sql):
con = sqlite3.connect(self.dbName)
with con:
cur = con.cursor()
cur.execute(sql)
res = cur.fetchall()
if con:
con.close()
return res
doing so, I'm sure that the connection is explicitly closed.
sqlite3does that). But better to be safe than sorry. Close your connections when you will no longer use them.