Tutorial on writing a database module in Python

This article describes how to compile a database module in Python. the code in this article is based on Python2.x. For more information, see all the data in a Web App, user information, published logs, and comments are stored in the database. In awesome-python-app, we select MySQL as the database.

There are many places in the Web App that need to access the database. To access the database, you must create database connection and cursor objects, execute SQL statements, handle exceptions, and clear resources. If these database access code is dispersed into various functions, it will inevitably be unable to be maintained, and it is not conducive to code reuse.

In addition, multiple users access a Web App at the same time, and the system processes the requests of each user in multi-process or multi-thread mode. Taking multithreading as an example, each thread must create a connection that only belongs to itself when accessing the database, which is invisible to other threads. otherwise, database operations will be disordered.

Therefore, we also need to create a simple and reliable database access model to operate databases securely and simply in one thread.

Why not choose SQLAlchemy? SQLAlchemy is too large, and over-ground object design leads to complicated APIs.

Therefore, we decided to design a database module that encapsulates basic SELECT, INSERT, UPDATE, and DELETE operations: transwarp. db.
Design db interface

The principle of designing the underlying module is to design simple and easy-to-use API interfaces based on the upper-layer callers, and then implement the internal code of the module.

Assuming that the transwarp. db module has been compiled, we want to call 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 directly operate SQL.

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

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

If you want to execute INSERT, UPDATE, or DELETE operations, execute the update () method and return the number of affected rows:

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 passed in variable parameters to bind, fundamentally avoiding SQL injection attacks.

Each select () or update () call implicitly automatically opens and closes the database connection. in this way, upper-layer callers do not have to worry about the underlying database connection.

However, what if I want to execute multiple SQL statements in a database connection? We use a with statement to implement:

with db.connection():  db.select('...')  db.update('...')  db.update('...')

What if I want to execute multiple SQL statements in a database transaction? We still use a with statement to implement:

with db.transaction():  db.select('...')  db.update('...')  db.update('...')

Implement 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:

# 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 the database connection: class _ DbCtx (threading. local): def _ init _ (self): self. connection = None self. transactions = 0 def is_init (self): return not 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, the database connections it holds are different for each thread. No thread can access the database connections held by other threads.

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

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()

Objects defining _ enter _ () and _ exit _ () can be used in the with statement to ensure that the _ exit _ () method can be called in any situation.

Write the _ ConnectionCtx scope to a function call:

with connection():  do_some_db_operation()

But the simpler method is to write @ decorator:

@with_connectiondef do_some_db_operation():  pass

In this way, it is easier to implement the select () and update () methods:

@with_connectiondef select(sql, *args):  pass@with_connectiondef update(sql, *args):  pass

Note that the Connection object is stored in the threadlocal object _ DbCtx. Therefore, nested use of with connection () is no problem. _ DbCtx always checks whether a Connection exists. If yes, use it directly. If no Connection exists, open a new Connection.

The same is true for transaction. with transaction () defines a database transaction:

with db.transaction():  db.select('...')  db.update('...')  db.update('...')

The transaction in the function scope also has a simplified @ decorator:

@with_transactiondef do_in_transaction():  pass

The transaction can also be nested, and the inner transaction is automatically merged into the outer transaction. this transaction model is sufficient to meet 99% of the requirements.

Transaction nesting is a little more complex than Connection nesting, because the transaction nesting requires counting, each layer of nesting is + 1, leaving the layer of nesting is-1, and finally committing the transaction at 0:

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 complete.