A tutorial on writing database modules in Python

This article mainly introduced in Python to write a database module tutorial, this article code based on python2.x version, the need for friends can refer to the

In a web app, all data, including user information, published logs, comments, and so on, are stored in the database. In Awesome-python-app, we chose MySQL as the database.

There are many places in the Web app that have access to the database. Accessing the database requires creating a database connection, a cursor object, and then executing the SQL statement, finally handling the exception, and cleaning up the resource. The code that accesses the database, if dispersed into functions, is bound to be unable to maintain or facilitate code reuse.

In addition, in a web app, there are multiple users accessing the system in multiple processes or multi-threaded mode to process each user's request. Assuming that multithreading is an example, each thread must create its own connection when accessing the database, not visible to other threads, causing the database operation to be confusing.

Therefore, we also want to create a simple and reliable database access model, in a thread, can be both safe and simple operation of the database.

Why not choose SQLAlchemy? SQLAlchemy is too large, and overly object-oriented design causes the API to be too complex.

So we decided to design ourselves a DB module that encapsulates the basic select, INSERT, update, and delete operations: Transwarp.db.

Designing DB Interfaces

The principle of designing the underlying module is to design an Easy-to-use API interface based on the upper caller, and then implement the module's internal code.

Assuming the Transwarp.db module has been written, we want to invoke it in this way:

First, initialize the database connection information through the Create_engine () function:

?

1 2	From Transwarp Import db Db.create_engine (user= ' root ', password= ' password ', database= ' test ', host= ' 127.0.0.1 ', port= 3306)

Then, you can manipulate SQL directly.

If you need to make a query, you can call the Select () method directly, and return to the list, where each element is the corresponding row represented by Dict:

?

1 2 3 4 5 6 7

Users = Db.select (' select * from user ') # users => # [# {' id ': 1, ' name ': ' Michael '}, # {' id ': 2, ' name ': ' Bob '}, # {"id": 3, "name": "Adam"} #]

If you want to perform an insert, update, or delete operation, perform the update () method to return the number of rows affected:

?

1	n = db.update (' INSERT into user (ID, name) VALUES (?,?) ', 4, ' Jack ')

The update () function is signed as:

?

1	Update (SQL, *args)

Unify with? As placeholders and pass in variable parameters to bind, fundamentally avoiding SQL injection attacks.

Each select () or update () invocation implicitly automatically opens and closes the database connection, so that the upper-level caller does not have to care about the underlying connection to the database at all.

But what if you want to execute multiple SQL statements in a database connection? We implement with a with statement:

?

1 2 3 4

With Db.connection (): Db.select (' ... ') db.update (' ... ') db.update (' ... ')

What if you want to execute more than one SQL statement in a database transaction? We use a with statement to implement:

?

1 2 3 4

With Db.transaction (): Db.select (' ... ') db.update (' ... ') db.update (' ... ')

Implementing the DB Module

Because the module is a global object and the module variable is a globally unique variable, there are two important module variables:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

# db.py # Database Engine object: Class _engine (object): Def __init__ (self, connect): Self._connect = connect def connect (self): return Self._connect () engine = None # Context object Holding database connection: Class _dbctx (threading.local): def __init__ (self): Self.connection = N One self.transactions = 0 def is_init (self): Return isn't self.connection is None def init (self): Self.connection = _las Yconnection () self.transactions = 0 def cleanup (self): Self.connection.cleanup () self.connection = None def cursor (sel f): Return Self.connection.cursor () _db_ctx = _dbctx ()

Because _db_ctx is a Threadlocal object, it holds a database connection that is different for each thread to see. No one thread can access a database connection held by another thread.

With these two global variables, we continue to implement the context of the database connection in order to automatically acquire and release the connection:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14-15 16

Class _connectionctx (object): Def __enter__ (self): global _db_ctx Self.should_cleanup = False if not _db_ctx.is_init (): _d B_ctx.init () Self.should_cleanup = True return self def __exit__ (self, exctype, Excvalue, Traceback): Global _db_ctx If Self.should_cleanup: _db_ctx.cleanup () def connection (): Return _connectionctx ()

objects that define __ENTER__ () and __exit__ () can be used with statements to ensure that the __exit__ () method can be invoked in any case.

The scope of the _CONNECTIONCTX to a function call, you can write this:

?

1 2	With connection (): Do_some_db_operation ()

But a simpler formulation is to write a @decorator:

?

1 2 3

@with_connection def do_some_db_operation (): Pass

This makes it easier to implement the Select () and update () methods:

?

1 2 3 4 5 6 7

@with_connection def select (SQL, *args): Pass @with_connection def update (SQL, *args): Pass

Note that the connection object is stored in the Threadlocal object of _dbctx, so there is no problem with nesting with connection (). _dbctx always detects if a connection is currently present, if it exists, use it directly, and if not, open a new connection.

Similar to transaction, with transaction () defines a database transaction:

?

1 2 3 4

With Db.transaction (): Db.select (' ... ') db.update (' ... ') db.update (' ... ')

The transaction for a function scope also has a simplified @decorator:

?

1 2 3

@with_transaction def do_in_transaction (): Pass

Transactions can also be nested, and inner transactions are automatically merged into outer transactions, which are sufficient to meet the requirements of 99%.

Transaction nesting is a bit more complex than connection nesting because transaction nesting needs to be counted, with each layer nested at + 1, leaving a layer of nesting-1, and then submitting the transaction at 0 o'clock:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

Class _transactionctx (object): Def __enter__ (self): global _db_ctx Self.should_close_conn = False if not _db_ctx.is_init ( ): _db_ctx.init () Self.should_close_conn = True _db_ctx.transactions = _db_ctx.transactions + 1 return self def __exit__ (Self, Exctype, Excvalue, Traceback): global _db_ctx _db_ctx.transactions = _db_ctx.transactions-1 try:if _db_ctx.trans   Actions==0:if Exctype is None:self.commit () else:self.rollback () finally:if self.should_close_conn: _db_ctx.cleanup () def commit (self): Global _db_ctx Try: _db_ctx.connection.commit () except: _db_ctx.connection.rollback () Raise Def Rol Lback (self): global _db_ctx _db_ctx.connection.rollback ()

Finally, the Select () and update () methods are implemented, and the DB module is completed.