A brief analysis of pymysql database operation process using Python interface automation

This article mainly introduces the installation, operation process, grammatical basis and encapsulation operation database class of pymysql. Friends who need it can refer to it. I hope it can be helpful to everyone. I will improve a little bit every day. You are welcome to communicate and discuss

During the automation process, we need to query the database to verify whether the results are correct. For example, after the recharge is completed, we need to query the database to see whether the recharge was successful.

The following mainly introduces the pymysql installation, operation process, syntax basics and encapsulated operation database class.

1. Introduction and installation of pymysql

01 Introduction to pymysql

MySQL should be said to be the most commonly used database today, and Python, as one of the most popular languages, naturally has to deal with MySQL, among which PyMySQL is the most used tool library.

  • PyMySQL is a MySQL client written in pure Python and can run in CPython, PyPy, IronPython and Jython environments;

  • The performance of PyMySQL is almost the same as that of MySQLdb. If the performance requirements are not particularly strong, it will be more convenient to use PyMySQL;

  • The usage of PyMySQL is almost the same as MySQLdb;

02 pymysql installation

Method 1: Install using command

pip install pymysql

Method 2: Internal installation of PyCharm

Picture

Import module:

importpymysql

2. Pymysql process and module description

01 pymysql operation process

  • import pymysql;

  • Establish a database connection: Use the connect() method of pymysql to connect data and return the connection object;

  • Use the connection object to create a cursor object (for operating sql);

  • Prepare to write sql statement (select * from student);

  • Use cursor object to execute sql;

  • Query data is obtained using a cursor;

  • Close the cursor (first) and database connection (last).

Picture

02 pymysql module description

Connection object

Represents: conn=connect (parameter list)

Function: Used to establish a connection with the database;

Create an object: call the connect() method;

Parameter list:

host: the connected mysql host, for example, this machine is ‘localhost’;

port: The port of the connected mysql host, the default is 3306;

database: the name of the database;

user: the user name of the connection;

password: the password for the connection;

charset: the encoding method used for communication, utf8 is recommended;

Object methods

The object methods are as follows:

close():Close the connection;

commit():Submit;

cursor():Returns Cursor object, used to execute sql statements and obtain results;

execute(operation [, parameters]):Execute the statement and return the number of affected rows. It is mainly used to execute insert, update, delete statements, and can also execute create, alter, drop and other statements;

fetchone(): When executing a query statement, obtain the first row of data in the query result set and return a tuple;

fetchall(): When executing a query, get all the rows of the result set, each row constitutes a tuple, and then put these tuples into a tuple and return it;

Regarding pymysql anti-injection, string splicing queries can easily cause injection. In order to avoid injection, use the parameterized statement provided by pymysql;

Cursor object

Cursor is the symbol of swimming. In layman’s terms, a SQL statement retrieves the interface/handle corresponding to n result resources, which is the cursor. You can retrieve one row at a time along the cursor.

  • Used to execute SQL statements. The most frequently used statements are select, insert, update, and delete;

  • Get the Cursor object: call the cursor() method of the Connection object: cs1=conn.cursor()

Object properties

  • The rowcount read-only attribute indicates the number of rows affected after the latest execute() execution;

  • connection gets the current connection object;

3. Basic syntax of pymysql

01 Code Example

import pymysql
# Connect to the database
conn = pymysql.connect(host='127.0.0.1', user='ITester', password='123456',
                       database='ITester', charset='utf8')
#Create cursor
cursor = conn.cursor()
#Execute sql statement
sql = 'select * from user limit 3;'
res = cursor.execute(sql)
# Get 1 piece of data from the query results
data = cursor.fetchone()
print(data)
# Close the cursor connection
cursor.close()
#Close database connection
conn.close()

02 Grammar summary

1. Connecting to the database requires host, user, password, database, charset and other information;

2. Create a cursor first when operating the database;

3. Execute the specified sql statement. If it involves adding, deleting, or modifying the database, conn.commit() must be used to submit the transaction.

4. There are three methods to query and obtain the number of data items: fetchone, fetchmany, and fetchall.

  • cursor.fetchone(): Gets the first data of the query result by default;

  • cursor.fetchmany(2): Get the specified number of query results, such as getting 2 pieces of data;

  • cursor.fetchall(): Get all the data of the query results;

5. It should be noted that the data obtained by fetch is a tuple by default. If you want a dictionary type,

cursor=pymysql.cursors.DictCursor;

6. Close the cursor first, then close the database connection;

4. Encapsulating database classes

01 Packaging instructions

In actual projects, database operations are used in many places, so database-related operations need to be encapsulated to facilitate calls from other modules.

As shown below, in the common directory, create a new file db_handler.py to encapsulate database operations.

Picture

db_handler.py

import pymysql
classDBHandler:
    def __init__(self,host,port,user,password,
                 database,charset,**kwargs):
        # Connect to database server
        self.conn = pymysql.connect(host=host, port=port, user=user,password=password,
                                    database=database,cursorclass=pymysql.cursors.DictCursor,
                                    charset=charset,**kwargs)
        # Get the cursor
        self.cursor = self.conn.cursor()
    
    def query(self, sql, args=None, one=True):
        self.cursor.execute(sql, args)
        # Submit transaction
        self.conn.commit()
        if one:
            return self.cursor.fetchone()
        else:
            return self.cursor.fetchall()
    def close(self):
        self.cursor.close()
        self.conn.close()
if __name__ == "__main__":
    db = DBHandler(host='127.0.0.1', port=3306,
                   user='ITester', password='123456',
                   database='ITester', charset='utf8')
    sql = 'select * from user limit 1;'
    data = db.query(sql)
    print(data)

The above is the detailed content of the Python interface automation to analyze the pymysql database operation process.

Finally: The complete software testing video tutorial below has been compiled and uploaded. Friends who need it can get it by themselves[Guaranteed 100% Free]

Software testing interview document

We must study to find a high-paying job. The following interview questions are from the latest interview materials from first-tier Internet companies such as Alibaba, Tencent, Byte, etc., and some Byte bosses have given authoritative answers. After finishing this set I believe everyone can find a satisfactory job based on the interview information.