Implement a simple bank transfer operation based on Python, and implement bank transfer through python

Implement a simple bank transfer operation based on Python, and implement bank transfer through python

Preface

During the development of an application system, four components are required from top to bottom: client-business logic layer-data access layer-database, the data access layer is an underlying and core technology. In actual development, database operations mean that the data access layer is nested in other languages and is the core of programming. This article is oriented to the python language, that is, through the python operation database to achieve simple bank transfer operations.

Tools

Python provides python DB APIs to operate databases in a unified manner, and standardizes database access interfaces. Before the python DB APIs are available, the interface programs are very messy. Different databases require different operation interfaces, therefore, this interface provides great convenience. During the specific operation, we need to operate the database and other logic python code, database connection object connection to establish a connection, and database interaction object cursor to "ship" data, A robust system is essential to database exception categories. The whole database access process is as follows:

Next we will introduce the following two main objects:

Connection: database connection object, which establishes a network connection between the python client and the database.
Creation Method: MySQLdb. connect (), including the main member methods:
Cursor (): Creates a connection and returns a cursor.
Commit (): Submit the current transaction
Rollback (): roll back the current transaction
Close () close the connection
Cursor: A cursor object used to query and obtain results. The cursor object supports the following methods:
Execute (): execute an SQL statement to obtain the result from the database to the client.
Fetchone (): gets the next row of the result set.
Fetchmany (size): obtains the next size row of the result set.
Fetchall (): gets all the remaining rows in the result set.
Rowcount: the number of rows returned by the last execute operation.
Close (): close the cursor object
The above method mentioned a key term: transaction. What is a transaction? It is a program execution unit for accessing and updating data. A set of many operations has four features:

Atomicity: all operations in a thing are either done or not done.
Consistency: the transaction must change the database from a consistent state to another consistent state.
Isolation type: the execution of a transaction is not disturbed by other transactions.
Durability: Once a transaction is committed, its changes to the database are persistent.
The above features of transactions are the key to completing bank transfers.

Implementation

How can we use transactions during development?

Disable Automatic commit ()
Terminate the transaction normally: conn. commit (),
End transaction exception: conn. rollback ()
In the bank transfer system, you need to consider the following requirements: for example, if A transfers money from account A to account B, if M money is reduced in account A, M money must be added to account B, you cannot subtract A from B, or add B to A. The account must be valid. The M money must be greater than the account. Therefore, in the specific design, you need to reduce the money of account A and increase the money of Account B as A transaction, either at the same time, or both fail. Write the code according to this requirement. For details about the code, see github, code replication, and database. There are two accounts with money of 110 and 10 respectively. When running the code, enter 1, 2 in the parameter bar, 100 (source_acctid, target_acctid, tranfer_money ).

The logic of the entire code is as follows: First connect to the database, then execute the logic, and then disconnect the database. The execution logic includes checking whether the accounts of both parties are valid, whether the transfer amount is greater than the account balance of the transferor, and the account amount of both parties has changed. If the transaction ends normally, commit and modify the database; otherwise, roll back.

#coding:utf-8import sysimport MySQLdbclass TransferMoney():def __init__(self, conn):self.conn = conndef 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 eself.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" %accitcursor.execute(sql)print "check_acct_available: " + sqlrs = 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: " + sqlrs = 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 efinally:conn.close()

Summary

A simple bank transfer system can be implemented through database operations. Therefore, during system development, we should do our best to make the whole system more than just splicing multiple components, 1 + 1> 2.

Articles you may be interested in:

• Practical bank transfer stored procedures and sequential number generation stored procedures
• Php + mysqli transaction control for bank transfer instances