在python中使用带有“WITH”关键字的sqlite3

I was doing a tutorial and came across a way to handle connections with sqlite3, Then I studied about the WITH keyword and found out that it is an alternative to try,except,finally way of doing things

我正在做一个教程并遇到一种方法来处理与sqlite3的连接，然后我研究了关键字并发现它是一个替代尝试，除了，最后的做事方式

It was said that in case of file-handling, 'WITH' automatically handles closing of files and I thought similar with the connection as said in zetcode tutorial:-

有人说，在文件处理的情况下，'WITH'会自动处理文件的关闭，我认为与zetcode教程中所说的连接类似： -

"With the with keyword, the Python interpreter automatically releases the resources. It also provides error handling." http://zetcode.com/db/sqlitepythontutorial/

“使用with关键字，Python解释器会自动释放资源。它还提供错误处理。” http://zetcode.com/db/sqlitepythontutorial/

so I thought it would be good to use this way of handling things, but I couldn't figure out why both (inner scope and outer scope) statements work? shouldn't the WITH release the connection?

所以我认为使用这种处理方式会很好，但我无法弄清楚为什么两个（内部范围和外部范围）语句都有效？不应该WITH释放连接？

import sqlite3

con = sqlite3.connect('test.db')

with con:    
    cur = con.cursor()    

    cur.execute('SELECT 1,SQLITE_VERSION()')
    data = cur.fetchone()   
    print data        

cur.execute('SELECT 2,SQLITE_VERSION()')
data = cur.fetchone()
print data

which outputs

哪个输出

(1, u'3.6.21')
(2, u'3.6.21')

I don't know what exactly the WITH is doing here(or does in general), so, if you will please elaborate on the use of with over TRY CATCH in this context.

我不知道WITH在这里做了什么（或者一般情况下），所以，如果你愿意请详细说明在这种情况下使用over TRY CATCH。

And should the connections be opened and closed on each query? (I am formulating queries inside a function which I call each time with an argument) Would it be a good practice?

是否应该在每个查询中打开和关闭连接？（我在一个函数内部构造查询，我每次都用一个参数调用）这是一个好习惯吗？

3 个解决方案

#1

From the docs: http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

来自文档：http：//docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

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:

连接对象可以用作自动提交或回滚事务的上下文管理器。如果发生异常，则回滚事务;否则，交易承诺：

So, the context manager doesn't release the connection, instead, it ensures that any transactions occurring on the connection are rolled back if any exception occurs, or committed otherwise... Useful for DELETE, UPDATE and INSERT queries for instance.

因此，上下文管理器不会释放连接，而是确保在发生任何异常时回滚连接上发生的任何事务，或者以其他方式提交...例如，对DELETE，UPDATE和INSERT查询很有用。

#2

In general, a context manager is free to do whatever its author wants it to do when used. Set/reset a certain system state, cleaning up resources after use, acquiring/releasing a lock, etc.

通常，上下文管理器可以*地执行作者在使用时要做的任何事情。设置/重置某个系统状态，使用后清理资源，获取/释放锁等。

In particular, as Jon already writes, a database connection object creates a transaction when used as a context manager. If you want to auto-close the connection, you can do

特别是，正如Jon已经写过的那样，数据库连接对象在用作上下文管理器时会创建一个事务。如果要自动关闭连接，则可以执行此操作

with contextlib.closing(sqlite3.connect('test.db')) as con:
    with con as cur:
        cur.execute('SELECT 1,SQLITE_VERSION()')
        data = cur.fetchone()   
        print data        

    with con as cur:
        cur.execute('SELECT 2,SQLITE_VERSION()')
        data = cur.fetchone()
        print data

#3

You could also write your own wrapper around sqlite3 to support with:

您也可以在sqlite3周围编写自己的包装器以支持：

class SQLite():
    def __init__(self, file='sqlite.db'):
        self.file=file
    def __enter__(self):
        self.conn = sqlite3.connect(self.file)
        self.conn.row_factory = sqlite3.Row
        return self.conn.cursor()
    def __exit__(self, type, value, traceback):
        self.conn.commit()
        self.conn.close()

with SQLite('test.db') as cur:
    print(cur.execute('select sqlite_version();').fetchall()[0][0])

https://docs.python.org/2.5/whatsnew/pep-343.html#SECTION000910000000000000000

#1