Python-based implementation of a simple bank transfer operation

Preface

In the development of an application system, from top to bottom generally need four components: client-business Logic Layer-data access layer-database, where the data access layer is a bottom-level, core technology. And in the actual development, the database operation means that the data access layer is nested in other languages, it is the core of programming. This article is intended for the Python language, which is a simple bank transfer operation through the Python operation database.

Tools

Python provides a Python DB API to unify the operation of the database, so that access to the database interface normalization, before the Python DB API, the interface program is very chaotic, different databases require different operating interfaces, so this interface provides great convenience. In the specific operation, we need to manipulate the database and other logical Python code, database connection object connection to establish a connection, database interaction object cursor to "Transport" data, a robust system is essential is the database exception class exceptions. The whole process of accessing the database is as follows:

Next, we introduce the next two main objects:

Connection: A database Connection object that establishes a Python client's network connection to the database.
Create method: MySQLdb.connect (), including the main member method:
Cursor (): Use this connection to create and return a cursor
Commit (): Commit the current transaction
Rollback (): Rollback of the current transaction
Close () Closes the connection
The cursor object, which is used to execute queries and get results, is the main method supported by the cursor object as follows:
Execute (): Executes the SQL statement, fetching the results from the database to the client
Fetchone (): Gets the next row of the result set
Fetchmany (size): Gets the next size row of the result set
Fetchall (): Get all remaining rows in the result set
ROWCOUNT: Number of rows in the last execute return data
Close (): Close the Cursor object
A key noun is mentioned in the above method: transaction, what is a transaction? He is a program execution unit that accesses and updates data, a collection of many operations, with four features:

Atomicity: The operations included in a thing are either done or not.
Consistency: A transaction must change the database from a consistent state to another consistent state
Isolated: Execution of one transaction is not disturbed by other transactions
Persistence: Once a transaction is committed, its change to the database is persistent
The above characteristics of the transaction is the key to our bank transfer operation.

Specific implementation

How do we use transactions in development?

Turn off automatic commit ()
Normal End transaction: Conn.commit (),
Unexpected end transaction: Conn.rollback ()
In the bank transfer system, you need to consider the following requirements: For example, A to B transfer, when a account is reduced m money, must be in the B account more M money, not a minus B no plus, and B plus a has not been reduced, of course, the account must be valid, M money amount must be greater than the amount of a account. So at the time of design, we need to reduce the money of a account and the money of B account as a transaction, either succeed at the same time, or fail together. According to this requirement, write code, detailed code see GitHub, code Copy and database as follows, there are two accounts, each with Money 110 and 10, when running the code in the parameter bar input 1,2,100 (Source_acctid, Target_acctid, Tranfer_money).

The logic of the whole code is as follows: First connect the database, then execute the logic, then disconnect the database, the logic of execution includes checking whether the account of the two parties is valid, whether the transfer amount is more than the account balance of the sender, and the account amount of the transfer parties is changed respectively. If the transaction ends normally, commit to modify the database, otherwise roll back.

#coding: Utf-8import sysimport mysqldbclass TransferMoney ():d EF __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 = M Oney-%s Where Acctid =%s "% (money, SRC) cursor.execute (SQL) print" Reduce_money: "+ Sql#rs = Cursor.fetchall () if cursor. RowCount! = 1:raise Exception ("The account to 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.exec Ute (SQL) print "Add_money:" + Sql#rs = Cursor.fetchall () if cursor.rowcount! = 1:raise Exception ("The account add-money FAI L ") 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 was 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.fetchal L () If Len (rs)! = 1:raise Exception ("The account does not has 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 ", use r = ' ****** ', passwd = ' ****** ', port = 3306, db = ' ****** ') Tr_money = TransferMoney (conn) Try:tr_money.transfer (SOURCE_ACC Tid, Target_acctid, money) except Exception as E:print efinally:conn.close ()

Summarize

Through the operation of the database can be implemented a simple bank transfer system, so in the system development, we should do our best, so that the entire system is not just a number of components splicing, should be implemented 1+1>2.

