Python3 operation database mysql-super brother serial (1)

Catalog index
1. When will it be used
2. How to use
   2.1 Install pymsql
   2.2 Connect to the database
       2.2.1 Connect to the database: 2.2.2
       -Get the cursor:
    2.3 Operate the database
        2.3.1-Method of executing sql
        2.3.2 Query operation
        2.3. 3-Update operation
        2.3.4-Delete operation
        2.3.5-Insert operation
Python3 operation database mysql serial (four)

1. When will it be used

When we make interface use case assertion or UI use case assertion, we may need to go to the database to fetch data, and if we need to automate data creation, sometimes we also need code to manipulate the database, so python operation of the database is still quite important. Today, super brother Come to take you to learn from the beginning, there was applause, although I can't hear it, hehe.

Super brother's consistent style, simple and rude , specific code:

2. How to use it

2.1 install pymsql

Python installs pymsql, generally we prefer to use the pip command: pip install pymysql, here is one point, pymysql is a database operation module supported by python3, the previous mysqldb module does not apply to python3, so it is discarded.

The installation is shown in the figure:

image

2.2 Connect to the database

To prepare for the connection, you first need to prepare the address of the database server that can be connected. Mine is as follows:

IP: 127.0.0.1

Port: 3306

user: root

pwd: xxxx

database: testdb

2.2.1-Connect to the database:

pymysql.connect() connect to the database function, you need to pass host (host address), user (database login user), passwd (password), db (database name to be connected) parameters

#导入安装好的pymysql包import pymsql#打开数据库连接conn = pymysql.connect(host='127.0.0.1',user = "root",passwd = "123456",db = "testdb")


2.2.2- Get the cursor:

To operate the database, it is not enough to connect to the data. You must get the cursor to operate the database before you can perform subsequent operations, such as reading data and adding data. Create a cursor through the cursor() method under the acquired database connection instance conn. The cursor is used to receive the returned result

#获取游标,用来查询,写入等

Description: The cursor returns a cursor instance object, which contains many methods of manipulating data, such as executing SQL statements.

2.3 Operating the database

2.3.1- Method of executing sql

Operating the database is nothing more than performing SQL additions, deletions, changes, and checks. There are two ways to execute SQL statements: execute and executemany. Let’s take a look at the two methods of use. The following code examples will explain in more detail.

execute(query,args=None)
function function: execute a single SQL statement, and return the number of affected rows after successful execution.
Parameter description:
query: SQL statement to be executed, string type
args: optional sequence or mapping, use The parameter value of the query. If args is a sequence, %s must be used as a placeholder in the query; if args is a mapping, %(key)s must be used as a placeholder in the query

#使用%s作为占位符,通过args传参

Executemany(query,args=None)
function function: execute SQL statements in batches, such as batch inserting data, and return the number of affected rows after successful execution.
Parameter description:
query: SQL statement to be executed, string type
args: nested sequence Or mapping, parameter value for query

#使用%s作为占位符,通过args传参

note:

A large part of the database performance bottleneck lies in network IO and disk IO. Putting multiple SQL statements together and executing IO only once can effectively improve database performance. Recommend this method

Using the executemany() method to execute SQL statements in batches at one time is good, but when too much data is transferred to the server side at a time, it may cause the buffer overflow on the server side, and it may also cause some unexpected troubles. Therefore, it is a reasonable way to use executemany reasonably and in batches

The above are two operation functions. After learning, we can show our skills. Of course, database operations cannot be separated from adding, deleting, modifying and checking. Keep up with the rhythm. Remember to share the article on the official account of Chaoge to let more people who learn technology have What you have gained, how much does Brother Chao love you?

to sum up:

The next step is to use the above two methods to specifically add, delete, modify and check the database. Tomorrow we will continue to update, remember to pay attention to the official account of Super Brother, next time you learn to check, don't get lost, search directly on WeChat: Orange is good for testing and development, you know.

Beijing Orange Technology, SuperTesting, Test Promotion Institute, focus on test promotion, annual salary sprint 40W!

Functional test students who want to transform test development can pay attention to the following public accounts, more dry goods are waiting for you!