Python operation MySQL Analog simple Bank transfer operation

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 corresponds to the path between Python and MySQL, while the cursor corresponds to 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-8import sysimport mysqldb "python operation MySQL database, Analog bank transfer ' class Trans_for_money (object): #初始化 class Def __init__ (self,conn): Self.conn = conn # # 1, check if the account entered is the presence of Def check_acct_available (Self,source_acctid): #  Use a link to the database and return the cursor cursor=self.conn.cursor () Try: #数据库命令 sql= "select * from Tr_money where acctid=%s "%source_acctid #执行命令 cursor.execute (SQL) #为方便观察执行过程 print" Check_a            cct_available: "+ SQL #讲结果集放入变量result中, if result is not equal to 1, then there is 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 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,m Oney): 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 Exception ("account%s insufficient balance"%source_acctid) finally:cursor.close () # # # # 3, cut-off Operation # # Def Reduce_money (Self,source_acctid,money): Cursor=self.conn.cursor () Try: #数据 Library command, minus the amount of the corresponding decrement person sql= "update Tr_money set money=money-%s where acctid=%s"% (Money,source_acctid) curs Or.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 Exception ("account%s reduction failed"%source_acctid) Finally:cursor.close () # 4, collection operation, and reduction Same Method # # Def Add_money (Self,target_acctid,money): Cursor=self.conn.cursor () try:sql= "update Tr_money set money=money+%s where Acctid =%s "% (money,target_acctid) cursor.execute (SQL) print" add_           Money: "+ SQL If Cursor.rowcount!=1:raise Exception ("account%s failed to receive payment"%target_acctid) Finally:cursor.close ( # # # # # 5, pass in the parameters separately, take the function above, perform the operation # # DEF trans_for (Self,source_acctid,target_acctid,money): TRY:SELF.C Heck_acct_available (Source_acctid) self.check_acct_available (Target_acctid) Self.has_enough_money (so            Urce_acctid,money) Self.reduce_money (Source_acctid,money) Self.add_money (Target_acctid,money) #提交当前事务 self.conn.commit () except Exception as E: #若出错, rollback the current transaction self.conn.rollb ACK () Raise EIF __name__== "__main__": # Source_acctid=sys.argv[1] # Target_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, number of transfers Source_acctid=raw_input ("Please enter the payee:") t        Arget_acctid=raw_input ("Please enter payee:") money=raw_input ("Please enter the number of transfers:") #将参数传入类中 Tr_money=trans_for_money (conn) Try: Tr_money.trans_for (Source_acctid,target_acctid,money) except Exception as E:print "problem:" +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

Python operation MySQL Analog simple Bank transfer operation