Python interface test operation for Mysql/unittest framework/requests

Unit tests support test Automation , shared installers, and shutdown Code testing ,

PolymerizationYesCollection，TestAndReportFrameworkFromTest independence unit test module provide can very easy to Support these quality a test

The test framework recommends that you go to the official view for detailed instructions and examples of demonstrations. UnitTest the relationships of each module are:

#!/usr/bin/env python#coding:utf-8import  unittestclass testdiv (unittest. TestCase):    def setUp (self):        pass    def tearDown (self):        pass    def test_001 (        self): Self.assertequal (Div (1),    def test_002 (self):        self.assertraises (zerodivisionerror,div,1,0) if __name__ = = ' __main__ ':    unittest.main (verbosity=2)

The most commonly used are get,put,delete,post, which can also be achieved through the request library.

, see the following code:

In the command-line environment of Python

See if you can import mysqldb, if you can import and no error prompts, indicating that the installation has been successful, see:

has been very successful to install the Python operation MySQL database, here, we detailed introduction to the python on MySQL additions, deletions, modifications

And the basic operation of the query, the database name used here is "day2017", our operations on the database, the first is to create a database, and then in the database

Create the table, where the table name is: userInfo, see Creating table field information:

OK, after the database is created and the tables in the database are created, the first step in manipulating the database is to connect to the database, and then the

Create a cursor, followed by a variety of operations on the database, here we first to manipulate the insert data, see the implementation of the Code:

#!/usr/bin/env python #coding: Utf-8import mysqldbdef insert_one ():    ' Insert a data '    try:        conn= MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor ()        sql = ' INSERT into UserInfo VALUES (%s,%s,%s,%s) '        params = (1, ' admin ', ' admin ' ', ' [email protected] ', '        cur.execute (sql, params)        conn.commit ()    finally:        cur.close ()        Conn.close () If __name__== ' __main__ ':    insert_one ()

Looking at the database, you can see that the data has been inserted into the database, see the results of the query:

In the above case, just insert a single piece of data, in fact, at some point, insert multiple pieces of data, that is, BULK INSERT, the code of the Bulk INSERT implementation is:

