Python operation Simple example of MySQL analog bank transfer operation

This article mainly describes the Python operation MySQL simulation simple Bank transfer operation, the need for friends can refer to the following

First, the basic knowledge

1, the installation of Mysql-python

Download and then pip install package

2. Python API specification for writing common database programs

(1), database Connection object connection, establish a Python client and database network connection, the creation method is mysqldb.connect (parameter)

There are six parameters: host (MySQL server address, General Local is 127.0.0.1)

Port (MySQL server port number)
User (username)
passwd (password)
DB (database name)
CharSet (Connection code)

Connection method: Cursor () uses the connection and returns a cursor

Commit () commits the current transaction
Rollback () Rollback of the current transaction
Close () Closes the connection

(2), database cursor object cursor, for executing queries and obtaining results

Method: Execute (Op[,args]) executes a database query and command

Fetchone () Gets the next row of the result set
Fetchmany (size) Gets the next few rows of the result set
Fetchall () gets all the rows left in the result set
RowCount last execute returns the number of rows of data or affects the number of rows
Close () Closes the cursor object

Connection and cursor:connection correspond to the path between Python and MySQL, while the cursor is the equivalent of a transport vehicle on the road to deliver commands and results.

3. Simple command:

Select query data: sql= "select * FROM table name item"
Insert inserts data: sql= "INSERT into table name inserted project"
Update change data: sql= "updata table name set changed item"
Delete deleted data: sql= "Delete item deleted from table name"
Where is also a critical existence of SQL commands, usually the WHERE Header = column name to locate that column

4. Business

A program execution unit that accesses and updates a database, and the commands that are executed can be called transactions

Atomicity, consistency, isolation, durability

Transaction execution:

Conn.commit () normal end transaction
The Conn.rollback () exception ends the transaction, and the transaction is rolled back, and the previous operation is restored if successive operations in the program execution unit fail in progress.

Simple operation process: Start → create connection→ get cursor→ program execution Unit → Close cursor→ close connection→ end

Second, analog bank transfer system code

#coding =utf-8 Import sys import mysqldb "' python operation MySQL database, Analog bank transfer ' class Trans_for_money (object): #初始化 class Def __in It__ (self,conn): Self.conn = conn # # # 1, check if the account entered is there # # def check_acct_available (Self,source_acctid): #使用与数据库的链接并    Returns the cursor Cursor=self.conn.cursor () Try: #数据库命令 sql= "select * from Tr_money where acctid=%s"%source_acctid #执行命令    Cursor.execute (SQL) #为方便观察执行过程 print "check_acct_available:" + SQL #讲结果集放入变量result中, if result is not equal to 1, then no account, output exception Result=cursor.fetchall () If Len (result)!=1:raise Exception ("account%s does not exist"%source_acctid) finally: #若过程出现问题, you still need to close the Closed Cursor Object Cursor.close () # # # # 2, check whether the balance is sufficient, the method and the previous function, just add a money parameter # # DEF Has_enough_money (Self,source_acctid,money) : Cursor=self.conn.cursor () try:sql= "SELECT * from Tr_money where acctid=%s and money>%s"% (Source_acctid,money ) cursor.execute (SQL) print "Has_enough_money:" + SQL Result=cursor.fetchall () if Len (result)!=1:raise Ex Ception ("Account%s insufficient balance"%SOURCE_acctid) # # # # # # # # # # # # 3, Finally:cursor.close # # def Reduce_money (Self,source_acctid,money): cursor=self.conn.cu    Rsor () Try: #数据库命令, minus the amount of the corresponding decrement person sql= "update Tr_money set money=money-%s where acctid=%s"% (Money,source_acctid) Cursor.execute (SQL) print "Reduce_money:" + SQL #操作的execute () the number of rows of data is not equal to 1 if the loss of money fails if cursor.rowcount!=1:raise excep tion ("account%s failed"%source_acctid) Finally:cursor.close () # # # # # # # # # # # 4, collection operations, same as the method of reducing money # def Add_money (Self,target_acctid,mo Ney): Cursor=self.conn.cursor () try:sql= "Update Tr_money set money=money+%s where Acctid =%s"% (money,target_accti d) cursor.execute (SQL) print "Add_money:" + SQL if Cursor.rowcount!=1:raise Exception ("account%s failed to receive payment"%TARGET_ACC   TID) # # # # 5, pass in the parameters, finally:cursor.close the function above, perform the operation # # DEF trans_for (Self,source_acctid,target_acctid,money): Try:self.check_acct_available (Source_acctid) self.check_acct_available (Target_acctid) Self.has_enough_money (sou Rce_acctid,money) selF.reduce_money (Source_acctid,money) Self.add_money (Target_acctid,money) #提交当前事务 self.conn.commit () except EXCEP tion as E: #若出错, rollback current transaction self.conn.rollback () Raise E if __name__== "__main__": # Source_acctid=sys.argv[1] # Targ         ET_ACCTID=SYS.ARGV[2] # Money=sys.argv[3] #建立与数据库的链接 conn = MySQLdb.connect (host= ' 127.0.0.1 ', port=3306, User= ' root ', passwd= ' 12345678 ', db= ' TT ', charset= ' UTF8 ') #手动输入减款人, payee, transfer number SOURCE_AC Ctid=raw_input ("Please enter the beneficiary:") target_acctid=raw_input ("Please enter payee:") money=raw_input ("Please enter the number of transfers:") #将参数传入类中 Tr_money=trans_fo  R_money (conn) try:tr_money.trans_for (Source_acctid,target_acctid,money) except Exception as E:print "problems occurred:" +str (E) Finally:conn.close () #关闭链接

Third, problem solving

1, sys.argv[]

Because the IDE used in the instructional video is MyEclipse, finally run. Configuration input parameters, and I use a pycharm, indicating that the stupid can not find or actually it did not!

So choose to use Raw_input () to enter parameters during execution

Actually have to understand sys.argv[], but still understand not very clear.

2, Mysql_exceptions. Integrityerror: (1062, "Duplicate entry ' 7 ' for key ' PRIMARY '")

This error indicates that the data you want to insert already exists, it is best to observe whether the data of the database is inconsistent with your own program operation.

3. Error when MYSQL builds table or input value: 1170-blob/text column ' name ' used in key specification without a key length

The error message is a BLOB or text field that uses a key that does not specify a key value length

Workaround: Set the other primary key or change the data form to varchar

Specific explanation website: http://myhblog1989.blog.163.com/blog/static/183225376201110875818884/

4. TypeError: ' Post ' is a invalid keyword argument for this function

Error reason: TypeError: "POST" is an invalid parameter for this function

The problem is very wrong, a moment of brain water to the "port" = 3306 written as "post" = ' 3306 '

5, 1054, "Unknown column ' Acctid ' in ' WHERE clause '

Error reason: "Acctid" column not found in the WHERE clause

Oh, the last mistake in the brain into the water did not come out, the table was written wrong ...

6, in addition, there is another error is the manual input of the reduction, the payee is set to letter or kanji when not found

may be my code or database to set up a table when the problem, indicating in character conversion and database this aspect or small white one, continue to struggle!

7. Start of MySQL Database

Computer → Right-click → manage → services and applications → services → find mysql→ right-click Start

Iv. specific implementation of the display

1, the initial state of the database Tr_money table

2. Code execution, input of the beneficiary, payee, transfer amount

3, execution, the results appear in the code of the operation process of the deliberately print display

4. Database Tr_money status after the execution of the table

Summarize