Python implements SQLITE3 transaction mechanism using Context Manager

Title, this article records how the Python context manager is used to manage the creation and deallocation of Sqlite3 handles and the transaction mechanism.

1. Python context management (with)

Python context management, which solves such problems, requires some preparation before entering logic and requires some aftercare before exiting logic, which can make this scenario clear and manageable.

The WITH statement is a basic use of Python context management, such as reading and writing files

With open ('filea', R) as F:    F.readlines ()

File uses a context-management mechanism so that it does not require additional effort to open file handles and dispose of file handles.

2, Sqlite3

Sqlite3 is an embedded file database that allows you to implement database operations with file reads without having to open additional processes and ports. The pros are lightweight and support advanced features such as transactions and triggers.

Sqlite3 is similar to MySQL's performance in Python handle creation and management.

3. Code

Let's first post the code outlined in this article, and then we'll explain it in detail later.

#-*-coding:utf-8-*-ImportSqlite3ImportTracebackclassSqlitedb (object):def __init__(Self, database='Sqlitedb', isolation_level="', ignore_exc=False): Self.database=Database Self.isolation_level=Isolation_level Self.ignore_exc=Ignore_exc self.connection=None self.cursor=Nonedef __enter__(self):Try: Self.connection= Sqlite3.connect (Database=self.database, isolation_level=self.isolation_level) Self.cursor=self.connection.cursor ()returnSelf.cursorexceptException, Ex:traceback.print_exc ()Raiseexdef __exit__(self, exc_type, Exc_val, EXC_TB):Try:            if  notExc_type isNone:self.connection.rollback ()returnSelf.ignore_excElse: Self.connection.commit ()exceptException, Ex:traceback.print_exc ()Raiseexfinally: Self.cursor.close () self.connection.close ()

We give a case for use

if __name__=='__main__':    #Build TableWith Sqlitedb ('Test') as Db:db.execute ('CREATE table if not exists user (ID integer PRIMARY KEY autoincrement, name VARCHAR (+), age INTEGER)')    #creates a record that can test for rollback of a transaction if an exception is thrownWith Sqlitedb ('Test') as Db:db.execute ('INSERT into User (name, age) VALUES (?,?)', ('Tom', 10))        #raise Exception ()    #Query RecordsWith Sqlitedb ('Test') as Db:query_set= Db.execute ('SELECT * from user where name=? limit?', ('Tom', 100,)). Fetchall ()PrintLen (query_set) forIteminchQuery_set:PrintItem#Deleting RecordsWith Sqlitedb ('Test') as Db:query_set= Db.execute ('Delete from user where name=?', ('Tom',))

You can see that the handle to the database is opened with the With statement, and after the database operation, we do not manage the release of the handle and the transaction rollback.

The output of the code is:

1(6, u'Tom', 10)

When you open the comment for raise Exception (), it indicates that an exception was encountered during the insertion. The uncommitted data in all connection will be rolled back.

So how do they do that?

Context management is implemented through the __enter__ and __exit__ two magic functions in the class Sqlitedb.

1. The Enter function, which is used to implement the preparation for processing before entering With_body, is to create the connect and Cursor,enter methods to return the cursor.

If the ENTER function has a return value, it can be assigned to the variable following the AS, and if not, you can simply remove the AS clause. We give an example without an as clause

Lock = Threading. Lock () with Lock:    Pass

If the Enter function throws an exception, the exception is thrown when the With statement is executed, and the program is interrupted.

2, logically, after the Enter function, began to execute the code inside the With_body, the code in the With_body contains SQL statements and some business logic, here, as long as the exception is thrown to trigger a transaction rollback mechanism, It does not distinguish between the SQL statement execution exception or the exception that occurs in the business logic.

3. The Exit Function executes the Exit function after the With_body executes successfully or throws an exception.

The Exit function passes in three variables, namely the Exc_type exception type, the Exc_val exception value, and the EXC_TB error stack information. If the program is normal, then the three values are none, if not none, then you can judge that With_body produced an exception.

Here, we determine whether Exc_type is none, to distinguish whether an exception is thrown, and if we throw an exception we use Connection.rollback to do the rollback of the transaction, otherwise we use Connection.commit to commit the transaction.

Note that in the event of an exception, a IGNORE_EXC is returned, and if true, the exception is ignored, the exception will not be thrown to the superior, and if none or false is returned, the exception will be thrown up. In fact, we still hope that the exception can be run out, easy to handle, so here we default to False.

Attention:

Isolation_level This field is the isolation level, here we do not do in-depth instructions. What you need to know is this field.

1) Pass in an empty string ' to commit a commit manually, which requires the execution connection.commit shown in the program to commit the transaction, and the DML statement in SQL will take effect.

2) Pass in none, which means that automatic commit is turned on, and commits are automatically committed at this time without the need to connection.commit the transaction in the program.

Python implements SQLITE3 transaction mechanism using Context Manager