Def insert_many ():    ' BULK insert Data '    try:        conn=mysqldb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' Server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor ()        sql = ' INSERT into UserInfo VALUES (%s,%s,%s,%s) '        params = [            (2, ' Wuya ', ' admin ', ' [email protected] '),            (3, ' Weke ', ' admin ', ' [ Email (protected] ')        ]        cur.executemany (SQL, params)        conn.commit ()    finally:        cur.close ()        conn.close () if __name__== ' __main__ ':    insert_many ()

Next, we look at the database query, the data query is divided into two kinds, one is the result of the query is a statement, using the Fetchone () method, and the other is the query data

The result is multiple, using the method is Fetchmany (), we look at the use of these two methods, we first look at a single data query, see the implementation of the Code:

Import  mysqldbdef select_one ():    ' single data query '    try:        conn=mysqldb.connect (host= ' 127.0.0.1 ', user= ' Root ', passwd= ' server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor ( cursorclass=mysqldb.cursors.dictcursor)        sql = ' SELECT * from UserInfo where id=%s '        params = (1,)        data= Cur.execute (SQL, params)        print cur.fetchone ()        conn.commit ()    finally:        cur.close ()        Conn.close () If __name__== ' __main__ ':    select_one ()

For multiple data queries, see Implementing the Code:

Import Mysqldbdef Select_many ():    ' multiple data query '    try:        conn=mysqldb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor (cursorclass =mysqldb.cursors.dictcursor)        sql = ' select * ' from UserInfo '        ret=cur.execute (SQL)        Data=cur.fetchall () For        item in data:            Print Item    finally:        cur.close ()        conn.close () if __name__== ' __main__ ':    Select_many ()

Let's look at the test of the UPDATE statement, see Implementing the Code:

Import  mysqldbdef update_test ():    ' UPDATE statement test '    try:        conn=mysqldb.connect (host= ' 127.0.0.1 ', user= ' Root ', passwd= ' server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor ( cursorclass=mysqldb.cursors.dictcursor)        sql = ' Update userInfo set username=%s where id=%s '        params= (' System ', 1,)        Ret=cur.execute (sql,params)        conn.commit ()    finally:        cur.close ()        conn.close () if __ name__== ' __main__ ':    update_test ()

The final step, that is, delete the data, directly see the following implementation code:

Import  mysqldbdef delete_test ():    ' DELETE statement test '    try:        conn=mysqldb.connect (host= ' 127.0.0.1 ', user= ' Root ', passwd= ' server ', db= ' day2017 ')    except:        print U ' connection to MySQL database failed '    else:        cur = conn.cursor ( cursorclass=mysqldb.cursors.dictcursor)        sql = ' Delete from userInfo where id=%s '        params= (3,)        ret= Cur.execute (sql,params)        conn.commit ()    finally:        cur.close ()        conn.close () if __name__== ' __main __ ':    delete_test ()

As a matter of fact, there is a lot of code that can be done on the database, such as the way to connect to the database, and we can manipulate the database

Way write in a class, in the business call directly call our database method to operate, see below Operation MySQL database method, see source code:

#!/usr/bin/env python #coding: Utf-8import mysqldbclass Mysqlhelper (object): Def __init__ (self): Pass def get  _one (self,sql,params): conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ') cur = Conn.cursor (cursorclass=mysqldb.cursors.dictcursor) Retcount = Cur.execute (sql,params) data = Cur.fetchon E () Cur.close () Conn.close () return Data def get_many (self,sql,params): conn = Mysqldb.conn ECT (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ') cur = conn.cursor (cursorclass=        MySQLdb.cursors.DictCursor) Retcount = Cur.execute (sql,params) data = Cur.fetchall () cur.close () Conn.close () return Data def insert_one (self,sql,params): conn = MySQLdb.connect (host= ' 127.0.0.1 ', use        R= ' root ', passwd= ' server ', db= ' day2017 ') cur = conn.cursor () cur.execute (SQL, params) conn.commit ()    Cur.close () return u ' Insert database succeeded 'def insert_many (self,sql,params): conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day201 7 ') cur = conn.cursor () cur.executemany (SQL, params) conn.commit () cur.close () return u ' Bulk Insert database successfully ' Def update_one (self,sql,params): conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' serve        R ', db= ' day2017 ') cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor) ret = cur.execute (sql, params)        Conn.commit () Cur.close () conn.close () return u ' Update database Success ' Def delete_one (self,sql,params): conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ') cur = conn.cursor (cursorc lass=mysqldb.cursors.dictcursor) ret = cur.execute (sql, params) conn.commit () Cur.close () conn . Close () return U ' Delete database succeeded '

The connection database part is refactored into a config.py file, so that the way we connect to the database needs to be maintained in the config.py file, not

Need to see in the above code in each of the changes, this is really bad, see the reconstructed config.py file source code:

#!/usr/bin/env python #coding: utf-8conn_dict=dict (host= ' 127.0.0.1 ', user= ' root ', passwd= ' server ', db= ' day2017 ')

See the database method of MySQL operation after refactoring, see source code:

#!/usr/bin/env python #coding: Utf-8import mysqldbimport configclass Mysqlhelper (object): Def __init__ (self): s Elf.conn=config.conn_dict def get_one (self,sql,params): conn = MySQLdb.connect (**self.conn) cur = conn.cu        Rsor (cursorclass=mysqldb.cursors.dictcursor) Retcount = Cur.execute (sql,params) data = Cur.fetchone () Cur.close () Conn.close () return Data def get_many (self,sql,params): conn = MySQLdb.connect (**self . conn) cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor) Retcount = Cur.execute (sql,params) d        ATA = Cur.fetchall () cur.close () Conn.close () return Data def insert_one (self,sql,params):        conn = MySQLdb.connect (**self.conn) cur = conn.cursor () cur.execute (SQL, params) conn.commit ()        Cur.close () return u ' Insert database Success ' Def insert_many (self,sql,params): conn = MySQLdb.connect (**self.conn)  cur = conn.cursor ()      Cur.executemany (SQL, params) conn.commit () cur.close () return u ' BULK Insert database Success ' Def update_one (SE LF,SQL,PARAMS): conn = MySQLdb.connect (**self.conn) cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor ) ret = Cur.execute (sql, params) conn.commit () Cur.close () conn.close () return u ' update database into Work ' Def delete_one (self,sql,params): conn = MySQLdb.connect (**self.conn) cur = conn.cursor (Cursorclass=mys         QLdb.cursors.DictCursor) ret = cur.execute (sql, params) conn.commit () Cur.close () Conn.close () return u ' Delete database succeeded '

The way to write a database is to operate on the business, not only to write these little practical significance, such as we implement the input user name and password, in

Verify in the database, if the user name and password are admin, then through, if one is not admin, prompt the user, please prompt the user name

or password error, the following to achieve such a process, see the implementation of the source code:

#!/usr/bin/env python #coding: Utf-8import mysqldbimport configclass Mysqlhelper (object): Def __init__ (self): s Elf.conn=config.conn_dict def get_one (self,sql,params): conn = MySQLdb.connect (**self.conn) cur = conn.cu        Rsor (cursorclass=mysqldb.cursors.dictcursor) Retcount = Cur.execute (sql,params) data = Cur.fetchone () Cur.close () Conn.close () return Data def get_many (self,sql,params): conn = MySQLdb.connect (**self . conn) cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor) Retcount = Cur.execute (sql,params) d        ATA = Cur.fetchall () cur.close () Conn.close () return Data def insert_one (self,sql,params):        conn = MySQLdb.connect (**self.conn) cur = conn.cursor () cur.execute (SQL, params) conn.commit ()        Cur.close () return u ' Insert database Success ' Def insert_many (self,sql,params): conn = MySQLdb.connect (**self.conn)  cur = conn.cursor ()      Cur.executemany (SQL, params) conn.commit () cur.close () return u ' BULK Insert database Success ' Def update_one (SE LF,SQL,PARAMS): conn = MySQLdb.connect (**self.conn) cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor ) ret = Cur.execute (sql, params) conn.commit () Cur.close () conn.close () return u ' update database into Work ' Def delete_one (self,sql,params): conn = MySQLdb.connect (**self.conn) cur = conn.cursor (Cursorclass=mys         QLdb.cursors.DictCursor) ret = cur.execute (sql, params) conn.commit () Cur.close () Conn.close () return u ' Delete database succeeded ' class Checkuserinfo (object): Def __init__ (self): self.__helper=mysqlhelper () def che Ckvalid (Self,username,password): sql= ' select * from UserInfo where username=%s and password=%s ' params= (use Rname,password) return Self.__helper.get_one (sql,params) def info (): username=raw_input (U ' Please enter your username: \ n ') passwo Rd=raw_input (U ' Please enter your password: \n ') Userinfo=checkuserinfo () result=userinfo.checkvalid (Username,password) if not result:print u ' username or password error Mistake, please contact the Administrator ' else:print u ' Congratulations, enter the correct! ' If __name__== ' __main__ ': info ()

Python interface Test/requests for mysql/unittest framework