This article will mainly explain the interface automation test Python How to operate the database 、 Why operate the database , What are the pros and cons , In addition, there is a portal for a series of articles below , It's still being updated , Interested partners can also go to check , Don't talk much , Let's have a look ~
Series articles ：
Series articles 1：【Python automated testing 1】 meet Python The beauty of the
Series articles 2：【Python automated testing 2】Python Installation configuration and PyCharm Basic use
Series articles 3：【Python automated testing 3】 First knowledge of data types and basic syntax
Series articles 4：【Python automated testing 4】 Summary of string knowledge
Series articles 5：【Python automated testing 5】 List and tuple knowledge summary
Series articles 6：【Python automated testing 6】 Dictionary and collective knowledge summary
Series articles 7：【Python automated testing 7】 Data operator knowledge collection
Series articles 8：【Python automated testing 8】 Explanation of process control statement
Series articles 9：【Python automated testing 9】 Function knowledge collection
Series articles 10：【Python automated testing 10】 File basic operation
Series articles 11：【Python automated testing 11】 modular 、 Package and path knowledge collection
Series articles 12：【Python automated testing 12】 Knowledge collection of exception handling mechanism
Series articles 13：【Python automated testing 13】 class 、 object 、 Collection of attribute and method knowledge
Series articles 14：【Python automated testing 14】Python Basic and advanced exercises of automatic test
Series articles 15：【Python automated testing 15】unittest The core concept and function of test framework
Series articles 16：【Python automated testing 16】 Test case data separation
Series articles 17：【Python automated testing 17】openpyxl Secondary packaging and data driven
Series articles 18：【Python automated testing 18】 Configuration file analysis and practical application
Series articles 19：【Python automated testing 19】 Log system logging Explain
Series articles 20：【Python automated testing 20】 Construction of interface automation test framework model
Series articles 21：【Python automated testing 21】 Interface automation test practice 1 _ Interface concept 、 Project introduction and test process Q & A
Series articles 22：【Python automated testing 22】 Interface automation test practice II _ Interface framework modification and use case optimization
Series articles 23：【Python automated testing 23】 Interface automation test practice III _ Dynamic parameterization and data forgery
In interface automation, the operation of database is mainly determined according to the business level , Some cases, such as querying the mobile phone number 、 Or personal information, you need to operate the database , Sometimes you may need to delete something , Generally speaking, I don't do that .
""" benefit ： 1、 Be able to solve certain business problems according to the business conditions we need Disadvantages ： 1、 The operation of the database itself makes automation slow , Connection required -- Inquire about wait 2、 The database itself is a dependency , We can not operate the database as much as possible """
To operate the database , It needs to be done first pymysql The installation of the library , Fill in the corresponding data according to the corresponding syntax ：
import pymysql
""" 1、 Connect to database ： enter one user name , password , Address , port 2、 The cursor ： Using a cursor to read data 、 Modifying data （ perform sql sentence ） 3、 Get operation results 4、 Close cursor 5、 Close database connection """
conn = pymysql.connect(user="future",
password="XXXXXX",
host="XX.XX.XX.XX",
port=3306
)
cursor = conn.cursor()
# perform sql
sql = "SELECT mobile_phone,id,reg_name FROM XXXXXXX.member WHERE mobile_phone = 137XXXXXXXX"
cursor.execute(sql)
# Get a result. Each record is represented by a tuple
res = cursor.fetchone()
print(res)
# close
cursor.close()
conn.close()
The output data is tuple , Tuple data cannot be modified , We need to do type conversion , To output a dictionary , Need to add DictCursor：
import pymysql
from pymysql.cursors import DictCursor
""" 1、 Connect to database ： enter one user name , password , Address , port 2、 The cursor ： Using a cursor to read data 、 Modifying data （ perform sql sentence ） 3、 Get operation results 4、 Close cursor 5、 Close database connection """
conn = pymysql.connect(user="future",
password="XXXXXX",
host="XX.XX.XX.XX",
port=3306
)
cursor = conn.cursor(DictCursor)
# perform sql
sql = "SELECT mobile_phone,id,reg_name FROM XXXXXXX.member WHERE mobile_phone = 137XXXXXXXX"
cursor.execute(sql)
# Get a result. Each record is represented by a tuple
res = cursor.fetchone()
print(res)
# close
cursor.close()
conn.close()
We need function encapsulation , Database content also belongs to public content , It can also be put into common It's a bag , After function encapsulation ：
class DBHandler:
def __init__(self, host=setting.db_host, port=setting.db_port,
user=setting.db_user, password=setting.db_pwd):
self.conn = pymysql.connect(user=user,
password=password,
host=host,
port=port,
autocommit=True
)
def query_one(self, sql, cursor_type=DictCursor):
cursor = self.conn.cursor(cursor_type)
cursor.execute(sql)
data = cursor.fetchone()
cursor.close()
return data
def query_all(self, sql, cursor_type=DictCursor):
cursor = self.conn.cursor(cursor_type)
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
return data
def query(self, sql, one=True, cursor_type=DictCursor):
if one:
return self.query_one(sql, cursor_type)
return self.query_all(sql, cursor_type)
def close(self):
self.conn.close()
# Implement the context manager yourself
# def __enter__(self):
# return self
#
# def __exit__(self, exc_type, exc_val, exc_tb):
# return self.close()
db_module = DBHandler()
if __name__ == '__main__':
db = DBHandler()
sql = 'select mobile_phone from futureloan.member limit 5'
res = db.query(sql, one=False)
db.close()
print(res)
#
# with DBHandler_2() as db:
# sql = 'select mobile_phone from futureloan.member limit 5'
# res = db.query(sql, one=False)
# print(res)
# Database configuration items
db_host = "XX.XX.XX.XX"
db_port = XXXX
db_user = "future"
db_pwd = "XXXXXX"
All right. ~ The above is all the content shared in this article , Have you learned ？ I hope I can help you ！
