如果我不关闭 PythonSQLite 中的数据库连接会怎样

我正在做这样的事情..。

conn = sqlite3.connect(db_filename)


with conn:
cur = conn.cursor()
cur.execute( ... )

with会自动提交更改，但是文档没有提到关闭连接。

实际上，我可以在后面的语句中使用 conn(我已经测试过了)。因此，上下文管理器似乎没有关闭连接。

我必须手动关闭连接。如果我让它开着呢？

剪辑

我的发现是:

  • 在 连接未关闭的上下文管理器中，我已经测试并确认了它。在 __exit__上，上下文管理器仅通过执行 conn.commit()提交更改
  • with conn和 with sqlite3.connect(db_filename) as conn是相同的，因此使用任何一个都将保持连接活动
  • with语句不会创建新的作用域，因此在 with 套件中创建的所有变量都可以在其外部访问
  • 最后，应该手动关闭连接
77678 次浏览
小开

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)
小开
最佳答案

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.

小开

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.

小开

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.

小开

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()
  • connects
  • starts a transaction
  • creates a db cursor
  • performs the operation and returns the results
  • closes the cursor
  • commits/rolls-back the transaction
  • closes the connection

all safe in both happy and exceptional cases

小开

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)

提交答案