Python three different ways to work with MySQL databases

First of all, in this summer if there is no special situation, I intend to use Python to try to write an examination system, I hope to be in the next semester of the Python course to use, and try to use the relevant technology in the way to share the blog, have to communicate with the small partners can continue to pay attention to me!

Not much to say, this blog is a test system to use a technology Python three ways to implement MySQL operation.

This is preceded by the introduction of three knowledge points:

1: __name__== ' __main__ '

What does that mean?

The suffix of the 1.python file is. py

The 2..py file can be used either directly or as a module to be imported,

3. Import the module with import in Python

__name__ as a built-in property of the module, the simple point is that the. py file is called, if it is called by the module __name__ is equal to the module name (the following will continue to explain ), if the direct call to __name__ is equal to ' __main__ '

2: command-line arguments

What is a command-line argument? As the name implies, the arguments are passed on the command line, and the common way of passing arguments is that the command-line pass parameter is to write the parameters and the program on the same command line to run.

Take a look at the actual picture (I'm using a Linux environment, if DOS is available under Windows):

　　

Here Python3 is the format for executing the Python program, pyt3.py and pyt2.py are the names of the. Py program, and the subsequent 100 101 3 are parameters.

Having identified this, introduce a library, the SYS library, which can support some operations on the parameters passed by the command line (which will continue to be explained later ).

3:pymysql This library

Note that the MYSQLDB library is not supported in versions above Python3, but the methods are basically the same.

The main steps to use this library are:

1: Build Connection

2: Get Cursor (you can think of it as a cursor)

3: Execute SQL statement

4: An exception occurred in the transaction ?

N:con.commit

Y:con.rollback

5: Get data after executing SQL

Cursor.fetch

　　What is the matter here? Simply put, all the operations a transaction contains are atomic operations that are either executed or not executed, and what is the use of the transaction (as explained later)

Okay, back to the chase, then Python. What are the three ways to operate the MySQL database? Take bank Transfer For example, first look at the first

1: Normal Way

ImportPymysqlImportSyscon=pymysql. Connect (host='xxx.xxx.xx.xx', port=3306,db='pytest', user='Root', passwd=' xxx', charset='UTF8') Cursor=con.cursor ()defIs_ava (Acctid): Sel_sql='SELECT * from account where acctid=%s'%Acctid Cursor.execute (sel_sql) RS=Cursor.fetchone ()Try: Len (RS)except:        returnFalsereturnTruedefMon_ava (Acctid,mon): Selm_sql='Select *from account where acctid=%s and money>=%s'%(Acctid,mon) Cursor.execute (selm_sql) RS=Cursor.fetchone ()Try: Len (RS)except:        returnFalsereturntruesr_id=sys.argv[1]tr_id=sys.argv[2]dt_money=sys.argv[3]ifIs_ava (sr_id) andIs_ava (tr_id):ifMon_ava (Sr_id,dt_money): Rm_sql='Update account set money=money-%s where acctid=%s'%(dt_money,sr_id) Add_sql='Update account set money=money+%s where acctid=%s'%(dt_money,tr_id)Try: Cursor.execute (rm_sql) cursor.execute (add_sql) con.commit ()except: Con.rollback ()finally: Cursor.close () con.close ()

This is purely a process-oriented programming idea, note that the last try of the code, except here is a complete transaction flow, when the execution of remove and add an exception occurs rollback () rollback the role of rollback to the SQL statement before the execution of the state.

Why would you do that? Imagine this: A to B of the bank card transfer 100 yuan, that is A-100 b+100. and b before this card is written off, if not transacted operation will appear: a lost 100 dollars in the case

What we expect is this: A-100 b+100, if an exception occurs during the transfer process, the amount of a and B remains the same. This atomic operation, using transactions, ensures that the operation is secure.

2: Look at the second method of object-oriented approach :

#-*-coding:utf-8-*-ImportPymysqlImportSYSclassTransformoney (object):def __init__(Self,con): Self.con=Con self.cursor=self.con.cursor ()defIs_ava (Self,acctid): Sel_sql='SELECT * from account where acctid=%s'%Acctid Self.cursor.execute (sel_sql) RS=Self.cursor.fetchone ()Try: Len (RS)except:            RaiseExceptiondefMon_ava (Self,acctid,mon): Selm_sql='Select *from account where acctid=%s and money>=%s'%(Acctid,mon) Self.cursor.execute (selm_sql) RS=Self.cursor.fetchone ()Try: Len (RS)except:            RaiseExceptiondefRd_mon (Self,acctid,mon): Rm_sql='Update account set money=money-%s where acctid=%s'%(Mon,acctid) Self.cursor.execute (rm_sql)ifSelf.cursor.rowcount! = 1:            RaiseExceptiondefAdd_mon (Self,acctid,mon): Add_sql='Update account set money=money+%s where acctid=%s'%(Mon,acctid) Self.cursor.execute (add_sql)ifSelf.cursor.rowcount! = 1:            RaiseExceptiondeftransfor (Self,srid,trid,mon):Try: Self.is_ava (Srid) Self.is_ava (Trid) Self.mon_ava (Srid,mon) Self.rd_mon (s Rid,mon) Self.add_mon (Trid,mon) self.con.commit ()except: Self.con.rollback ()finally: Self.cursor.close () self.con.close ()if __name__=='__main__': sr_id=sys.argv[1] tr_id=sys.argv[2] Dt_money=sys.argv[3] Con=pymysql. Connect (host='xxx.xxx.xx.xx', port=3306,db='pytest', user='Root', passwd='xxxxx', charset='UTF8') Tr_obj=Transformoney (Con) tr_obj.transfor (Sr_id,tr_id,dt_money)

All methods are encapsulated in a class, the Transfor method is also a transaction operation, the front also said __name__ this built-in property, if the program is directly run __name__ is equal to __main, then if __name__== ' __main__ ' is set up, that is to say, the entrance to the program.

3: See below the third way of modularity　

#-*-coding:utf-8-*-"""Created on Tue June 6 11:45:42 2017@author:a"""ImportSYSImportPymysqlImporttemp2sr_id=sys.argv[1]tr_id=sys.argv[2]dt_money=sys.argv[3]con=pymysql. Connect (host='xxx.xxx.xx.xx', port=3306,db='pytest', user='Root', passwd='xxxxx', charset='UTF8') Tr_obj=Temp2. Transformoney (Con) tr_obj.transfor (Sr_id,tr_id,dt_money)

is not very short, yes, the second method of the above to save the program as temp2.py, in the program directly import TEMP2 can call temp2.py class method, notice at this time temp2.py __name__ is not equal to __main__ is equal to Temp.

Yes, the above connection to host and passwd I have to use XXX to express, because it is my personal server is not easy to open, in practice, we can change to their own on it.

Python three different ways to work with MySQL databases