Tutorials for writing database modules in Python

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 to access the database. Accessing the database requires creating a database connection, a cursor object, then executing the SQL statement, finally handling the exception, and cleaning up the resource. The code that accesses the database, if scattered among the functions, is bound to be impossible to maintain and not conducive to code reuse.

In addition, in a Web app, multiple users are simultaneously accessed, and the system processes each user's request in a multi-process or multithreaded mode. Assuming that in the case of multithreading, each thread must create its own connection when accessing the database, and it will not be visible to other threads, otherwise it would cause confusion in database operations.

So, we're going to create a simple and reliable database access model that allows you to operate the database safely and simply in one thread.

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

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

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

Assuming that 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:

From Transwarp import dbdb.create_engine (user= ' root ', password= ' password ', database= ' test ', host= ' 127.0.0.1 ', port= 3306)

Then, you can manipulate the SQL directly.

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

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

If you are performing an INSERT, update, or delete operation, execute the update () method, returning the number of rows affected:

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

The update () function signature is:

Update (SQL, *args)

Unified use? As a placeholder and pass in a mutable parameter to bind, fundamentally avoiding SQL injection attacks.

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

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

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

What if you want to execute multiple SQL statements in a database transaction? We also implement with a with statement:

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

Implementing the DB Module

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

# db.py# Database Engine object: Class _engine (object):  def __init__ (self, connect):    self._connect = connect  def connect ( Self):    return self._connect () engine = none# holds the context object for the database connection: Class _dbctx (threading.local):  def __init__ (self):    self.connection = None    self.transactions = 0  def is_init (self):    return isn't self.connection is None  def init (self):    self.connection = _lasyconnection ()    self.transactions = 0  def cleanup (self):    self.connection.cleanup ()    self.connection = None  def cursor (self):    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 that it sees. No one thread can access a database connection that is held by another thread.

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

Class _connectionctx (object):  def __enter__ (self):    global _db_ctx    self.should_cleanup = False    if not _db_ctx.is_init ():      _db_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 ()

An object that defines __enter__ () and __exit__ () can be used with the WITH statement to ensure that the __exit__ () method can be called in any case.

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

With connection ():  do_some_db_operation ()

But a simpler notation is to write a @decorator:

@with_connectiondef do_some_db_operation ():  Pass

This makes it easier for us to implement the Select (), Update () method:

@with_connectiondef Select (SQL, *args):  pass@with_connectiondef Update (SQL, *args):  Pass

Note that the connection object is stored in the _dbctx threadlocal object, so nesting with connection () is not a problem. _dbctx always detects whether a connection is currently present, if it exists, is used directly, and if it does not, a new connection is opened.

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

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

Function-scoped transactions also have a simplified @decorator:

@with_transactiondef do_in_transaction ():  Pass

Transactions can also be nested, and the inner transaction is automatically merged into the outer transaction, a transaction model sufficient to meet the 99% requirement.

Transaction nesting is a bit more complicated than connection nesting, because transaction nesting needs to be counted, each level of nesting is 1, leaving a layer nested on-1, and the last to 0 o'clock commits the transaction:

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.transactions==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 rollback (self):    global _db_ctx    _db_ Ctx.connection.rollback ()

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

