Python implements a simple bank transfer operation method

Preface

In the process of developing an application system, four components are generally required from top to bottom: client-business logic layer-data access layer- Database, in which the data access layer is a bottom-layer and core technology. And in actual development, the operation of the database, that is, the data access layer, is nested in other languages, which is the core of programming. This article is oriented to the Python language, that is, operating the database through Python to implement simple bank transfer operations.

Tools

Python provides python DB API to unify the operation of the database and standardize the interface for accessing the database. Before the python DB API, the interface program was very confusing. Different databases require different operating interfaces, so this interface provides great convenience. During the specific operation, we need to operate the database and other logical python codes, the database connection object connection to establish the connection, and the database interaction object cursor to "transport" data. A robust system is indispensable for the database exception class Exceptions. The entire database access process is as shown below:

Next, we will introduce the two main objects:

connection: database connection object, establish python client and database network connection.
Creation method: MySQLdb.connect(), including main member methods:
cursor(): Use the connection to create and return the cursor
commit(): Submit the current transaction
rollback(): Return Roll the current transaction
close() closes the connection
cursor: cursor object, used to execute queries and obtain results. The main methods supported by the cursor object are as follows:
execute(): execute SQL statements and retrieve the results from the database Get to the client
fetchone(): Get the next row of the result set
fetchmany(size): Get the next size row of the result set
fetchall(): Get all the remaining rows in the result set
rowcount: The number of rows of data returned by the most recent execute
close(): Close the cursor object
In the above method, a key term is mentioned: transaction. What is a transaction? It is a program execution unit that accesses and updates data. It is a collection of many operations and has four characteristics:

Atomicity: All operations included in the thing are either done or none of them are done.
Consistency Persistence: A transaction must change the database from a consistent state to another consistency state
Isolation type: The execution of a transaction is not interfered with by other transactions
Persistence: Once a transaction is committed, its changes to the database are durable The above characteristics of the
transaction are the key for us to complete the bank transfer operation.

Specific implementation

How do we use transactions in development?

Turn off automatic commit()
Normal end of transaction: conn.commit(),
Abnormal end of transaction: conn.rollback()
In the bank transfer system, the following requirements need to be considered: For example, if A transfers money to B, when M money is reduced from A's account, M money must be added to B's account. It cannot be that A has reduced money but B has not added it, nor that B has added money and A has not reduced it. Of course, the account must be valid. , the amount of M's money must be greater than the amount in A's account. Therefore, in the specific design, the reduction of money in account A and the increase of money in account B need to be treated as one transaction, either succeeding at the same time or failing together. According to this requirement, write the code. See github for the detailed code. The code copy and database are as follows. There are two accounts with money 110 and 10 respectively. When running the code, enter 1, 2,100 (source_acctid, target_acctid, transfer_money) in the parameter column.

The logic of the entire code is as follows: first connect to the database, then execute the logic, and then disconnect from the database. The executed logic includes checking whether the accounts of both parties to the transfer are valid and whether the transfer amount is valid. If there is more than the transferor's account balance, the account amounts given to both parties to the transfer will change. If the transaction ends normally, commit the modification to the database, otherwise roll it back.

#coding:utf-8
import sys
import MySQLdb
class TransferMoney():
def __init__(self, conn):
self.conn = conn
def transfer(self, src, target, money):
try:
self.check_acct_available(src)
self.check_acct_available(target)
self.has_enough_money(src, money)
self.reduce_money(src, money)
self.add_money(target, money)
self.conn.commit()
except Exception as e:
print e
self.conn.rollback()
def reduce_money(self, src, money):
cursor = self.conn.cursor()
try:
sql = "update account set money = money - %s where acctid = %s" %(money, src)
cursor.execute(sql)
print "reduce_money: " + sql
#rs = cursor.fetchall()
if cursor.rowcount != 1:
raise Exception("the account reduce money fail")
finally:
cursor.close()
def add_money(self, target, money):
cursor = self.conn.cursor()
try:
sql = "update account set money = money + %s where acctid = %s" %(money, target)
cursor.execute(sql)
print "add_money: " + sql
#rs = cursor.fetchall()
if cursor.rowcount != 1:
raise Exception("the account add money fail")
finally:
cursor.close()
def check_acct_available(self, accit):
cursor = self.conn.cursor()
try:
sql = "select * from account where acctid = %s" %accit
cursor.execute(sql)
print "check_acct_available: " + sql
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("the account %s is not exist" %accit)
finally:
cursor.close()
def has_enough_money(self, src, money):
cursor = self.conn.cursor()
try:
sql = "select * from account where acctid = %s and money >= %s " %(src, money)
cursor.execute(sql)
print "has_enough_money: " + sql
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("the account does not have enough money")
finally:
cursor.close()
if __name__ == "__main__":
source_acctid = sys.argv[1]
target_acctid = sys.argv[2]
money = sys.argv[3]
conn = MySQLdb.connect(
host = "127.0.0.1", user = '******', passwd = '******', port = 3306, db = '******'
)
tr_money = TransferMoney(conn)
try:
tr_money.transfer(source_acctid, target_acctid, money)
except Exception as e:
print e
finally:
conn.close()

Summary

A simple bank transfer system can be implemented by operating the database, so in the system When developing, we should try our best to make the entire system not just a splicing of multiple components, but to achieve 1+1>2.

For more related articles on implementing a simple bank transfer operation method in Python, please pay attention to the PHP Chinese website!